CODEWITHSUNDEEP
sqloracle-database
user1529074
user1529074

Reputation: 23

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

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.

Upvotes: 2

Views: 593

Answers (3)

N West
N West

Reputation: 6819

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.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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.

Upvotes: 1

OraNob
OraNob

Reputation: 694

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(+)

Upvotes: -2

Related Questions