tricky join with case 
Author Message
 tricky join with case
Hello

I have a problem with joining and using case in it.........

I have this table (sort of):

[Table1]

A=0
B=1
C=0

[TableA]
AInfo=blah

[TableB]
BInfo=blahb

[TableC]
CInfo=blabc

Depending on which one in Table1 that has the value 1 it should select
from that table and return the tables value and which table that it took
the info from.......

/Lasse

--
"Everything should be made as simple as
possible, but not simpler." -Albert Einstein



Fri, 08 Nov 2002 03:00:00 GMT
 tricky join with case

Lasse,

SELECT
     "Return value" =
        CASE
            WHEN Table1.A = 1 THEN SELECT TableA.AInfo FROM TableA
            WHEN Table1.B = 1 THEN SELECT TableB.BInfo FROM TableB
            WHEN Table1.C = 1 THEN SELECT TableC.CInfo FROM TableC
        END
     "From Table" =
        CASE
            WHEN Table1.A = 1 THEN "Table A"
            WHEN Table1.B = 1 THEN "Table B"
            WHEN Table1.C = 1 THEN "Table C"
        END

If this doesn't work why not try having on field on Table1 that holds either
A or B or C and use the above CASE statements.

DR

Quote:

>Hello

>I have a problem with joining and using case in it.........

>I have this table (sort of):

>[Table1]

>A=0
>B=1
>C=0

>[TableA]
>AInfo=blah

>[TableB]
>BInfo=blahb

>[TableC]
>CInfo=blabc

>Depending on which one in Table1 that has the value 1 it should select
>from that table and return the tables value and which table that it took
>the info from.......

>/Lasse

>--
>"Everything should be made as simple as
>possible, but not simpler." -Albert Einstein



Fri, 08 Nov 2002 03:00:00 GMT
 tricky join with case

Quote:
>> I have a problem with joining and using case in it... I have this

table (sort of): <<

In the future, please post reall DDL instead of some personal pseudo-
code.  You are asking people to program for you for free with
specifications worse than they use when they are being paid <g>.  Here
is my wild guess at what you meant to say:

CREATE TABLE Foobar
(a INTEGER NOT NULL CHECK (a IN (0,1)),
 b INTEGER NOT NULL CHECK (b IN (0,1)),
 c INTEGER NOT NULL CHECK (c IN (0,1)),
 CHECK (a + b + c = 1));

CREATE TABLE TableA (..);
CREATE TABLE TableB (..);
CREATE TABLE TableC (..);

Quote:
>> Depending on which one in Table1 that has the value 1 it should

select from that table and return the tables value and which table that
it took the info from... <<

This sounds like a really bad DB design, but ignoring that, your query
is :

 SELECT CASE
        WHEN a = 1 THEN (SELECT x FROM TableA)
        WHEN b = 1 THEN (SELECT y FROM TableB)
        WHEN c = 1 THEN (SELECT z FROM TableC) END AS stuff,
        CASE
        WHEN a = 1 THEN 'TableA'
        WHEN b = 1 THEN 'TableB'
        WHEN c = 1 THEN 'TableC') END AS source
   FROM Foobar;

Be sure to put on the constraints or this can blow up on you.

--CELKO--
Joe Celko, SQL and Database Consultant

Sent via Deja.com http://www.deja.com/
Before you buy.



Sat, 09 Nov 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Case Sensitive Join on non case-sensitive sql server

2. Case-sensitive Join on case-insensitive database

3. tricky CASE WHEN query...

4. tricky case problem

5. Outer join on three tables -tricky

6. need help on a tricky outer join

7. Help with CASE and Inner Joins

8. How do I do a join within a CASE statement

9. case stmt possible in join

10. can I use case statements at joins?

11. select case with right join

12. Bug in CASE statement with outer joins?


 
Powered by phpBB® Forum Software