How do have two constraints on a UNION?

Question

I have this code below. It works if i use only ONE WHERE variable but the moment i add another one, the query doesn't work.

It works if i just use this one all unions:

       where table_constant.user_id = '$uid'

But when i use this one below, it doesn't work:

      where table_constant.user_id = '$uid' and table_one.something <> '$uid'

Code:

  $sql = "select table_one.field1, table_constant.field1, 
table_one.field2, table_one.field3, table_one.field4, 
table_one.field5, table_constant.c_id
from table_one LEFT JOIN table_constant on table_one.field1 
= table_constant.c_id 
where table_constant.user_id = '$uid' and table_one.something <> '$uid'
UNION
select table_two.field1, table_constant.field1, table_two.field2, 
table_two.field3,    table_two.field4, table_two.field5, table_constant.c_id
from table_two LEFT JOIN table_constant on table_two.c_id 
= table_constant.c_id 
where table_two.added_by = '$uid' and table_two.something <> '$uid'
UNION 
select table_three.field1, table_constant.field1, table_three.field2, 
table_three.field3, table_three.field4, table_three.field5,
table_constant.c_id
from table_three LEFT JOIN table_constant ON table_three.c_id 
= table_constant.c_id
where table_constant.user_id = '$uid' and table_three.something <> '$uid'
UNION
select table_four.field1, table_constant.field1, table_four.field2, 
table_four.field3, table_four.field4, table_four.field5, 
table_constant.c_id
from table_four LEFT JOIN table_constant ON table_four.c_id 
= table_constant.c_id
where table_constant.user_id = '$uid' and table_four.something <> '$uid'
ORDER BY date DESC LIMIT $start, $limit";
$result = mysql_query($sql);

Then I guess you'll have to show some data that should give a result but doesn't. — idstam, Jun 27 '11 at 04:35

score 1 · Accepted Answer · answered Jun 27 '11 at 04:48

On second thought I have a decent guess as to what the problem might be. When you write a LEFT JOIN, I presume you do so because there might be some rows that don't match anything on the right side of the join. If you'd like to constrain those rows that do match in some way, you need to do so in the JOIN itself. For example:

LEFT JOIN table_constant ON table_one.field1 = table_constant.c_id AND table_constant.user_id = '$uid'

By placing the second condition in the WHERE clause, you're essentially turning the left join into an inner join by forcing the right side to have a value.

How do have two constraints on a UNION?

1 Answers1