Subquery - 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

Subquery

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2007-10-18 : 05:23:13

Hi, I've read through quite a few of the other new sql user questions and saw similar questions, but nothing that quite fits.

I use a transit database. The tables that I need to query for this question are Clients, Booking, Bookinglegs

I need to return results on trip information where the creation date
was the same day as the trip date (ldate) However, I also need to show trips taken by the clients whose trips made the above criteria, but also had other trips the same day.

This query produces the results I need for the trips created the same day as the trip day:

Select c.clientid, b.ldate, b.credate, PU.reqtime, DO.reqlate

From Clients C, Booking B, Bookinglegs PU, Bookinglegs DO

Where c.clientid=b.clientid
AND b.bookingid=PU.bookingid
AND PU.legnum=0
AND DO.legnum=1
AND b.credate=b.ldate

I tried writing a case expression so that the results of this query would return a value of 'SAME DAY' and then adding an identical union query to it, except with the AND b.credate<b.ldate with a case expression that returned a value of 'Prior Booking'...this worked, however, I get every single booking for that day from the second query. Is there any way to do a subquery or a different statement that would produce results that were only contained in the first statement? I tried outer joins as well, but I'm really at a loss...

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 05:27:34

Please provide proper and accurate sample data and your expected result.

E 12°55'05.25"
N 56°04'39.16"

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-10-18 : 08:16:52

Your post is somewhat confused but something like the following may help:

SELECT 
    C.clientid
    ,B.ldate
    ,B.credate
    ,PU.reqtime
    ,DO.reqlate
    ,CASE
        WHEN B.credate = B.ldate
        THEN 'Same Day'
        ELSE 'Prior Booking'
    END AS Booking
FROM Clients C
    JOIN (
            SELECT B1.clientid, B1.ldate
            FROM Bookings B1
            WHERE B1.credate = B1.ldate
            GROUP BY B1.clientid, B1.ldate
        ) D
        ON C.clientid = D.clientid
    JOIN Bookings B
        ON D.clientid = B.clientid
            AND D.ldate = B.ldate
    JOIN Bookinglegs PU
        ON B.bookingid = PU.bookingid
            AND PU.legnum = 0
    LEFT JOIN Bookinglegs DO
        ON B.bookingid = DO.bookingid
            AND DO.legnum = 1

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2007-10-19 : 01:30:00

Thanks for the input guys...Ifor, actually, the query you wrote worked out great with a few tweeks. Sorry I couldn't describe it better. The query produced results that showed client information on those clients whose booking was made on the same day as the trip day, but also included information for those same clients if they had other trips on the same day; not necessarily booked that day.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources