Re: SQL Exists for Flag -- MIDRANGE-L

I certainly was! I don't know why I can't get my head around this stuff
sometimes. Maybe it's a vitamin deficiency.

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

Woohoo! You made me almost do a happy dance!

----- Original Message -----
Wonderful...it works! Thanks Vern (and Charles). Seeing the straight select
with the left outer join helped me see it Vern. Yay!

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

wrote:

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.

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