Problem in Union Join For MySQL Query

Question

hey guys

i managed to select from a table that saves my latest posts

but i need to have double condition in selection

here is my code :

$sql_query = "SELECT b.*,u.username AS MY_Sender
FROM TABLE_users u,TABLE_blogs b
Where b.reciever = '0' AND u.user_id = b.sender
UNION
SELECT b.*,u.username AS MY_reciever
FROM TABLE_users u,TABLE_blogs b
Where b.reciever != '0' AND u.user_id = b.reciever
ORDER BY bid DESC
LIMIT 0,7 ";

but MY_reciever is Null and empty

Am i wrong in using UNION for this need ?!

Answer 1

SELECT b.*,u.username AS MY_Sender, NULL AS MY_reciever
FROM TABLE_users u,TABLE_blogs b
Where b.reciever = '0' AND u.user_id = b.sender
UNION
SELECT b.*, NULL AS MY_Sender, u.username AS MY_reciever
FROM TABLE_users u,TABLE_blogs b
Where b.reciever != '0' AND u.user_id = b.reciever

Answer 2

Columns across the parts of a union must have the same name. Try:

SELECT b.*,
       u.username AS MY_User
  FROM TABLE_users u,
       TABLE_blogs b 
 WHERE b.reciever = '0' 
   AND u.user_id = b.sender 
UNION 
SELECT b.*,
       u.username AS MY_User
  FROM TABLE_users u,
       TABLE_blogs b 
 WHERE b.reciever != '0' 
  AND u.user_id = b.reciever 
 ORDER BY bid DESC 
 LIMIT 0,7

If you need to distinguish which part of the UNION a record has been returned by, return an extra column:

SELECT b.*,
       u.username AS MY_User.
       'Sender' AS MyType
  FROM TABLE_users u,
       TABLE_blogs b 
 WHERE b.reciever = '0' 
   AND u.user_id = b.sender 
UNION 
SELECT b.*,
       u.username AS MY_User.
       'Recipient' AS MyType
  FROM TABLE_users u,
       TABLE_blogs b 
 WHERE b.reciever != '0' 
  AND u.user_id = b.reciever 
 ORDER BY bid DESC 
 LIMIT 0,7

Answer 3

@Mac Taylor

hey man , sure you do understand me , told you that i need one more condition added to this statement you wrote , AS My_Real_Sender .. as you may know this statement you wrote works fine to find the Reciver ( MY_Type ) and not the sender of the post

You mean something like this?

SELECT b.*, 
       u.username AS MY_Sender. 
       NULL as MY_Recipient,
       'Sender' AS MyType 
  FROM TABLE_users u, 
       TABLE_blogs b  
 WHERE b.reciever = '0'  
   AND u.user_id = b.sender  
UNION  
SELECT b.*, 
       u1.username AS MY_Sender. 
       u2.username AS MY_Recipient. 
       'Recipient' AS MyType 
  FROM TABLE_users u1, 
       TABLE_users u2, 
       TABLE_blogs b  
 WHERE b.reciever != '0'  
  AND u1.user_id = b.reciever  
  AND u2.user_id = b.sender  
 ORDER BY bid DESC  
 LIMIT 0,7