Two different tables in one output in MySQL

Question

SELECT t.dateline AS date,t.tid, t.subject, u.avatar,t.views, t.username, t.replies, u.profilepic, t.uid, p.thumbsup, t.firstpost, f.name, f.fid, p.message, a.updatetime, a.md5hash, a.uploadtime, a.aid, a.attachname, a.filename, a.thumbs, td.vidid, td.cat, td.portada
FROM ". TABLE_PREFIX ."threads t 
LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
LEFT JOIN ".TABLE_PREFIX."forums f ON (f.fid=t.fid)
LEFT JOIN ".TABLE_PREFIX."xtattachments a ON (a.tid=t.tid)
LEFT JOIN ".TABLE_PREFIX."threadfields_data td ON (td.tid=t.tid)
LEFT JOIN ".TABLE_PREFIX."posts p ON (p.pid=t.firstpost)
WHERE t.fid IN ($f4id) AND t.uid IN ($show_post_list)  

UNION ALL

SELECT th.dateline AS date, th.thumbsup, th.uid
FROM ". TABLE_PREFIX ."thumbspostrating th 


ORDER BY date DESC 
LIMIT ".(($page-1)*$perpage).", ".$perpage);

Example:

Thread number one dateline: today
Like number one dateline: yesterday 1:10pm
Thread number two dateline: yesterday 1:09pm
Like number two dateline: yesterday 1:08pm
Like number three dateline: yesterday 1:07pm
Thread number three dateline: yesterday 1:05pm

I don’t know why this doesn’t work? In ORDER BY <-- I need t.dateline and l.dateline in to one for do that example.

1222 - The used SELECT statements have a different number of columns

1222 - The used SELECT statements have a different number of columns Of course t.uid and th.uid — Jonathan Nungaray
– Jonathan Nungaray, Commented May 4, 2014 at 18:12
Are you having a laugh? You've completely changed the query and effectively the question. — Tony Hopkinson
– Tony Hopkinson, Commented May 4, 2014 at 20:12
This question appears to be off-topic because it changes each time you answer it. — Tony Hopkinson
– Tony Hopkinson, Commented May 4, 2014 at 20:15

melvin · Accepted Answer · 2014-05-04 18:01:48Z

1

You can try to do add as dateline to your select SELECT l.dateline as dateline , l.name, l.avatar.

That worked for me. If that does not work you can always do a select on your selects and then order that result it would read something like this

SELECT dateline, name, avatar FROM ( 
  SELECT t.dateline, t.name, u.avatar
  FROM ". TABLE_PREFIX ."threads t 
  LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)

  UNION ALL

  SELECT l.dateline, l.name, l.avatar
  FROM ". TABLE_PREFIX ."likes l 
  LEFT JOIN ".TABLE_PREFIX."treads t ON (t.uid=l.uid)
) as x
ORDER BY x.dateline DESC 
LIMIT 10;

answered May 4, 2014 at 18:01

melvin

1,1258 silver badges12 bronze badges

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

6 Comments

Avinash Babu Over a year ago

is uid a column here ??

Jonathan Nungaray Over a year ago

It sends and error: 1222 - The used SELECT statements have a different number of columns

melvin Over a year ago

Well you changed the complete query i see but if i see your new query indeed you change the number of columns and they are not equal. For union its necessary to have the same number of columns

Jonathan Nungaray Over a year ago

Its for this example :/ the threads has more information : Thread number one dateline: today Like number one dateline: yesterday 1:10pm Thread number two dateline: yesterday 1:09pm Like number two dateline: yesterday 1:08pm Like number three dateline: yesterday 1:07pm Thread number three dateline: yesterday 1:05pm

melvin Over a year ago

@JonathanNungaray If threads has more information and you really want to show it you still have to make sure that they have the same nr of colums. You could achieve this by simply adding something like this "" as username to assign empty values to virtual columns but if the data is that differnt i would not use a union because you have to do that for every column that threads has extra (and in the correct place)

|

Giacomo1968 · Accepted Answer · 2014-05-04 18:23:56Z

0

The ORDER BY is only applied to the second query:

Select * From (
SELECT t.dateline, t.name, u.avatar
FROM ". TABLE_PREFIX ."threads t 
LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)

UNION ALL

SELECT l.dateline, l.name, l.avatar
FROM ". TABLE_PREFIX ."likes l 
LEFT JOIN ".TABLE_PREFIX."treads t ON (t.uid=l.uid) ) allData

ORDER BY allData.dateline DESC 
LIMIT 10;

edited May 4, 2014 at 18:23

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered May 4, 2014 at 18:00

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

Collectives™ on Stack Overflow

Two different tables in one output in MySQL

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related