Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

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
0
terpsichore
Asked:
terpsichore
1 Solution
 
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
 
Rey Obrero (Capricorn1)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
 
terpsichoreAuthor Commented:
worked great - the other solution worked, but was extremely slow in drawing on the screen
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now