problem in mysql queries in using union

Question

i was looking for a way to combine different mysql queries in a php file so this 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.*,
u2.username AS MY_Recipient
  FROM table_users u2, 
       table_blogs b  
 WHERE b.reciever != '0'  
  AND 
  u2.user_id = b.reciever  
";

this code works fine unless it cant fetch MY_Recipient

in the above code i need to fetch both sender of blog post and the receiver

is it wrong to use Union to do so ?!

Answer 1

I have made a guess at your table structure, and produced something similar. Right or wrong, it might at least help arrive at a suitable solution for you.

Two tables, users and blogs:

CREATE TABLE `users` (
    `id` int(11) NOT NULL auto_increment,
    `username` varchar(255) NOT NULL,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

CREATE TABLE `blogs` (
    `id` int(11) NOT NULL auto_increment,
 `sender` int(11) NOT NULL,
 `receiver` int(11) NOT NULL,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

Add some users:

INSERT INTO `users` (username) VALUES
('Alice'), ('Bob'), ('Carol'), ('Eve');

Add blog entries for some users:

INSERT INTO `blogs` (sender, receiver) VALUES
(1,2), (2,1), (3,4), (4,3), (1,4), (4,1);

For each blog entry, list the sender and receiver:

SELECT
    b.id,
 b.sender AS sender_id,
 b.receiver AS receiver_id,
    us.username AS sender_name,
    ur.username AS receiver_name
FROM blogs AS b
JOIN users AS us ON us.id = b.sender
JOIN users AS ur ON ur.id = b.receiver
ORDER BY b.id;

+----+-----------+-------------+-------------+---------------+
| id | sender_id | receiver_id | sender_name | receiver_name |
+----+-----------+-------------+-------------+---------------+
|  1 |         1 |           2 | Alice       | Bob           |
|  2 |         2 |           1 | Bob         | Alice         |
|  3 |         3 |           4 | Carol       | Eve           |
|  4 |         4 |           3 | Eve         | Carol         |
|  5 |         1 |           4 | Alice       | Eve           |
|  6 |         4 |           1 | Eve         | Alice         |
+----+-----------+-------------+-------------+---------------+

UPDATE 1
table_blogs should probably look like this:

CREATE TABLE IF NOT EXISTS `table_blogs` (
  `bid` int(10) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) DEFAULT NULL,
  `date` varchar(14)  DEFAULT NULL,
  `sender` int(10) NOT NULL,
  `reciever` int(10) NOT NULL,
  CONSTRAINT `fk_sender`
    FOREIGN KEY (`sender` )
    REFERENCES `table_users` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_receiver`
    FOREIGN KEY (`receiver` )
    REFERENCES `table_users` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  PRIMARY KEY (`bid`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;

The CONSTRAINT clauses will prevent inserting values for users which don't exist, and will delete entries when users are deleted from the user table.

UPDATE 2
I think this is what you want, but as KM and bobince have stated in the comments, it violates foreign key constraints, which is not really a good idea. So, assuming no foreign key constraints, here's some additional inserts and a modified query:

INSERT INTO `blogs` (sender, receiver) VALUES
(1,0), (0,1), (4,0), (0,4), (2,0), (0,2);

SELECT
    b.id,
 b.sender AS sender_id,
 b.receiver AS receiver_id,
    IFNULL(us.username, ur.username) AS sender_name,
    IFNULL(ur.username, us.username) AS receiver_name
FROM blogs AS b
LEFT JOIN users AS us ON us.id = b.sender
LEFT JOIN users AS ur ON ur.id = b.receiver
ORDER BY b.id;

+----+-----------+-------------+-------------+---------------+
| id | sender_id | receiver_id | sender_name | receiver_name |
+----+-----------+-------------+-------------+---------------+
|  1 |         1 |           2 | Alice       | Bob           |
|  2 |         2 |           1 | Bob         | Alice         |
|  3 |         3 |           4 | Carol       | Eve           |
|  4 |         4 |           3 | Eve         | Carol         |
|  5 |         1 |           4 | Alice       | Eve           |
|  6 |         4 |           1 | Eve         | Alice         |
|  7 |         1 |           0 | Alice       | Alice         |
|  8 |         0 |           1 | Alice       | Alice         |
|  9 |         4 |           0 | Eve         | Eve           |
| 10 |         0 |           4 | Eve         | Eve           |
| 11 |         2 |           0 | Bob         | Bob           |
| 12 |         0 |           2 | Bob         | Bob           |
+----+-----------+-------------+-------------+---------------+

Answer 2

What are you trying to do? You say there are two queries there, but it looks like the same query to me, just one of them having a different table alias.

The only purpose I can see for the UNION is to put all the rows with a zero-receiver before those without. But you can do that more simply by using a computed ORDER BY:

SELECT b.*, u.username
FROM table_blogs AS b
JOIN table_users AS u ON u.user_id=b.sender
ORDER BY b.receiver<>0

• if there are no negative receiver IDs, you could change that to ORDER BY b.receiver as 0 would always come first, which would then be possible to index if you needed to;

• ANSI JOIN is generally considered more readable than the old-school method of implicit joins in the WHERE conditions;

• <> is preferable to !=, which is a non-standard MySQL synonym;

• check the spelling of receiver.

Answer 3

You can do this in a single query, but if you want to use unions, the problem is that both queries need to have the same column names:

select b.*, u.username AS username, "sender" as type ...
select b.*, u2.username AS username, "recipient" as type...

Answer 4

For a union to work, the two select statements should return identical columns. This is where the query is failing.

Answer 5

The field name should be the same

Rename My_sender and My_Recipient to "User" and the union will work.