Page 1 of 1

problem with results from multiple table join

Posted: Wed Nov 20, 2002 4:14 pm
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

RE: problem with results from multiple table join

Posted: Wed Nov 20, 2002 10:32 pm
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)

Posted: Thu Nov 21, 2002 8:32 am
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.

Posted: Thu Nov 21, 2002 1:54 pm
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...

Posted: Thu Nov 21, 2002 8:26 pm
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:

Posted: Thu Nov 21, 2002 8:31 pm
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.

Apology to caseymanus

Posted: Fri Nov 22, 2002 8:05 am
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:

Posted: Fri Nov 22, 2002 11:31 pm
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.

Posted: Sat Nov 23, 2002 7:13 am
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?