CODEWITHSUNDEEP
phpsql
Jonathan Nungaray
Jonathan Nungaray

Reputation: 43

Two different tables in one output in MySQL

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:

  1. Thread number one dateline: today
  2. Like number one dateline: yesterday 1:10pm
  3. Thread number two dateline: yesterday 1:09pm
  4. Like number two dateline: yesterday 1:08pm
  5. Like number three dateline: yesterday 1:07pm
  6. 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

Upvotes: 0

Views: 78

Answers (2)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

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;

Upvotes: 0

melvin
melvin

Reputation: 1125

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;

Upvotes: 1

Related Questions