Query Help

Anon

I'm trying to perform a search on a database across multiple tables. There is a main table called 'events' with most of the data to be searched, but also several other tables that the main table has a foreign key to (ex. meetingroom). The problem is that much of the data stored in the other tables is optional, so a foreign key to it may or may not exist. For instance, a meetingroom was not chosen on a form, so the foreign key to it would be null in the Events table.

At first when making the search query, I simply joined all the tables through the appropriate foreign keys (WHERE events.meetingroom = meetingroom.id) and such. However, if a foreign key was null, an entire row will be left out of the results because it didn't meet the conditions of the join, even though a match for the search may have been found in the main table, or in other tables. Is there any way to change the following query so that it will still return rows when one of the foreign keys that is a condition for the join is NULL? Sorry, I'm a little weak in this area. Maybe a snippet from the query will help, I removed a lot of the extra detail.

SELECT Events.id, Events.title, Events.description, location.location, meetingroom.meetingroom, audience.audience

FROM Events, location, meetingroom, audience WHERE Events.location = location.id AND Events.meetingroom = meetingroom.id AND Events.audience = audience.id AND (Events.title LIKE '%searchterm%' OR Events.description LIKE '%searchterm%' OR location.location LIKE '%searchterm%' OR meetingroom.meetingroom LIKE '%searchterm%' OR audience.audience LIKE '%searchterm%')

Anon

Look up outer join in the documentation for your dbms.

Anon

Thanks for the tip.

Anon

Hmmm...ive done a bunch of tests and read the SQL Server 7 documentation on it, and I don't think an outer join is the solution. While it does allow the query to return values when the foreign keys are null, it also returns too many unnecessary rows. For instance, a search for 'summer' will return every row, whether or not there is a match, because the outer join is forcing it to return values that shouldn't be returned. I find that I cannot restrict or refine the results of the outer join, because whether or not any boolean expression is true or not, it's still being returned.

SELECT Events.id, Events.title , Events.description , location.location , meetingroom.meetingroom , audience.audience FROM Events, location, meetingroom, audience WHERE Events.location = location.id AND Events.meetingroom = meetingroom.id AND Events.audience *= audience.id AND ( Events.title LIKE '%summer%' OR Events.description LIKE '%summer%' OR location.location LIKE '%summer%' OR meetingroom.meetingroom LIKE '%summer%' OR audience.audience LIKE '%summer%')

flipis

I suggest you try this:

SELECT Events.id, Events.title , Events.description , location.location , meetingroom.meetingroom , audience.audience FROM Events INNER JOIN location ON location.id = Events.location INNER JOIN meetingroom ON meetingroom.id = Events.meetingroom INNER JOIN audience ON audience.id = Events.audience WHERE( Events.title LIKE '%summer%' OR Events.description LIKE '%summer%' OR location.location LIKE '%summer%' OR meetingroom.meetingroom LIKE '%summer%' OR audience.audience LIKE '%summer%')

After trying hard to refine the queries i found this command. It works faster than the one you put (at least on my MySQL machine)

Please, note thet the use of OR makes the data included. If you want to make a mroe restrictive search, use AND. This way, only the rows matching ALL of the criteria, and not just one, will be returned.

Hope this helps

fLIPIS

Anon

Cool. I took your advice with the explicit JOIN keywords, except i used some new methods together with LEFT OUTER JOIN. It works great now. Thanks for the help, Lars and fLIPIS.