Nested Query - SQL Server Forums

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

Nested Query

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-11-29 : 17:43:10

Tables: Booking
Fields: BookingID, Schedstatus, Clientid
(Fields in From & Where statement: Moddate, Ldate are both in Booking Table)

Sample Data:

Clientid***Bookingid***SchedStatus
1 ***100 ***40
1 ***101 ***40
2 ***102 ***1
2 ***103 ***40
3 ***104 ***20
3 ***105 ***1

SELECT Distinct(bookingid), Clientid, Schedstatus

FROM Booking B
JOIN (Select clientid
from Booking
where ldate=20081128
AND schedstatus not in (0,1,2,3)
AND ModDate=Ldate) BB
ON B.Clientid=BB.Clientid

WHERE lDate=20081128

--------------------------------------------------------------------
When I run the above query, I return results:

BookingID***CliendId***SchedStatus
100 ***1 ***40
101 ***1 ***40
102 ***2 ***1
103 ***2 ***40
104 ***3 ***20
105 ***3 ***1

I would like to see results where a client (clientid) has at least one booking
(bookingid) with a schedstatus that is not in 0,1,2,3 but also has bookings (bookingid)
that have a schedstatus IN 0,1,2,3.

The Schedstatus is how we determine if a trip has been cancelled. So if it is IN 0,1,2,3,
then it is scheduled. If it is not IN 0,1,2,3, then it has been cancelled.

EX: Booking agent received a cancellation notice for a round trip. I don't need to see all
the cancellations, but I do need to see any cancellation where there is also a scheduled
trip for that clientid.

The above query returns results that are superfluous because they only include clients who have
schedstatus NOT IN 0,1,2,3.

This query gives me almost what I want, but I'd like to go one step further and filter the superfluous
information out so that I just see the following:

BookingID***CliendId***SchedStatus
102 ***2 ***1
103 ***2 ***40
104 ***3 ***20
105 ***3 ***1

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-29 : 22:55:34

This could be completely wrong because your description is all mixed up (you said you want clients with bookingid not (0, 1, 2, 3) but you tested schedstatus in your query, and the output columns in your query don't match the example output and you gave output but not input, and I don't know what [Today,Date] is).

SELECT ClientID, BookingID, SchedStatus
FROM Booking
WHERE lDate = [Today,Date]
    AND SchedStatus NOT IN (0,1,2,3)
    AND ModDate = Ldate
    AND EXISTS (SELECT * FROM Booking FilterBooking
        WHERE SchedStatus IN (0,1,2,3) 
            AND FilterBooking.ClientID = Booking.ClientID)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-11-29 : 23:30:34

Hi Sn...thanks for the post...sorry if it wasn't clear. I'll try again:

Tables: Booking
Fields: BookingID, Schedstatus, Clientid
(Fields in From & Where statement: Moddate, Ldate are both in Booking Table)

Sample Data:

Clientid***Bookingid***SchedStatus
1 ***100 ***40
1 ***101 ***40
2 ***102 ***1
2 ***103 ***40
3 ***104 ***20
3 ***105 ***1

SELECT Distinct(bookingid), Clientid, Schedstatus

FROM Booking B
JOIN (Select clientid
from Booking
where ldate=20081128
AND schedstatus not in (0,1,2,3)
AND ModDate=Ldate) BB
ON B.Clientid=BB.Clientid

WHERE lDate=20081128

--------------------------------------------------------------------
When I run the above query, I return results:

BookingID***CliendId***SchedStatus
100 ***1 ***40
101 ***1 ***40
102 ***2 ***1
103 ***2 ***40
104 ***3 ***20
105 ***3 ***1

I would like to see results where a client (clientid) has at least one booking
(bookingid) with a schedstatus that is not in 0,1,2,3 but also has bookings (bookingid)
that have a schedstatus IN 0,1,2,3.

The Schedstatus is how we determine if a trip has been cancelled. So if it is IN 0,1,2,3,
then it is scheduled. If it is not IN 0,1,2,3, then it has been cancelled.

EX: Booking agent received a cancellation notice for a round trip. I don't need to see all
the cancellations, but I do need to see any cancellation where there is also a scheduled
trip for that clientid. This is because the trip that is

The above query returns results that are superfluous because they only include clients who have
schedstatus NOT IN 0,1,2,3.

This query gives me almost what I want, but I'd like to go one step further and filter the superfluous
information out so that I just see the following:

BookingID***CliendId***SchedStatus
102 ***2 ***1
103 ***2 ***40
104 ***3 ***20
105 ***3 ***1

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-29 : 23:36:48

So I think I was close before, probably just need to remove the SchedStatus check from the main query

SELECT BookingID, ClientID, SchedStatus
FROM Booking
WHERE lDate = 20081128
    AND ModDate = Ldate
    AND EXISTS (SELECT * FROM Booking FilterBooking
        WHERE SchedStatus IN (0,1,2,3) 
            AND FilterBooking.ClientID = Booking.ClientID)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-11-29 : 23:52:54

Hi Sn...thanks for the try...that's returning the same results as my original query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 01:17:43

I think what you want is this


SELECT t.Clientid,t.Bookingid,t.SchedStatus
FROM YourTable t
INNER JOIN (SELECT Clientid
            FROM YourTable 
            GROUP BY Clientid
            HAVING SUM(CASE WHEN chedstatus not in (0,1,2,3) THEN 1 ELSE 0 END)>0
            AND SUM(CASE WHEN chedstatus in (0,1,2,3) THEN 1 ELSE 0 END)>0)tmp
ON tmp.Clientid=t.Clientid

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-11-30 : 01:27:06

Vis, you're the man! That's perfect...This will help alot! Craig

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 01:30:03

You're welcome Craig

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-30 : 01:34:00

Yeah, I missed the part that the client had to have a 0, 1, 2, 3 and also a not 0, 1, 2, 3. This should do it using the approach I used, and I suspect this will perform better with lots of data:

SELECT BookingID, ClientID, SchedStatus
FROM Booking
WHERE lDate = 20081128
    AND ModDate = Ldate
    AND EXISTS (SELECT * FROM Booking FilterBooking
        WHERE SchedStatus IN (0,1,2,3) 
            AND FilterBooking.ClientID = Booking.ClientID)
    AND EXISTS (SELECT * FROM Booking FilterBooking
        WHERE SchedStatus NOT IN (0,1,2,3) 
            AND FilterBooking.ClientID = Booking.ClientID)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-11-30 : 01:56:04

Hi Sn, that's still not working...I'll play with it though...thanks again!
Craig

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources