Closed Thread Icon

Preserved Topic: Multiple joins. (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=20926" title="Pages that link to Preserved Topic: Multiple joins. (Page 1 of 1)" rel="nofollow" >Preserved Topic: Multiple joins. <span class="small">(Page 1 of 1)</span>\

 
jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 06-06-2001 21:09

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

linear
Paranoid (IV) Inmate

From: other places
Insane since: Mar 2001

posted posted 06-07-2001 00:43

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.

jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 06-07-2001 16:35

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

linear
Paranoid (IV) Inmate

From: other places
Insane since: Mar 2001

posted posted 06-08-2001 04:32

Glad that worked.

What admin tool are you referring to?

jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 06-08-2001 18:18

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

« BackwardsOnwards »

Show Forum Drop Down Menu