Tough SQL Query: I need your help 4

[ujohnc00]

Hello all.

I am having a lot of trouble coming up with the query I need to do the following:

I have 3 tables involved in the query:
Projects, Contracts, ProjectContract

The ProjectContract table relates the other two tables by a project_id and a contract_id. My goal is to find the records whose link is broken. A project and a contract are considered related if they have the same 'title' value. So my logic says to find those records that have the same 'title', but whose 'project_id' and 'contract_id' fields are not related in ProjectContract.

Any suggestions?
Thanks for your time.

 

[CasperTFG]

SELECT P.Title AS P_Title, C.Title AS C_Title
FROM PROJECTS AS P
INNER JOIN CONTRACTS AS C ON C.Title = P.Title
WHERE P.ProjectID NOT IN
(SELECT PC.ProjectID FROM ProjectContracts AS PC)
OR P.ContractID NOT IN
(SELECT PC.ContractID FROM ProjectContracts AS PC)


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[ujohnc00]

Casper, thank you so much for your prompt guidance.

Your query is almost what I need. Whereas your query looks for the records that are not in the ProjectContract table, what I need are those records that are not directly linked to eachother in that table, although other records may exist. So if a project and a contract have the same title, I am looking for those records whose respective ids (project_id and contract_id) are not associated in the ProjectContract.

Let me know if you need me to elaborate and I will try.

Thanks again.

 

[donutman]

I don't think the NOT IN clauses will always work because both the ProjectID and ContractID could be in ProjectContracts. They just might not be in the same row.

SELECT P.Title AS P_Title, C.Title AS C_Title 
FROM PROJECTS AS P
INNER JOIN CONTRACTS AS C ON C.Title = P.Title 
Left Join ProjectContracts PC 
   on (PC.ProjectID=P.ProjectID and PC.ContractID=C.ContractID)
where PC.ProjectID is null

-Karl


[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[maswien]

select * from contract c join project p on c.title = p.title
where not exists
(
select 1 from pc where pc.cid = c.cid and pc.pid = p.pid
)

 

[ujohnc00]

That's what I was looking for Mjia.
Cheers!

 

[donutman]

Mjia, doesn't a not exist subquery have to be executed for every row of the main query? Correct me if I'm wrong (cuz I'm not sure), but a left join should be faster.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[maswien]

Karl,

I turned on the show plan option in query analyzer, I found these two queries are almost the same from the performance point of view.

Only one observation may need to be pay attention to, the EstimateCPU time for yours is a little biger than mine. I'm
not sure if that means mine is better than yours, I want to see your opinion on this.

 

[donutman]

I'm somewhat embarrassed to say that I've never used the Show Plan Query Analyzer except in a class that I took years ago. I guess I should work on becoming proficient with it.
I'm very surprised by the result. I'm guessing that it means that the QA optimizer knows how to avoid doing a query on every row, otherwise the result makes no sense to me.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]