Mohammad Mohammad - 6 months ago 13
MySQL Question

Select from table where id is not in another

I want to select all the planes that aren't belong to a certain company. I have three tables in this case:

Planes
,
Companies
, and
CompanyPlanes
.

Here is my query:

SELECT *
FROM planes p,
companyplanes cp,
companies c
WHERE c.id = ?
AND cp.idCompany != c.id
AND (cp.idPlane = p.id OR p.id NOT IN (SELECT idPlane FROM companyplanes))
ORDER BY name ASC


But this query returned nothing! what is the wrong here?

example:

| Plane |
---------
id | name
---------
1 | p1
2 | p2
3 | p3


|Company|
---------
id | name
---------
1 | c1
2 | c2

| companyPlanes |
------------------------
id | idCompany | idPlane
------------------------
1 | 1 | 1
2 | 1 | 2
3 | 2 | 2


if I want to get the planes that are not belong to the company
c2
the result should be: p1, p3.

Answer

Update Answer

We can get the result in following way

  1. Get all planes of the unexpected company

    SELECT idplane from CompanyPlanes WHERE idCompany = ?

  2. Get all planes without those planes of the unexpected company

    SELECT * FROM Planes WHERE id NOT IN ( SELECT idplane from CompanyPlanes WHERE idCompany = ? )

You don't need to join with Company table as you already get idCompany from CompanyPlanes table.

Comments