Error: The used SELECT statements have a different number of columns

by

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.

More Related Contents:

  1. You can’t specify target table for update in FROM clause
  2. Generating a range of numbers in MySQL
  3. How to get next/previous record in MySQL?
  4. How do I make a row generator in MySQL?
  5. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  6. MySQL number of items within “in clause”
  7. Error 1046 No database Selected, how to resolve?
  8. What’s the difference between VARCHAR and CHAR?
  9. Alter a MySQL column to be AUTO_INCREMENT
  10. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  11. MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
  12. is there a group_concat function in ms-access?
  13. single fixed table with multiple columns vs flexible abstract tables
  14. MySQL remove duplicates from big database quick
  15. mysql, iterate through column names
  16. How to delete from select in MySQL?
  17. How to delete Duplicates in MySQL table
  18. How to ORDER BY a SUM() in MySQL?
  19. mySQL Returning the top 5 of each category
  20. Selecting Random Rows in MySQL
  21. Use A Union Or A Join – What Is Faster [closed]
  22. MySQL – how to front pad zip code with “0”?
  23. Find the number of columns in a table
  24. Mysql, reshape data from long / tall to wide
  25. MySQL: Compare differences between two tables
  26. Mysql strip time component from datetime
  27. How do I start auto increment from a specific point?
  28. I want to copy table contained from one database and insert onto another database table
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment