Select from multiple MySQL tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Iganorf
Forum Newbie
Posts: 10
Joined: Tue Dec 06, 2005 4:53 pm

Select from multiple MySQL tables

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Iganorf
Forum Newbie
Posts: 10
Joined: Tue Dec 06, 2005 4:53 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply