how to query several tables in one string?

[jpfour23]

Hey all,

Forgive my newbieness... I just discovered these forums and they ROCK!

How do I query several tables but get one piece of info? I'm so confused... I use phpmyadmin to practice but I always get like 4 or 5 rows back of details. Am I not being specific enough in the WHERE clause?

example: DB=test and Tables=tbl1, tbl2, tbl3
(all of which have the same types of colums)

$slq = "SELECT * FROM tdb1, tdb2, tdb3 WHERE username = $uid AND article = $artID"; 

Does this make sense? I'm new to joins and trying to understand them. Perhaps someone can offer advise.

THANKS!!
JP

[DeNasio]

After the WHERE clause you should specify what the tables have in common.

Example:

Let's say you have the following tables:

table "a":
+----+------+
| a1 | a2 |
+----+------+
| 10 | u |
| 20 | v |
| 30 | w |
| 40 | x |
| 50 | y |
| 60 | z |
+----+------+

table "b":
+----+------+
| b1 | b2 |
+----+------+
| 10 | p |
| 20 | q |
+----+------+

SELECT * FROM a,b;

The code above will give you the following results:

+----+------+----+------+
| a1 | a2 | b1 | b2 |
+----+------+----+------+
| 10 | u | 10 | p |
| 20 | v | 10 | p |
| 30 | w | 10 | p |
| 40 | x | 10 | p |
| 50 | y | 10 | p |
| 60 | z | 10 | p |
| 10 | u | 20 | q |
| 20 | v | 20 | q |
| 30 | w | 20 | q |
| 40 | x | 20 | q |
| 50 | y | 20 | q |
| 60 | z | 20 | q |
+----+------+----+------+

SELECT * FROM a,b WHERE a.a1 = 20;

The code above will give you the following results:

+----+------+----+------+
| a1 | a2 | b1 | b2 |
+----+------+----+------+
| 20 | v | 10 | p |
| 20 | v | 20 | q |
+----+------+----+------+

SELECT * FROM a,b WHERE a.a1 = 20 AND a.a1=b.b1;

The code above will give you the following results:

+----+------+----+------+
| a1 | a2 | b1 | b2 |
+----+------+----+------+
| 20 | v | 20 | q |
+----+------+----+------+

Hope this helps.

[jpfour23]

It does a little. I'm not sure why all the extra data is coming up... like in SELECT * from a,b .... why does it double the data in each column? Maybe I just need to read more about SQL.

[weirdbeardmt]

Head over to mysql.com and look at the SELECT query syntax. You'll learn about using DISTINCT and LIMIT.

[Mincer]

It does a little. I'm not sure why all the extra data is coming up... like in SELECT * from a,b .... why does it double the data in each column? Maybe I just need to read more about SQL.

What you're doing is called a cross-join, that gies you all combinations of fields. You need to do an equi-join, as DeNasio siad.

Here are some tutorials from devshed you may find useful:

Understanding SQL Joins

MySQL Table Joins

Matt.