× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.