Page 1 of 1

Select from multiple MySQL tables

Posted: Wed Feb 15, 2006 4:20 pm
by Iganorf
I have four tables that I need to select data from to display one particular row. All four tables share one column for an id. I need a query that will select a distinct row with a distinct id from the four tables, and all the queries I've tried have caused many problems.
I tried this:

Code: Select all

SELECT * FROM t1,t2,t3,t4 WHERE t1.id = '$id' OR t2.id = '$id' OR t3.id = '$id' OR t4.id = '$id' LIMIT 1
But it never completes.

I also tried:

Code: Select all

SELECT * FROM t1 WHERE id = '$id' UNION SELECT * FROM t2 WHERE id = '$id' UNION SELECT * FROM t3 WHERE id = '$id' UNION SELECT * FROM t4 WHERE id = '$id'
It gives me an error when I try to call mysql_fetch_array().

I even tried:

Code: Select all

SELECT * FROM t1,t2 WHERE t1.id = '$id' OR t2.id = '$id'
But it took so long that I could have manually searched the database myself and found the entries before mysql did, and when it did return a row it was the completely wrong row.

I'm not very fluent at MySQL, but mainly because I can't understand the Documentation. Anyone have any ideas? I know that I could do four separate queries, but I have no way of knowing which table the id will be in, and then I'll have to make sure that I select all the data from the correct table because each table has a differect structure, other than the id column.

Posted: Wed Feb 15, 2006 4:28 pm
by feyd
This should be 4 separate queries. Joining across tables is for linking information from those tables together to create a more descriptive result set.

Posted: Wed Feb 15, 2006 4:39 pm
by Iganorf
Thanks, I just thought that there might be a way to do it with one query. I think I'll just set up a loop to find which table it's in.
Thus my MySQL knowledge is increased.

Posted: Wed Feb 15, 2006 4:47 pm
by feyd
Depending on your version of MySQL, you may have support for a UNION. however if the columns vary in type or quantity, you need to use separate queries.

Posted: Wed Feb 15, 2006 6:45 pm
by RobertGonzalez
If you are doing your checking code-side, set up an array of tables, for-loop through the table array, run a simple select query and check each result set for a count from each table iteration. As soon as the query you are on reaches a num_rows count of 1 you have it. You'll know the table from the array location you are on and you can exit when you get what you want.