I have, to make it clear, 2 many-to-many relationships for table person:
Person Person_Club Club Person_Creditcard
CreditCard
-------------- ------------------- ---------------------
--------------------- ---------------------
person_id person_id club_id person_id
credit_id
first_name club_id name credit_id
company_name
last_name role_id additional_info additional_info
additional_info
middle_ini additional_info =========== ===========
============
gender ===========
========
to find people with specified club access and credit access, i have to compose sql
involving all
tables here. Here 'additional_info' stands for possibly multiple additional columns.
Suppose tables person and person_creditcard have large number of rows, and each club
has
a lot less members than any creditcard company does. What my sql should look like?
I'm looking at this:
select t0.* from person t0 where (...some conditions within table person...) and
t0.person_id in
(select t1.person_id from person_creaditcard t1, creditcard t2
where (...some conditoons about t1 and t2) and t1.person_id in
(select t3.person_id from person_club t3, club t4 where (...some conditions within t3,
t4) ) ) );
I'm hoping in this way, mysql gets smallest possible person_id set from sub query
about person_club and club,
and then use this to speet up the search on tables person_creditcard and creditcard
and finaly speed up the
person table search.
Any inputs? Thanks a lot
