Solved

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

Posted on 2012-03-28
5
334 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
ID: 37779050
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
ID: 37779062
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
ID: 37779077
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
ID: 37779084
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
ID: 37779267
worked great - the other solution worked, but was extremely slow in drawing on the screen
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

867 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

18 Experts available now in Live!

Get 1:1 Help Now