How to do a "selective where" in Oracle 10g?

Question

I have a parent table A that have 2 foreign keys (from tables B and C), but it can have just one foreign key at a time. Example:

SELECT a.evi, a.time, a.date, 
       a.number, a.descr, a.x, 
       a.y, a.z,  a.FK_tableB, a.FK_tableC, 
       b.brand, b.type, 
       b.color, b.model, c.name, c.lastname, 
  FROM tableA a, 
       tableB b, 
       tableC c  
 WHERE (PK_tableA = 100 AND PK_tableB = FK_tableB)      
    OR (PK_tableA = 100 AND PK_tableC = FK_tableC)

(This is not working, obviusly)

How do I return data when just one of the where clauses is true.

When there are duplicates, how do you determine which is the one you want in the result? — OMG Ponies, Jul 16 '12 at 14:06
[Please, please, PLEASE stop using antiquated JOIN syntax.](http://stackoverflow.com/questions/11251751/which-join-syntax-is-better) — N West, Jul 16 '12 at 14:07

score 4 · Accepted Answer · answered Jul 16 '12 at 14:19

4

It appears you want to do a "Exclusive OR" (XOR) on your query.

Since SQL doesn't have XOR, you could try something like:

create table a
( a_id int, b_id int, c_id int);

create table b
( b_id int);

create table c
( c_id int);

insert into a (a_id, b_id, c_id) values (1, 1, 1);
insert into a (a_id, b_id, c_id) values (2, NULL, 2);
insert into a (a_id, b_id, c_id) values (3, 2, NULL);
insert into a (a_id, b_id, c_id) values (4, NULL, NULL);

insert into b (b_id) values (1);
insert into b (b_id) values (2);

insert into c (c_id) values (1);
insert into c (c_id) values (2);

SELECT a.a_id, a.b_id, a.c_id, b.b_id, c.c_id
  FROM a 
  LEFT JOIN b
    ON (a.b_id = b.b_id)
  LEFT JOIN c  
    ON (a.c_id = c.c_id)
 WHERE (   (b.b_id is NOT NULL AND c.c_id is NULL) 
        OR (c.c_id is NOT NULL AND b.b_id is NULL));

See this SQLFiddle to try it out.

answered Jul 16 '12 at 14:19

N West

6,768
25
40

this is so far the only solution matching the question – Sebas Jul 16 '12 at 14:20
1

Note that I don't condone this table design - it's a bit confusing. If you truly can have one key but not the other, then you're probably need to revisit your design of two keys in the table. – N West Jul 16 '12 at 14:24
Thanks this is pretty much what I needed. Sorry for the lame example, I was in a developer's block, every comment was really useful. And thanks for your time. You guys Rock =) – user1529074 Jul 16 '12 at 14:53

score 1 · Answer 2 · answered Jul 16 '12 at 14:17

Use want to use left outer joins in order to keep all rows in table A, even if there are no matching rows in the other tables.

SELECT a.evi, a.time, a.date, a.number, a.descr, a.x, a.y, a.z,  a.FK_tableB,
       a.FK_tableC, b.brand, b.type,  b.color, b.model, c.name, c.lastname
FROM tableA a left outer join
     tableB b
     on a.FK_TableB = b.PK_tableB left outer join
     tableC c
     on a.FK_tableC = c.pk_TableB
where PK_tableA = 100

Also, you need to use proper join syntax in your queries. And, using aliases int he SELECT clause is good, but you should also use them in the ON and WHERE clauses.

score -2 · Answer 3 · answered Jul 16 '12 at 14:17

-2

Try specifying an outter join on tables B and C Two outter joins.... I think it will work

SELECT a.evi, a.time, a.date, 
       a.number, a.descr, a.x, 
       a.y, a.z,  a.FK_tableB, a.FK_tableC, 
       b.brand, b.type, 
       b.color, b.model, c.name, c.lastname, 
FROM tableA a, 
     tableB b, 
     tableC c  
WHERE PK_tableA = 100
AND a.PK_tableB = b.FK_tableB(+)
AND a.PK_tableB = c.FK_tableC(+)

answered Jul 16 '12 at 14:17

OraNob

684
3
9

2

From the [Oracle Documentation on Join](http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm) *Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax...* – Conrad Frix Jul 16 '12 at 14:25

How to do a "selective where" in Oracle 10g?

3 Answers3