Query on multiple rows in single table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query on multiple rows in single table

Author

Topic

slaquer
Starting Member

5 Posts

Posted - 2014-08-01 : 22:40:15

I have a table named LEDGER
LEDGER has two columns named PATID and CODE

Example data:

PATID CODE
1 Z1110
1 D3330
1 Z0330
2 Z1298
2 Z0987
2 Z0330
2 D1092

I need a query that returns PATID if they have CODE Z0330 but not Z1110. I only want one PATID to return if this condition exists.

I thought this would be easy but I can't get anything to work. Any ideas? Thanks!

slaquer
Starting Member

5 Posts

Posted - 2014-08-01 : 23:04:07

I just realized this needs to be a little harder:

I have a table named LEDGER
LEDGER has three columns named PATID, CODE and TRANDATE

Example data:

PATID CODE TRANDATE
1 Z1110 07-15-1970
1 D3330 07-15-1970
1 Z0330 07-15-1970
2 Z1298 07-20-1970
2 Z0987 07-20-1970
2 Z0330 07-20-1970
2 D1092 07-20-1970
3 Z0330 08-20-1980
3 Z1110 02-11-1982

I need a query that returns PATID if they have CODE Z0330 but not Z1110 AND both codes appear on the same TRANDATE. I only want one PATID to return if this condition exists. In the example above, the output should be:

PATID CODE TRANDATE
2 Z0330 07-20-1970
3 Z0330 07-20-1980

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-08-02 : 05:45:23

[code]
SELECT *
FROM Table t
WHERE CODE = 'Z0330'
AND NOT EXISTS (SELECT 1
FROM Table
WHERE PATID = t.PATID
AND CODE = 'Z1110'
AND TRANDATE = t.TRANDATE
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-02 : 06:01:03

What about this test data?

DECLARE	@Ledger TABLE
	(
		PATID INT,
		CODE CHAR(5),
		TRANDATE DATE
	);

INSERT	@Ledger
	(
		PATID,
		CODE,
		TRANDATE
	)
VALUES	(1, 'Z1110', '19700715'), 
	(1, 'D3330', '19700715'),
	(1, 'Z0330', '19700715'),
	(2, 'Z1298', '19700720'),
	(2, 'Z0987', '19700720'),
	(2, 'Z0330', '19700720'),
	(2, 'D1092', '19700720'),
	(3, 'Z0330', '19800820'),
	(3, 'Z0330', '19820211'),
	(3, 'Z1110', '19820211');

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

slaquer
Starting Member

5 Posts

Posted - 2014-08-02 : 08:38:39

This worked - the only one that did exactly what I needed. Thanks a lot. Breaking this down (trying to learn), what does this do: "AND NOT EXISTS (SELECT 1 FROM Table" - what does the select 1 do?

quote:
Originally posted by visakh16

SELECT *
FROM Table t
WHERE CODE = 'Z0330'
AND NOT EXISTS (SELECT 1
FROM Table
WHERE PATID = t.PATID
AND CODE = 'Z1110'
AND TRANDATE = t.TRANDATE
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-08-02 : 10:29:14

quote:
Originally posted by slaquer

This worked - the only one that did exactly what I needed. Thanks a lot. Breaking this down (trying to learn), what does this do: "AND NOT EXISTS (SELECT 1 FROM Table" - what does the select 1 do?

quote:
Originally posted by visakh16

SELECT *
FROM Table t
WHERE CODE = 'Z0330'
AND NOT EXISTS (SELECT 1
FROM Table
WHERE PATID = t.PATID
AND CODE = 'Z1110'
AND TRANDATE = t.TRANDATE
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

the NOT EXISTS will make sure it returns only those cases which doesnt have record with Z1110 as code existing for the same date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources