-2

It's been a while since I had to write raw MySQL queries and lost touch a bit. I have a following situation in tables

Company     Client_Company
ID NAME     CLIENT_ID COMPANY_ID 
-------     --------------------
1  X        10         1 
2  Y        12         1
3  Z        36         3

My scenario does not really involve a client but I will use 'client' as don't want to get into explaining the exact entity I'm using here.

So imagine I can associate companies with clients through a select box within client editing area. What I want to do is that every time next company is assigned to the same client, that company must not appear in the dropdown anymore.

So basically I need to select all records from the Company table, to display as available options for a client with ID=10 but excluding company with ID=1 (which is already assigned to client 10). How would I do that?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
belinea
  • 123
  • 1
  • 9

1 Answers1

-1

This is a simple exclusion join:

SELECT c.ID, c.NAME
FROM Company AS c
LEFT OUTER JOIN Client_Company AS cc ON c.ID=cc.COMPANY_ID AND cc.CLIENT_ID=10
WHERE cc.COMPANY_ID IS NULL
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828