retrieve records from several tables where certain_col = certain_value

[peppericious]

I have a db with 4 tables:

table_A

table_B

table_C

table_D

 

 

table_A contains personal info about users and has a primary id field called uid.

Each of tables B, C and D is linked to table_A via the foreign key f_uid.

 

table_A also has a column called signed_up which can contain a 1 or a 0.

 

I want to pull up lots of info from each of the 4 tables for every user, as long as the signed_up column in table_A contains a 1.

 

So, I've made a query like this:

 

$q = "SELECT
	table_A . col_1 ,
	table_A . col_2 ,
	table_A . col_3 ,
	table_B . col_1 ,
	table_B . col_2 ,
	table_B . col_3 ,
	table_C . col_1 ,
	table_C . col_2 ,
	table_C . col_3 ,
	table_D . col_1 ,
	table_D . col_2 ,
	table_D . col_3
FROM
	table_A, table_B, table_C, table_D
WHERE
	table_A . uid = table_B . f_uid
AND
	table_A . uid = table_C . f_uid
AND	
	table_A . uid = table_D . f_uid	
AND
	table_A . signed_up = '1'
ORDER BY
	table_A . some_col		
";
$r = mysqli_query($dbc, $q);

 

The query is executing but it's also pulling up records where signed_up in table_A contains a '0'. Can anyone see a logical flaw in my query?...

 

Alternatively, is there an easier way I could build the query?

 

TIA

[Psycho]

Hmm, I've never put spaces between the table name and the table field like that. So, don't know if that is the cause. But, I would definitely suggest using "proper" joins. It makes it more logical to separate the JOIN logic from the WHERE logic. Try the following:

 

$q = "SELECT a.col_1, a.col_2, a.col_3,
             b.col_1, b.col_2, b.col_3,
             c.col_1, c.col_2, c.col_3,
             d.col_1, d.col_2, d.col_3

      FROM table_A AS a
      LEFT JOIN table_B AS b ON b.f_uid = a.uid
      LEFT JOIN table_C AS c ON c.f_uid = a.uid
      LEFT JOIN table_D AS d ON d.f_uid = a.uid

      WHERE a.signed_up = '1'

      ORDER BY a.some_col";

[peppericious]

Thanks, Psycho, will give this a go...