16

Why am I getting ERROR 1222 (21000): The used SELECT statements have a different number of columns from the following?

SELECT * FROM friends
LEFT JOIN users AS u1 ON users.uid = friends.fid1
LEFT JOIN users AS u2 ON users.uid = friends.fid2
WHERE (friends.fid1 = 1) AND (friends.fid2 > 1)
UNION SELECT fid2 FROM friends
WHERE (friends.fid2  = 1) AND (friends.fid1 < 1)
ORDER BY RAND()
LIMIT 6;

users:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| uid        | int(11)       | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50)   | NO   |     | NULL    |                |
| last_name  | varchar(50)   | NO   |     | NULL    |                |
| email      | varchar(128)  | NO   | UNI | NULL    |                |
| mid        | varchar(40)   | NO   |     | NULL    |                |
| active     | enum('N','Y') | NO   |     | NULL    |                |
| password   | varchar(64)   | NO   |     | NULL    |                |
| sex        | enum('M','F') | YES  |     | NULL    |                |
| created    | datetime      | YES  |     | NULL    |                |
| last_login | datetime      | YES  |     | NULL    |                |
| pro        | enum('N','Y') | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

friends:

+---------------+--------------------------------------+------+-----+---------+----------------+
| Field         | Type                                 | Null | Key | Default | Extra          |
+---------------+--------------------------------------+------+-----+---------+----------------+
| friendship_id | int(11)                              | NO   | MUL | NULL    | auto_increment |
| fid1          | int(11)                              | NO   | PRI | NULL    |                |
| fid2          | int(11)                              | NO   | PRI | NULL    |                |
| status        | enum('pending','accepted','ignored') | NO   |     | NULL    |                |
+---------------+--------------------------------------+------+-----+---------+----------------+
philipxy
  • 14,867
  • 6
  • 39
  • 83
Josh Smith
  • 14,674
  • 18
  • 72
  • 118

1 Answers1

39

UNIONs (UNION and UNION ALL) require that all the queries being UNION'd have:

  1. The same number of columns in the SELECT clause
  2. The column data type has to match at each position

Your query has:

SELECT f.*, u1.*, u2.* ...
UNION 
SELECT fid2 FROM friends

The easiest re-write I have is:

   SELECT f.*, u.*
     FROM FRIENDS AS f
     JOIN USERS AS u ON u.uid = f.fid2
    WHERE f.fid1 = 1 
      AND f.fid2 > 1
UNION 
   SELECT f.*, u.*
     FROM FRIENDS AS f
     JOIN USERS AS u ON u.uid = f.fid1
    WHERE f.fid2  = 1 
      AND f.fid1 < 1
ORDER BY RAND()
LIMIT 6;

You've LEFT JOIN'd to the USERS table twice, but don't appear to be using the information.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Great answer! Do you have any idea how I'm supposed to be joining to the `users` table to actually make use of the information? Am I missing a `SELECT` somewhere? – Josh Smith Sep 07 '10 at 04:22
  • I'm also REALLY bugged that I can't select an answer until a certain amount of time has passed. Makes me long for Quora. – Josh Smith Sep 07 '10 at 04:24
  • @Josh Smith: The question is, what do you want/need to do? If you want the user information, you'll have to add the same joins to both sides of the UNION statement & make sure the columns fall into the right order based on the first query in the UNION. Additionally, if the `fid1` (or `fid2`) column can not be NULL, there's no need for a LEFT JOIN--use an [INNER] JOIN. – OMG Ponies Sep 07 '10 at 04:26
  • @Josh Smith: I don't recommend accepting immediately anyways--there's a chance of someone seeing something. – OMG Ponies Sep 07 '10 at 04:27
  • @OMG Ponies: I need to `JOIN` with the `users` table so I can get all of the information for the particular user's friend. Basically, the same information you would get if you were doing `SELECT * FROM users WHERE uid = someNum`. – Josh Smith Sep 07 '10 at 04:33
  • I've never really used subqueries, but maybe that's what I'm *really* wanting to do here? – Josh Smith Sep 07 '10 at 04:34
  • @Josh Smith: No, subqueries you'd use for testing existence of values. If you want related data in the output, you're looking at JOIN to a table or a derived table/inline view. See updated answer – OMG Ponies Sep 07 '10 at 04:35
  • Thanks for the update. That answer's close, but not quite what I wanted. If you simply omit `u2` from the left side of the `UNION` and omit `u1` from the right side, then you get the result I'm looking for. In other words, we don't want to get back the requesting user's information. I'm not sure, though, if there's something I'm missing there. I think there may be (possibly because `uid` = 1 is a bad choice for testing). – Josh Smith Sep 07 '10 at 04:42