Help with a join

[Foamcow]

Can someone help me with this pretty simple query?

Some of you may know that I just can't get the hang of joins - they require masses of mental effort on my part and I'm pretty burned out at the moment so this is just not happening for me.

Here's the gist of what I am trying to do.

I have 3 tables

pages - contains main info for a page on the site
suppContent - contains extra info that may appear on pages within the site
pageToSuppContentId - contains info to map suppContent to Pages. It maps pageId to suppContentId

I know the pageId and I need to get the correct suppContent Row based on the pageId mapped against it in the suppContentToPage table

So I look up the suppContentId in the pageToSuppContentId table

	SELECT ptsc.suppContentId FROM pageToSuppContentId ptsc WHERE ptsc.pageId = <page_id>

I can then use this suppContentId to lookup the data from the suppContent table

	SELECT sc.* FROM  suppContent sc WHERE sc.id = <suppContentId>

How can I combine this into a single query?

<honk>*:O)</honk>

Earl & Thompson Marketing - Marketing Agency Services in Gloucestershire

 

[traingamer]

Foamcow, welcome to the dark side. How about something like:

SELECT sc.* FROM  suppContent sc 
INNER JOIN  pageToSuppContentId ptsc ON sc.id = ptsc.suppContentId
WHERE ptsc.pageId = <page_id>

depend

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Foamcow]

Oh hang on, I think I've done it.

SELECT sp.* FROM pageToSuppContent ptsc RIGHT JOIN suppPages sp ON sp.id = ptsc.suppContentId WHERE ptsc.pageId = <page_id>

Seems to work, but I've not throroughly tested it yet

<honk>*:O)</honk>

Earl & Thompson Marketing - Marketing Agency Services in Gloucestershire

 

[Foamcow]

I'll try yours too Traingamer, thanks.

<honk>*:O)</honk>

Earl & Thompson Marketing - Marketing Agency Services in Gloucestershire

 

[lespaul]

Additionally, read the understanding joins article linked below.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Foamcow]

Thankyou lespaul.
Actually I've read that before. I'm afraid I have an appaling mental block when it comes to joins. They just don't 'click'.

But, I'll give it another read because its something I really need to get a handle on.

Thanks again

<honk>*:O)</honk>

Earl & Thompson Marketing - Marketing Agency Services in Gloucestershire