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 ?!

You probably don't need a union. Instead make it one query with the two WHERE conditions in an OR — Bart van Heukelom
– Bart van Heukelom, Commented Jul 7, 2010 at 12:12
Please can you post the output of CREATE TABLE <table> for both your table_users and table_blogs tables. Your joins seem a little odd. — Mike
– Mike, Commented Jul 7, 2010 at 12:15

Mike · Accepted Answer · 2010-07-07 13:12:13Z

3

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           |
+----+-----------+-------------+-------------+---------------+

edited Jul 7, 2010 at 13:12

answered Jul 7, 2010 at 12:28

Mike

21.7k2 gold badges48 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Mac Taylor Over a year ago

that was nice answer but you didnt mentioned , what if the reciver is 0 !?! when the reciver is 0 it means the sender posting it in his own page and else if reciver has a value , then he is posting it in other's page

Mike Over a year ago

table_blogs looks okay, but you haven't posted the user table. table_blogs would benefit from a couple of foreign key constraints to prevent invalid values being inserted. I don't think that there is a need for the index on bid either, as the primary key is already indexing it.

Mac Taylor Over a year ago

table_users is simple just a user_id and username field is necessary , so plz read my previous comment , the condition should be what if the receiver is 0 , then the sender is posting in its own page and the receiver should be the sender

bobince Over a year ago

Yeah, don't use 0 for an out-of-band value on a reference to another table, that'll bite you bad when you move to another table/database type that has foreign key support. You really should be using InnoDB and foreign keys; there's little good reason to run MyISAM today, other than FULLTEXT. As well as the separate join table, you could also use a NULL to mean no-foreign-reference, or in this case surely if it is someone posting to their own page, receiver=sender would be appropriate?

Mike Over a year ago

I don't quite understand the sender/receiver idea for a blog, but if we forget that for a moment, foreign key constraints could be implemented if you simply insert the same value in the sender and receiver columns if both sender and receiver are the same user. But as I'm not really all that clear on what you are doing, I'm not really sure if that is appropriate or not.

|

munissor · Accepted Answer · 2010-07-07 12:07:34Z

1

The field name should be the same

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

answered Jul 7, 2010 at 12:07

munissor

3,78524 silver badges25 bronze badges

3 Comments

Artefacto Over a year ago

The manual only says the columns must have the same type; if they have different names the names form the first result set are used: dev.mysql.com/doc/refman/5.1/en/union.html

Artefacto Over a year ago

This works fine, for instance: select 'a' as col union select 'b' as col2;

KM. Over a year ago

in "general" UNION requires the same number and type of columns from each set, not the same column names, while the first set's column names are used for the result set.

bobince · Accepted Answer · 2010-07-07 12:56:53Z

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.

Collectives™ on Stack Overflow

problem in mysql queries in using union

3 Answers 3

9 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related