SQL Select from more than one table ?

[Anon]

Hi everyone,

ich have to make an sql query in which i have to selcet a certain field (in my case: id) from more thank one table in the database.

I have 3 tables in fact (articles, decription, use). And i want to make a sql select query like this:

$sql = "SELECT id FROM articles, decription, use WHERE domething = '$somevar'";

But that does not work. Does anyone know how i can do the thing i want in working SQL-Langueage ?

Thanks in advance

ali

[Anon]

Ali -
This may help:

$sql_query = "SELECT articles.id AS article_id, description.id AS description_id, use.id AS use_id FROM articles, description, use WHERE something = '$somevar'";

depending on the logistics of the query, there may be more efficient ways of doing this (look into JOINS), but this should work. Also, because of the AS statements the results would be referenced like:

$result = mysql_query( $sql_query );
$row = mysql_fetch_array( $result );
$desc_id = $row["description_id"];
$art_id = $row["article_id"];
$use_id = $row["use_id"];

-Jim Keller
http://www.centerfuse.net

[[deleted]]

That too cannot work.

When selecting data from more than one table you MUST link ALL the tables through the WHERE clause. You should be able to 'walk' accross the relations from the first table to every other table.

Extremely bad:

SELECT something
FROM table1, table2, table3
WHERE foo=bar

because none of the tables are linked at all.

Still bad:
SELECT something
FROM table1, table2, table3
WHERE table1.field1 = table2.field2

because table3 is not linked to any other tables.

Good:
SELECT something
FROM table1, table2, table3
WHERE table1.field1 = table2.field2
AND table2.field3 = table3.field3

Table1 is linked to table2, and table2 is linked to table3.

Also good:
SELECT something
FROM table1, table2, table3
WHERE table1.field1 = table2.field2
AND table1.field1 = table3.field3

Table1 is linked to table2 and table3.

---

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

you are correct, i forgot to mention that all tables must be specified in the WHERE clause as well.

[Anon]

thanks a lot ,but whats wrong with following:

$sql = "
SELECT age
FROM test, real
WHERE test.name = real.name
AND name = '$searchedName'
";

it gives me an error in the sql. (Warning: Supplied argument is not a valid MySQL result resource .....)

Could anyone of you tell ?

thanks in advance

ali

[jim_keller]

"name" is an ambiguous field, as it seems that it is present in both the real and test tables. You also need to specify which table 'age' needs to come from. Try something more like this:

SELECT real.age
FROM test, real
WHERE test.name = real.name
AND real.name = '$searchedName'

Although this seems like kind of a bizarre query - what exactly are you trying to do?

-Jim Keller
http://www.centerfuse.net