February 27, 2014 at 4:20 am
Hi,
I am facing an issue with displaying data for a BOOKING_ID as per the where clause based on a column (FLAG). What I need to achieve is if for one BOOKING_ID the FLAG is Y, then the data should not be displayed.
Also, similarly, if there are 2 rows for the same BOOKING_ID and the values for the 2 FLAGS are 'Y' and 'N' then it should not display the 2 rows at all (because there is a row present for FLAG = 'Y').
For. eg.
We have the following table:
BOOKING_ID BOOKING_COMMODITY_ID FLAG
1000 10 N
1200 11 N
1001 1 Y
1001 2 N
In the above scenario, only the following rows should be returned:
1000 10 N
1200 11 N
I tried to achieve this through this code:
Where FLAG <> 'Y'. This returns the following rows, which is incorrect:
1000 10 N
1200 11 N
1001 2 N
I dont know if I am able to explain the problem clearly. Could somebody please tell what needs to be changed in order for this to work correctly ?
Thanks.
February 27, 2014 at 4:46 am
Without table definition and usable sample data I'm guessing, but...
WHERE NOT EXISTS (SELECT 1 FROM <Table Name> ib WHERE ib.Booking_ID = <outer table reference>.Booking_ID AND Flag = 'Y')
If you want more than that, you're going to have to at least give me the table's definition.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2014 at 5:23 am
GilaMonster (2/27/2014)
Without table definition and usable sample data I'm guessing, but...
WHERE NOT EXISTS (SELECT 1 FROM <Table Name> ib WHERE ib.Booking_ID = <outer table reference>.Booking_ID AND Flag = 'Y')
If you want more than that, you're going to have to at least give me the table's definition.
Thanks but it doesnt work. What I wish to achieve is no rows should be displayed where DG_FLG = 'Y' and OOG_FLG = 'Y'. This applies to the scenario based on the attached sample data that even if there are multiple rows for 1 BOOKING_ID (103) and 1 of them has the value OOG_FLG = 'Y' , it should hide both the rows in this case.
Please find the table structure and sample data below:
----------------------------------------------
CREATE TABLE [dbo].[BOOKING](
[BOOKING_ID] [numeric](10, 0) NOT NULL,
[DG_FLG] [char](1) NULL,
)
CREATE TABLE [dbo].[BOOKING_COMMOD]
(
[BOOKING_ID] [numeric](10, 0) NULL,
[BOOKING_COMMODITY_ID] [numeric](10, 0) NOT NULL,
[OOG_FLG] [char](1) NULL
)
-------------------------------------------------
insert into [BOOKING]
values('100','N')
insert into [BOOKING]
values('101','Y')
insert into [BOOKING]
values('102','N')
insert into [BOOKING]
values('103','N')
insert into BOOKING_COMMOD
values('100', '1','Y')
insert into BOOKING_COMMOD
values('101', '2','N')
insert into BOOKING_COMMOD
values('102', '3','N')
insert into BOOKING_COMMOD
values('103', '4','N')
insert into BOOKING_COMMOD
values('103', '5','Y')
----------------------------------------------
Thanks.
February 27, 2014 at 5:30 am
pwalter83 (2/27/2014)
What I wish to achieve is no rows should be displayed where DG_FLG = 'Y' and OOG_FLG = 'Y'.
Which wasn't what you said in your initial post.
What I need to achieve is if for one BOOKING_ID the FLAG is Y, then the data should not be displayed.
I can't read your mind and I can't intuit that the actual requirements don't match the request.
So, should no rows be displayed at all if there's any row with the OOG_FLG of Y, or just no rows for that booking ID? Does the same apply for DG_FLG? Do both of them have to be Y for a row to be excluded or just one? Is BookingID unique in the Booking table? It does not appear to be, but I can't tell if that's just something you left out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2014 at 6:07 am
GilaMonster (2/27/2014)
pwalter83 (2/27/2014)
What I wish to achieve is no rows should be displayed where DG_FLG = 'Y' and OOG_FLG = 'Y'.Which wasn't what you said in your initial post.
What I need to achieve is if for one BOOKING_ID the FLAG is Y, then the data should not be displayed.
I can't read your mind and I can't intuit that the actual requirements don't match the request.
So, should no rows be displayed at all if there's any row with the OOG_FLG of Y, or just no rows for that booking ID? Does the same apply for DG_FLG? Do both of them have to be Y for a row to be excluded or just one? Is BookingID unique in the Booking table? It does not appear to be, but I can't tell if that's just something you left out.
Sorry for not being clear enough. What I meant was- the where clause should be like
where DG_FLG <> 'Y'
and OOG_FLG <> 'Y'. However, if there are multiple rows for 1 booking_ID (103) and one is OOG_FLG = 'Y' and another is OOG_FLG = 'N', then both these rows should not be displayed for the particular booking_id(103). It should of course display other rows which match the criteria.
To further answer your questions:
1. should no rows be displayed at all if there's any row with the OOG_FLG of Y, or just no rows for that booking ID? just the rows for that particular booking_ID
2. Does the same apply for DG_FLG? - There will only be one value for DG_FLG for a unique booking_ID.
3. Do both of them have to be Y for a row to be excluded or just one? Yes, both of them have to be Y for the row to be excluded.
4. Is BookingID unique in the Booking table? Yes, the booking id is unique in the booking table, however, there could be multiple booking ids in the BOOKING_COMMOD table.
Thanks.
February 27, 2014 at 6:17 am
Ok, so the query fragment i gave earlier is correct, it was just short a clause.
SELECT Booking_ID FROM dbo.BOOKING AS b
WHERE DG_FLG != 'Y' AND
NOT EXISTS (SELECT 1 FROM dbo.BOOKING_COMMOD AS bc WHERE bc.Booking_ID = b.Booking_ID AND OOG_FLG = 'Y')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2014 at 6:24 am
GilaMonster (2/27/2014)
Ok, so the query fragment i gave earlier is correct, it was just short a clause.
SELECT Booking_ID FROM dbo.BOOKING AS bWHERE DG_FLG != 'Y' AND
NOT EXISTS (SELECT 1 FROM dbo.BOOKING_COMMOD AS bc WHERE bc.Booking_ID = b.Booking_ID AND OOG_FLG = 'Y')
Thanks a lot, Gail !! that was amazing. It worked perfectly !
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply