Multiple table select issues :(

[darthmahon]

Hi,

I know this has been covered a million times but I am having trouble getting it to work for me.

I need to select events from two different tables and get the next event by date.

Here is what I am using:

$distributor = 
"SELECT 
	m.meetingid As meetingid, 
	TRIM(m.title) As title, 
	TRIM(m.description) As description, 
	DATE_FORMAT(m.date_start, '%d-%m-%Y') As date_start, 
	DATE_FORMAT(m.date_end, '%d-%m-%Y') As date_end,

n.networkuniid As networkuniid, 
TRIM(n.title) As title, 
TRIM(n.text) As text, 
DATE_FORMAT(n.date_start, '%d-%m-%Y') As date_start, 
DATE_FORMAT(n.date_end, '%d-%m-%Y') As date_end
FROM 
	MEETINGS as m, NETWORKUNI as n
WHERE
	date_start >= now()
ORDER BY date_start  DESC LIMIT 1";

Now, this doesn't seem to work too well - any ideas why?

Cheers,
Chris

[zhabala]

That is because you are doing so called cartesian product. It means that you will have the number of recods in table meetings times the number of records in table networkuni records returned back from the server for this select.

I am not sure, what exactly you need. But if the two tables has to be joined, then you have to join them like:

SELECT 
    m.meetingid As meetingid, 
    TRIM(m.title) As title, 
    TRIM(m.description) As description, 
    DATE_FORMAT(m.date_start, '%d-%m-%Y') As date_start, 
    DATE_FORMAT(m.date_end, '%d-%m-%Y') As date_end, 

n.networkuniid As networkuniid, 
TRIM(n.title) As title, 
TRIM(n.text) As text, 
DATE_FORMAT(n.date_start, '%d-%m-%Y') As date_start, 
DATE_FORMAT(n.date_end, '%d-%m-%Y') As date_end 
FROM 
    MEETINGS as m, NETWORKUNI as n 
WHERE 
    date_start >= now() AND
    m.meetingid = n.meetingid // !!!!!!!!
ORDER BY date_start  DESC LIMIT 1

if you need only to combine the results then you can use UNION clause or you have to run two select statements and then solve the order.

Z.

[darthmahon]

Thanks for the reply.

Ok I will have a play with UNION, I just want to combine the results...