Calling all MySQL Experts

Can you help me build this query?

[trillianjedi]

I'm having a little trouble with this one, as it's a bit advanced for me, and I haven't yet got my head around all this "INNER JOIN" type stuff...

I have a DB which is a database of members. Each member has a name (site_name) and a unique ID (uid).

I need to take the name, from that get the unique ID, then with the Unique ID get another unique number (some_number) from another table, then from that, return a result set from yet another table.

In short, three queries. But I'd like to do it all in one hit, if this is possible?

The three queries individually would be something like:-

Query 1

SELECT uid from USERS where Site_Name = 'trillianjedi';

Query 2

SELECT some_number from THE_OTHER_TABLE where uid = (the uid from query 1);

Query 3

SELECT * from THE_FINAL_TABLE where some_number = (the some_number from query 2);

I hope that makes sense.... any help as to how I would wrap this all up in one query would be much appreciated!

Thanks,

TJ

[coopster]

Certainly. JOIN the tables using the PRIMARY KEY from the left table to JOIN to the FOREIGN KEY in the right table and repeat for the other right table. An INNER JOIN will return only those rows where a match has been found in all three tables.

SELECT 
 USERS.uid,  
 THE_OTHER_TABLE.some_number,  
 THE_FINAL_TABLE.*  
 FROM USERS  
 INNER JOIN THE_OTHER_TABLE ON  
  (USERS.uid = THE_OTHER_TABLE.uid)  
 INNER JOIN THE_FINAL_TABLE ON  
  (THE_OTHER_TABLE.some_number = THE_FINAL_TABLE.some_number)  
 WHERE USERS.Site_Name = 'trillianjedi'  
;

[trillianjedi]

Coop - many thanks for the explanation - I never seem to stop learning here ;-)

TJ