Checking whether record id exists in another query

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

Checking whether record id exists in another query

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-01-10 : 10:44:18

I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.

There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, any help on correcting it so I can see all the records whether Yes or No would be appreciated:

Select distinct
	
	pg.grid_id [Price Grid], 
	pg.grid_name [Grid Name],
	case when exists
	(Select
	c.grid_id from 
	customers c
	inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
	where 
	c.type = 'C' and
	dh.dticket is not null) then 'Yes' else 'No' end [Active]
	
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id

Order by pg.grid_id

Many thanks
Martyn

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-10 : 11:54:50

???[CODE]Select distinct

pg.grid_id [Price Grid],
pg.grid_name [Grid Name],
case when exists
(Select
c.grid_id from
customers c
inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
where
c.type = 'C' and
dh.dticket is not null) then 'Yes' else 'No' end [Active]

From gridhdr pg left outer join customers c on c.grid_id = pg.grid_id

Order by pg.grid_id[/CODE]

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-01-10 : 12:07:18

Nearly, returns all records now but even those not in use (i.e. do not appear in the query part of the case statement) show as Yes.

Thanks
Martyn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-10 : 14:32:04

quote:
any help on correcting it so I can see all the records whether Yes or No

then in your next post:

quote:
returns all records now but even those not in use

You've confused me.

Be One with the Optimizer
TG

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-10 : 16:40:37

Some schema definitions (CREATE TABLE), sample data (INSERT) and expected outputs would be instructive. Help us to help you. (We're trying, man, but you gotta give us a little).

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources