problem with results from multiple table join

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
k9kid
Forum Newbie
Posts: 3
Joined: Sat Nov 16, 2002 10:24 pm

problem with results from multiple table join

Post by k9kid »

:?: I have multiple tables cross-referenced by meeting_id. I need information from the main table (meetings_table) when criteria are met in any of several other tables. I find myself stymied by the results showing that a query containing 'AND ((A.w OR A.x) OR (B.y OR B.z))' will only give me results from the last '(B.y OR B.z)' part of the query. My other thought was to do individual queries into temp tables, then do a join of the temp tables -- would this be an workable method? Or is there a better way? What to do??? Here is some of the actual query code -- thanks for any help -- :

Select meetings_table.*,vet_table.*,vet_alt_comp_table.*
FROM meetings_table,vet_table,vet_alt_comp_table
WHERE (meetings_table.meeting_type LIKE '%1%'
OR meetings_table.meeting_type LIKE '%3%')
AND meetings_table.meeting_id=vet_table.meeting_id
AND meetings_table.meeting_id=vet_alt_comp_table.meeting_id
AND ((vet_table.cardiology='Y' OR vet_table.surgery='Y')
OR (vet_alt_comp_table.homeopathy='Y' OR vet_alt_comp_table.tcm_acupuncture='Y'))
ORDER BY start_date
User avatar
caseymanus
Forum Commoner
Posts: 34
Joined: Wed Nov 20, 2002 10:32 pm
Contact:

RE: problem with results from multiple table join

Post by caseymanus »

To start, this is going to be a very expensive query. Using ORs and Likes can make a query very slow. Especially if your join fields are not indexed.
But to answer your original question, its really a matter of how you have your criteria grouped.
try something more like this...

select * from mytable where
first condition
and
(second condition or third condition)
and
more conditions
and
(more conditions or more conditions)

Try to eliminate your like statements, even if it means using sub - queries


you did not specify which database your using, so that can make a difference also...some database handle inner joins and outer joins differently.....

Also, if this is a query thats going to be used alot, consider creating a view, and then selecting data from that view.


8)
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

caseymanus: Watch out - subqueries and views are not supported in MySQL yet. As a new MySQL user from an Oracle background, this gets me all the time.

As to k9's original question, it sounds like you need some "left joins" in there to allow data in a table to have no matches and still be able to return results.

I would try:

Code: Select all

SELECT mt.*,vt.*,vact.* 
FROM meetings_table mt
LEFT JOIN vet_table vt ON mt.meeting_id=vt.meeting_id 
LEFT JOIN vet_alt_comp_table vact ON mt.meeting_id=vact.meeting_id 
WHERE
   (mt.meeting_type LIKE '%1%' OR mt.meeting_type LIKE '%3%') 
   AND ((vt.cardiology='Y' OR vt.surgery='Y') 
      OR (vact.homeopathy='Y' OR vact.tcm_acupuncture='Y')) 
ORDER BY mt.start_date
but you may need to add some "is NULL" checks to the VT and VACT tables to allow the possibility that data is not coming from those tables.
User avatar
caseymanus
Forum Commoner
Posts: 34
Joined: Wed Nov 20, 2002 10:32 pm
Contact:

Post by caseymanus »

No where did he state he is using mySQL, but your right , sub-queries are not supported, .. I really wish people would post the platform they are using with every question .. IE mySQL, MS SQL, Oracle, PostgreSQL...
k9kid
Forum Newbie
Posts: 3
Joined: Sat Nov 16, 2002 10:24 pm

Post by k9kid »

Thanks for the replies. I apologize -- I did mean to mention that the database is MySQL, so Views and Subselects are out. It looks like the left joins will work. But... I also seem to be seeing that it IS taking a long time with this search -- can't be sure it is the LIKE part that is doing it or the LEFT JOIN. I will experiment and let you know. I am also considering the creation of meeting_id arrays (basically partial selects) and working with the arrays to create the final list of meetings -- any thoughts on this? :wink:
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

to see a description of your query and see what its doing and why it might be taking so long try adding EXPLAIN to the beggining of it and view the results in a DB tool like phpMyAdmin or MySQL-Front.
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Apology to caseymanus

Post by Rob the R »

caseymanus: you're right. I did assume MySQL. :oops: I will try to be more broadminded next time.

Of course, I could avoid that if everyone would just restrict their questions to MySQL in this forum. Being broadminded is just way too much trouble. :wink:
User avatar
caseymanus
Forum Commoner
Posts: 34
Joined: Wed Nov 20, 2002 10:32 pm
Contact:

Post by caseymanus »

I dont think we should restrict this to MySQL, because its intended for database questions related to PHP, and we all use different databases. I for one use Oracle, Postgres, and MS SQL more than MySQL.
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

I agree, of course. I guess I'm not good at indicating sarcasm in my posts. Which one is the sarcasm emoticon?
Post Reply