Re: Combine results based on a value
Date: Fri, 22 Feb 2008 14:02:42 -0800 (PST)
Message-ID: <69d9ebb4-46c2-45c9-94f4-e0fd53e045fd@60g2000hsy.googlegroups.com>
On Feb 22, 4:38 pm, "aravind.ka..._at_gmail.com"
<aravind.ka..._at_gmail.com> wrote:
> I need to join two tables where when the join condition is true, it
> should return YES and returns NO if it doesnt. Let me try to give an
> example:
>
> I have BOOK_RENTALS which contains customer names and books:
>
> JOHN SMITH, BOOKA
> JOHN SMITH, BOOKB
> JOHN SMITH, BOOKC
>
> and I have a Book Master:
>
> BOOKA
> BOOKB
> BOOKC
> BOOKD
> BOOKE
>
> I would like to combine the results from these two tables such that I
> get results as follows:
>
> JOHN SMITH, BOOKA, YES
> JOHN SMITH, BOOKB, YES
> JOHN SMITH, BOOKC, YES
> JOHN SMITH, BOOKD, NO
> JOHN SMITH, BOOKE, NO
>
> Is this possible in a single sql statement?
>
> Any help is appreciated.
You might try doing this with an outer join (left outer join), and then use the DECODE or NVL2 function on the BOOK_RENTALS.BOOK column to determine what to return when that column is NULL.
Left outer join syntax:
WHERE
TABLE1.COLUMN1 = TABLE2.COLUMN1(+)
There should be plenty of examples in this group and through a Google
search to help with the DECODE and/or NVL2 syntax.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Feb 22 2008 - 16:02:42 CST