Exclude all records from a join which have two specific values in one table

Question

Post reply

Exclude all records from a join which have two specific values in one table

tasdavid

Newbie

Points: 5
More actions
January 20, 2010 at 3:32 pm

#219732

Hi all,
I'm a newbie struggling to script up a query to exclude all records from one table which have two specific values. I then want to join the results to another table to get appropriate information from it.
Table constits has several columns and the important ones are:
Sequence (Primary key)
ID (Many to one in the table I want to join)
Constit
example of constits data:
Sequence ID Constit
1 345 PC
2 345 STF
3 12 PC
4 23 STF
5 62 PC
Community table data
ID Surname GivenName
12 Bloe Joe
23 Bloggs Bert
345 Crun Henry
62 Bannister Minnie
I want to join community to constits to give me all records with a constit of PC but exclude those who have both PC and STF. So results from the above data should give me:
12 Bloe Joe
62 Bannister Minnie
I tried using EXCEPT but I'm still not getting the desired results.
Any help/examples greatly appreciated
Cheers
David

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

richardsouthall Valued Member Points: 65 More actions · Answer 1

Hope this what you're looking for:

select surname, givenname

from community as cm join constits as cn on cm.id = cn.id

where cn.constit = 'PC'

and not exists ( select constits.id from

constits where constits.id = cn.id

and constits.constit <> 'PC' )

More accurately for your requirement substitute = 'STF' for <> 'PC'.

Have fun.

tasdavid Newbie Points: 5 More actions · Answer 2

Sorry Andrew,

that query returned no results at all.

One acperkins submitted the following which does work:

SELECT c.ID, c.Surname, c.GivenName

FROM Community c

INNER JOIN (

SELECT ID,

MIN(Constit) Constit

FROM (

SELECT ID, Constit

FROM Constits

WHERE Constit IN ('PC', 'STF')

GROUP BY

ID, Constit) a

GROUP BY

ID

HAVING

COUNT(*) = 1) b ON c.ID = b.ID

WHERE b.Constit = 'PC'

And whilst I dont fully understand it, it does the job perfectly so I'm happy.

Cheers

David

WayneS SSC Guru Points: 95460 More actions · Answer 3

tasdavid (1/20/2010)
And whilst I dont fully understand it, it does the job perfectly so I'm happy.

Might I recommend that until you do understand what this code is doing, that you do not just move it into any production environment. If you don't understand it, then you will never know if any problem you may run into is due to this code or not.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

tasdavid Newbie Points: 5 More actions · Answer 4

Thanks for the sound advice Wayne,

I'm only using it to extract data for something else outside of the database. It will never be used to inject data back in and once I get a handle on what it does I will also feel a lot happier.

As I say, I'm new to SQL and learning all the time.

Cheers

David

richardsouthall Valued Member Points: 65 More actions · Answer 5

Hi David,

I'm not sure who Andrew is, but I'm baffled as to why the code I posted didn't work since it was a cut and paste from a working query that gave your intended results, excepting the id column.

This was my first attempt at a reply and it highlights the standing recommendation to include as much information as possible - in this case a complete script for the table build, population, and query in the post.

Glad you have a solution!

richard

tasdavid Newbie Points: 5 More actions · Answer 6

Oops my apologies Richard,

Too much assaulting my brain when I replied.

I've just retried your code as is using <> 'PC' (I used <> 'STF' when I got no results) and this time I get results but many of the records I want to see are missing. Not sure why but when I have a moment I'll investigate further.

The other code I got does return all the results I want so I'll stick with that.

Many thanks for all your help. Its greatly appreciated.

Cheers

David

tasdavid Newbie Points: 5 More actions · Answer 7

Hello again Richard,

I re-examined the logic of what you submitted and realised the last line is the key.

By changing

and constits.constit <> 'STF'

to

and constits.constit = 'STF'

I get the results I wanted and the beauty is that I understand the logic. This works because we are testing against NOT EXISTS.

So the full code becomes:

SELECT

cm.ID,

cm.Surname,

cm.Preferred,

cn.Constit

FROM Community AS cm JOIN Constits AS cn ON cm.ID = cn.ID

WHERE cn.Constit = 'PC'

AND NOT EXISTS (

SELECT Constits.id

FROM Constits

WHERE Constits.id = cn.id

AND Constits.Constit = 'STF' )

ORDER BY cm.Surname, cm.Preferred;

Once again Many thanks for your assistance

Cheers

David