asked on

SQL query - easy for someone who knows their stuff...

Dear expert - I need help with a query.
I have 3 tables:
Projects - list of projects, with key field Project_ID
Project_Parts - contains Project_ID and Part_ID (it is a list of all parts, by project)
Active_Quotes_Parts - contains Part_ID and other fields - list of all parts that are part of an active quote.

NOW: I want to generate a list of Projects (Project_ID) for which there are parts that are NOT part of active quotes.

(I also have a project table, that lists all projects, for what it's worth).

I tried something along the lines of the attached, with no luck...

Any ideas?
Capture.PNG

OCDan

this should work:
SELECT *
FROM projects p
JOIN project_parts pp ON pp.project_id = p.project_id
WHERE
pp.part_id NOT IN (SELECT part_id FROM active_quotes_parts)

Kelden

This should give you a list of projects

select distinct projects.*
from projects
inner join project_parts on projects.project_id = project_parts.project_id
where project_parts.part_id not in ( select part_id from active_quotes_parts )

I used distinct so that you don't get the projects multiple times when you have more parts that are not in any active quote.

Rey Obrero (Capricorn1)

try this query

select Project_ID
from ProjectParts Left Join Active_Quotes_Parts
ON ProjectParts.Part_ID=Active_Quotes_Parts.Part_ID
Where Active_Quotes_Parts.Part_ID isNull

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

terpsichore

ASKER

worked great - the other solution worked, but was extremely slow in drawing on the screen