Preserved Topic: Multiple joins. (Page 1 of 1)

So i have this query that isn't working out:

SELECT Event.start_time, Title.title, Location.location FROM (Event NATURAL LEFT JOIN Title) NATURAL LEFT JOIN Location ORDER BY Event.start_time;

Basically Event has fields titled title_id and location_id that match up with the fields in Title and Location respectively. I just want a table that has an event's time followed by title and location. But this query repeats each event 6 times (one for each location in the Location table). I think the reason is that both the Location and Title tables contain an 'onMenu' field which it appears to be joining on. Unfortunately specifying the second join as a regular LEFT JOIN Location USING (location_id) gives me an error saying that the table Title doesn't have a location_id attribute, argh!!!!

-jiblet

SELECT Event.start_time, Title.title, Location.location FROM (Event ,Title, Location) WHERE ((condition) AND (condition));

Ought to do it.

Why is this in separate tables? This seems needlessly complex.

Fully normalized database. Many events share the same title or location. Aside from the redundancy reduction, the admintool also puts common titles and locations on a menu next to the textfield so you don't even have to type it.

Your solution using WHERE clauses seems to do the trick nicely. I guess I was trying to accomplish too much in the join thinking that it would be more efficient than a WHERE clause, but ultimately the engine has to check equivalency either way I suppose.

Thanks.

-jiblet

Glad that worked.

What admin tool are you referring to?

The suite of web forms I'm developing to maintain the database.

If you want to see it I can send you the full source code and give you access to the actual pages provided u don't change any events Drop me an email if u interested: jiblet@visi.com

-jiblet