query returning result of only one possible type - DML (SELECT, INSERT, UPDATE, DELETE) Questions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » query returning result of only one possible type

query returning result of only one possible type

Options

Previous Topic · Next Topic

dmikester1

Posted: Monday, June 23, 2008 4:42:43 PM

Rank: Newbie

Joined: 6/23/2008
Posts: 4
Points: 12
Location: WI

OK, so my subject isn't that great. I have two tables TAXMASTER and TAXOWNER linked by an ID called PRPID. Here is my sample query which I will be asking about.

SELECT DISTINCT TAXMASTER.LGPIN, TAXMASTER.TAXID, TAXMASTER.PRPID, LU_PEOPLE.FNAME, LU_PEOPLE.LNAME,
TAXMASTER.TXYEAR, TAXOWNER.PBILL FROM ((TAXMASTER LEFT JOIN TAXOWNER ON
TAXMASTER.PRPID=TAXOWNER.PRPID) LEFT JOIN LU_PEOPLE ON TAXOWNER.PEID=LU_PEOPLE.PEID) WHERE
TAXOWNER.PBILL='0'

So at the end of the query when TAXOWNER.PBILL='0', then the person returned is a co-owner. When TAXOWNER.PBILL='1', then the person returned is a primary owner. I want to get all the results where TAXMASTER.PBILL='0' and there is no PBILL of '1' for that person. How would I do that?
Thank you
Mike

Scott Whigham

Posted: Monday, June 23, 2008 4:56:23 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Code:

SELECT DISTINCT tm.LGPIN, tm.TAXID, tm.PRPID, ppl.FNAME, ppl.LNAME, tm.TXYEAR, to.PBILL
FROM TAXMASTER tm LEFT JOIN TAXOWNER to
ON tm.PRPID=to.PRPID
LEFT JOIN LU_PEOPLE
ON to.PEID=ppl.PEID
WHERE to.PBILL='0'
AND NOT EXISTS (SELECT * FROM TAXOWNER sub WHERE sub.PRPID=to.PRPID and sub.PRPID <> '1')

I can't really make sure about this as I'm not really 100% sure what you're asking. Does this solve it?

dmikester1

Posted: Monday, June 23, 2008 5:05:30 PM

Rank: Newbie

Joined: 6/23/2008
Posts: 4
Points: 12
Location: WI

OK, I kind of understand the query you sent back. I don't understand what the 'sub's mean though. I tried that exact query spelling out all the table names and got 0 results.
Thanks
Mike

Scott Whigham

Posted: Monday, June 23, 2008 5:12:59 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Yikes - just noticed a bug in the subquery:

Code:

Previously I had sub.PRPID <> '1'

dmikester1

Posted: Tuesday, June 24, 2008 9:39:42 AM

Rank: Newbie

Joined: 6/23/2008
Posts: 4
Points: 12
Location: WI

Awesome. I had to change it to " sub.PBILL = '1' " because I wanted to remove any results that had PBILLs of '1'. But that query worked great! Thank you very much.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.