MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » June 2013

Re: SQL Exists for Flag



fixed

I've got a question... what if table b has the same value as table a twice?
With a left outer join you are going to get table A record twice. So you
would need to add a distinct or use a subselect.

Select a.fld1, a.fld2, (select 'X' from tableb b where (a.fld1, a.fld2) =
(b.fld1, b.fld2) fetch first row only)
from tablea a



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.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact