[SOLVED] MYSQL JOINS

[aclab]

Hi, my understanding of different mysql joins is quite limited, I have two statements below which I'm assuming can be combined into one but I'm not sure how to approach it:

 

$sql = "SELECT * FROM galleries WHERE id = '" . $id . "'";

$data = mysql_query($sql);

$sql = "SELECT title FROM galleries WHERE id = '" . $data[parentID] . "'";

 

The 'galleries' table holds both the child and parent galleries, so in the above I'm looking to get all the fields for the child gallery in question, and then the title of the parent gallery from the same table.

 

On the same note if anyone knows of any good resources/tutorials for mysql joins (preferrably rich in examples!)...

 

Thanks in advance

[Ken2k7]

SELECT g1.*, g2.title AS parent_title
FROM galleries g1
INNER JOIN galleries g2
     ON (g2.id = g1.parentID)
WHERE g1.id = $id

 

Make sense? Let me know if you need clarification.

[aclab]

Perfect, many thanks.

[aclab]

Ahh... realised while implementing that I made an error in my description and thus the solution provided... all fields should ALWAYS be retrieved for the requested record, whereas the parentID should only be retrieved IF IT EXISTS. The above statement will only return a record if a parentID exists. Any further advice for adapting this statement?

[kickstart]

Hi

 

It is an outer join you need then

 

SELECT g1.*, g2.title AS parent_title
FROM galleries g1
LEFT OUTER JOIN galleries g2
ON (g2.id = g1.parentID)
WHERE g1.id = $id

 

All the best

 

Keith

[aclab]

Thanks Keith, much appreciated.