Solved

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

Posted on 2012-03-28
5
321 Views
Last Modified: 2012-03-28
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
Comment
Question by:terpsichore
5 Comments
 
LVL 9

Expert Comment

by:OCDan
Comment Utility
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
 
LVL 2

Expert Comment

by:Kelden
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:terpsichore
Comment Utility
worked great - the other solution worked, but was extremely slow in drawing on the screen
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now