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";
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.
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)