MySQL Forums
Forum List  »  Newbie

Re: Why use JOINS when have FK
Posted by: Chad Bourque
Date: March 24, 2010 03:46PM

Andy,

Well that depends. If you want to see clients that don't have any products, you would just add the other tables in as left joins and add to the where clause in a similar manner. If, on the other hand, you want to see the all of the clients and all the products and which ones the client has, you need to pivot the data. Something like this could work:

select c.*,
    (i1.clientId is not null) insProd1,
    (i2.clientId is not null) insProd2,
    (i3.clientId is not null) insProd3
  from Clients c
    left join InsuranceProduct1 i1
      on c.clientId = i1.clientId
    left join InsuranceProduct2 i2
      on c.clientId = i2.clientId
    left join InsuranceProduct3 i3
      on c.clientId = i3.clientId;

And if you only want the clients that are missing at least one product, add a where clause like this to the above query:

where i1.clientId is null
  or i2.clientId is null
  or i3.clientId is null;

HTH,
Chad

Options: ReplyQuote


Subject
Written By
Posted
March 23, 2010 05:11AM
Re: Why use JOINS when have FK
March 24, 2010 03:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.