terpsichore
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
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
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.
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.
try this query
select Project_ID
from ProjectParts Left Join Active_Quotes_Parts
ON ProjectParts.Part_ID=Activ e_Quotes_P arts.Part_ ID
Where Active_Quotes_Parts.Part_I D isNull
select Project_ID
from ProjectParts Left Join Active_Quotes_Parts
ON ProjectParts.Part_ID=Activ
Where Active_Quotes_Parts.Part_I
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
worked great - the other solution worked, but was extremely slow in drawing on the screen
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)