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


  1.  Home
  2. MIDRANGE-L
  3. June 2013

Re: SQL Exists for Flag

Nice Charles!


On Thu, Jun 20, 2013 at 2:05 PM, Charles Wilt <charles.wilt@xxxxxxxxx>wrote:


http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Charles


On Thu, Jun 20, 2013 at 1:52 PM, Michael Ryan <michaelrtr@xxxxxxxxx>
wrote:

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.


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