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
terpsichoreAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
sorry typo

try this query


select Project_ID
from Project_Parts Left Join Active_Quotes_Parts
ON Project_Parts.Part_ID=Active_Quotes_Parts.Part_ID
Where Active_Quotes_Parts.Part_ID is Null
0
 
OCDanCommented:
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)
0
 
KeldenCommented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
terpsichoreAuthor Commented:
worked great - the other solution worked, but was extremely slow in drawing on the screen
0
All Courses

From novice to tech pro — start learning today.