[Resolved] Multiple table query

aconway

I am trying to query multiple tables that have the same field name and return the results. It should only return one result because there is only one possible id with the database, I just don't know which table to look in, so I'm trying to look in all of them.

any way here is the query:

"SELECT * FROM Table1, Table2, Table3, Table4 WHERE Table1.ID = '$id', Table2.ID = '$id', Table3.ID = '$id', Table4.ID = '$id'";

any ideas on what I am doing wrong?

YappyDog

You don't say what database you are using, but it looks like you need to use a UNION to get what you are after.

Here's a link to a MySQL page explaining how to use it
http://dev.mysql.com/doc/mysql/en/UNION.html

aconway

so would I do something like this

"
(SELECT FROM Table1 WHERE Table1.ID = '$id')
UNION
(SELECT FROM Table2 WHERE Table2.ID = '$id')
UNION
(SELECT FROM Table3 WHERE Table3.ID = '$id')
UNION
(SELECT FROM Table4 WHERE Table4.ID = '$id')
";

YappyDog

Looks like it. You probably don't need the parentheses, or the table name identifiers before the id (ID instead of Table1.ID), but it should work the way it is.

aconway

I'm still not getting any results, let me show you what I have

connect_db();

$results = mysql_query("(SELECT FROM Table1 WHERE ID='$id') UNION
(SELECT FROM Table2 WHERE ID='$id') UNION
(SELECT FROM Table3 WHERE ID='$id') UNION
(SELECT FROM Table4 WHERE ID='$id')");

$data = mysql_fetch_array($results);

YappyDog

Are you certain your $id variable has anything in it before you build your query?

Does your $data variable have anything in it after you do your fetch?

aconway

the $id variable does have something in it

the $data variable doesn't have anything in it after the fetch.

YappyDog

Do ALL of the tables have exactly the same columns (type and size) in the exact order?

aconway

No the table structure is different for each table.

They all have the same field ID, though.

YappyDog

Then instead of Select * from ...

you need to use Select ID from ...

aconway

will I be able to get all the info from the record associated with ID if I do that?

YappyDog

If you go back to the link I sent you on the first post, it says that each query has to select the same number of columns and all columns selected have to be the same type, and in the same order.

So the answer to your question depends on what columns you have in each table, and what columns you need to get out of each table.

aconway

Okay,

I'll read that again.

Thanks for your help