MySQL Forums
Forum List  »  Newbie

Re: Why use JOINS when have FK
Posted by: Chad Bourque
Date: March 24, 2010 08:54AM

Andy,

Quote
Andy
i have the constraints setup using workbench, that ok? was not sure what column i clicked first for referncing though but it works no problem.

Using Workbench should be just fine. IIRC, you click on the child table (the one you are adding the foreign key constraint to) first, then click on the parent / referenced table (the one with the data as the primary key).

Quote
Andy
One thing is though when i tried to import the .sql file it did not let me unless i deleted the FK then added them manually via php myadmin. wierd??

The only thing I could think it might be is that when I generate scripts from Workbench, it adds the database name in the foreign key constraints even when I check the box that says to omit the database names. This causes problems when I run the script to create the tables in a database of a different name (which can be common on hosted boxes). To solve this, I always do a search and replace of `databaseName`. with an empty string on the the script it creates. That may or may not be your issue.

Now, about your query... You didn't really provide enough information. But, in general, to get a list of clients that don't have a row in the InsuranceProduct1 table, you could do something like this:

select c.*
  from Clients c
    left join InsuranceProduct1 i
      on c.clientId = i.clientId
  where i.clientId is null;

The left join means get all clients. If they have the product, those data columns in the result (if you chose to pull them) would have data in them. If they don't have the product, those data columns would be null. The where clause filters out all of the clients that have the product leaving you with just the clients that don't have the product.

HTH,
Chad

Options: ReplyQuote


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


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.