Order by date from 2 different tables

I’m trying to figure out how to output data from 2 different db tables depending on the release dates…

Table1:
id, subject, content, date
Table1:
id, nsubject, ncontent, ndate

How do I get both into the query and then print in order of the dates…

Thanks in advance :slight_smile:

SELECT id, subject, content, `date`
  FROM Table1
UNION ALL
SELECT id, nsubject, ncontent, ndate
  FROM Table2
ORDER
    BY `date`

But what if the 2 fields don’t have the same amount of fields… This is the full script I want to work

Currently working:

$sql="SELECT u.fname, u.lname, u.new_userid, n.news, n.clubid, n.newsid, cl.clubname FROM ".$prefix."_club_users AS cu
		INNER JOIN ".$prefix."_clubnews AS n ON cu.clubid = n.clubid
		INNER JOIN ".$prefix."_users u ON u.new_userid = n.userid
		INNER JOIN ".$prefix."_club cl ON cl.clubid = cu.clubid
		WHERE cu.new_userid='$userid'
		ORDER BY n.dbdate DESC
		LIMIT 5";

Now this is the table I want to join with the clubnews somehow:

$sql="SELECT lang, subject, content FROM ".$prefix."_news WHERE lang=$lang ORDER BY date DESC";

Can this be done?

then you should not “dumb down” your original question to make it look as though the tables have the same number of similar columns

you’ll have to explain how the tables should be joined

Sorry… My bad.

I the “clubnews” I have 2 fields (news, dbdate) which is the ones I use. In the “news” table I have (subject, content, date) which is the fields of important allthough I have a “lang” filed which determens the language of the news… I want to join those 2 tables and order the news from them both by date/dbdate.

Hope this helps…

when you say you want to “join” these two tables, are you ~sure~ youe want to match the rows from them on some column?

or are you really looking to do a UNION?

I might want to be looking at UNION but not sure…

okay, that’s fair

perhaps you could go and do a bit of research on what UNION does?

after that, you might be in a better position to describe what you want to do with your queries and tables…

:slight_smile:

OK… Have been surfing around, and this is what I have come up with…

$sql="SELECT n.newsid, n.news, n.dbdate FROM ".$prefix."_clubnews n
					UNION
				  SELECT gn.id, gn.content, gn.date FROM ".$prefix."_GA_news gn WHERE gn.lang=213
				  	ORDER BY n.dbdate DESC LIMIT 5";

If I don’t user the n. or gn. it works fine, but how come I cant use those???

because the result set of a UNION query has unqualified column names

the column names are assigned in the first SELECT

so in your php code, you would reference newsid, news, and dbdate

alternatively, you can assign column aliases…

SELECT n.newsid [COLOR="Blue"]AS the_id[/COLOR]
     , n.news   [COLOR="blue"]AS the_con[/COLOR]tent
     , n.dbdate [COLOR="blue"]AS the_date[/COLOR]
  FROM ".$prefix."_clubnews n
UNION ALL
SELECT gn.id
     , gn.content
     , gn.date 
  FROM ".$prefix."_GA_news gn 
 WHERE gn.lang=213
ORDER 
    BY the_date DESC LIMIT 5

and then reference the_id, the_content, and the_date

but one thing still puzzles me

in post #3, those two queries return wildly different sets of columns, that really aren’t very amenable to a UNION (unlike post #1)