Re: SQL Exists for Flag

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Michael

Let's see if I can make sense of this - a LEFT OUTER JOIN gives you all records from the first table, plus any matching records from the second table.

So the JOIN accomplishes the EXISTS condition. If FLD1 exists in TableB, there will be values there.

When there is not a matching record - FLD1 is not in TableB, then the column TAbleB/Fld1 will be NULL. So the CASE tests for that and sets either 'X' or a blank.

You can get a better feel for it, perhaps, if you run this -

select tablea.fld1, tablea.fld2, tableb.fld1 from tablea left outer join tableb on tablea.fld1 = tableb.fld1

If I'm guessing right, you should see some hyphens in the 3rd column - these are the NULLs for unmatched records.

Vern

----- Original Message -----
Hey Vern -

But the condition isn't that TableB.Fld1 is null, it's that the key value
TableA.Fld1 is found in TableB.


On Thu, Jun 20, 2013 at 1:01 PM, Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>wrote:

> Hi Michael
>
> That's what this should do - a LEFT OUTER JOIN will give you all records
> from TableA and TableB, if there is a match - NULLs, otherwise.
>
> Give it a try. You might like it almost as much as 7Up!
>
> Vern
>
> ----- Original Message -----
> Thanks guys...I didn't explain myself well. I want to display the values
> from TableA, and an 'X' if the key in TableA is found in TableB.
>
>
> On Thu, Jun 20, 2013 at 12:27 PM, Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx
> > wrote:
>
> > Left outer JOIN, I think.
> >
> > select tablea.fld1, tablea.fld2, case when TableB.fld1 is null then ' '
> > else 'X' end from tablea join tableb on tablea.fld1 = tableb.fld1
> >
> > Check the syntax of the CASE - I'm doing this from memory.
> >
> > HTHAW (hope this helps and works)
> > Vern
> >
> > ----- Original Message -----
> > Here's what I want to do...TableA has 4 columns. TableB shares a key
> value
> > with TableA. I want to produce this output in a select statement (to be
> > used from PHP fwiw):
> >
> > TableA.Fld1 TableA.Fld2 'X' (if TableA.Fld1 is in TableB).
> >
> > I've been trying combinations of a CTE, CASE, and exists, but I can't get
> > this to work. I'd like:
> >
> > A1 A2
> > A3 A4 X
> > A5 A6 X
> > A7 A8
> >
> > IOW, I want the values for TableA to show up, and an X if the
> corresponding
> > key value is in TableB. This has to be easier than I'm making it out to
> be.
> > Thanks!
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.