Post reply

How to return rows based on a condition

  • January 20, 2011 at 6:12 am

    #243986

    Hi,

    I got stuck on the following:

    I have Table1 and Table2

    Table1

    PolicyNum____PreviousPolicyNum

    7777________88888

    7777________6666

    Table2

    PolicyNum____DateSold

    88888_______2010-01-01

    In this example I want to do a join between these 2 tables but only return:

    PolicyNum____PreviousPolicyNum____PolicyNum____DateSold

    7777________88888______________88888_______2010-01-01

    because the 2nd row in Table1 does not match anything in Table2 On Table1.PreviousPolicyNum = Table2.PolicyNum.

    However, in this example:

    Table1

    PolicyNum____PreviousPolicyNum

    7777________88888

    7777________6666

    Table2

    PolicyNum____DateSold

    99999_______2010-01-01

    I want to return just the 1st record in Table1 because there is no match at all on T1.PreviousPolicyNum = T2.PolicyNum

    It's almost like I need an INNER JOIN in the first example, but a LEFT OUTER JOIN in the 2nd example.

    How can I achieve the above results in just a single query?

    Thanks.

  • January 20, 2011 at 7:12 am

    #1275903

    In your second example, why is only the first row to be returned?

    According to your logic, the second row should also be returned, since there is also no match at all for the 6666 policynum.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 20, 2011 at 1:43 pm

    #1276239

    CELKO (1/20/2011)

    Since tables have rows not records and rows have no ordering, what does "first record" mean?

    If you really want to go "old school" in relational theory, you should call the table a "relation".

    (cfr. CJ Date: An Introduction to Database Systems, 8th Edition. (p64))

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 20, 2011 at 4:05 pm

    #1276300

    Hi guys,

    Sorry for the confusion,

    In my 2nd example, because the PolicyNumber is the same (7777) for both records, and neither of the 2 records have a match in Table2, then the business rules say that I should return any (single) record from Table 1. The business rules do not care which record I return, but from an SQL perspective, I can just select Top 1 record and ordered by PreviousPolicyNum.

    But I'm not sure how to formulate a query that caters for both the first example and the 2nd example.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply