| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> SQL Query Help, please?
I have two tables (call them Table_A and Table_B for originality :-)
Table_A is the parent, and Table_B the child. Table_B has a foreign
key column ProtId referencing ProtId in Table_A.
There will be a maximum of DictTableCode can take on one of four values, 1000, 1010, 1020 and 1030. There will *always* be an entry in Table_B for DictTableCode = 1000. There may also be one or more entries with DictTableCode = 1010, 1020, 1030.
What I need is a query that will retrieve * from Table_A and if DictTableCode is 1010, will retrieve ProtParmAlphaValue from Table_B, but if DictTableCode is anything else, will retrieve NULL from Table_B.
I only ever want one row for each value of ProtId retrieved from Table_A, so if there are two records in Table_B with the same Prot_Id, but DictTableCodes of 1010 and 1020, say, I would want to retrieve just the single row from Table_A joined with ProtParmAlphaValue from table_B. If there are two records in Table_B with the same Prot_Id, but DictTableCodes of 1000 and 1020, say, I would want to retrieve just the single row from Table_A joined with NULL.
Thus
Table_A
A 1 X 3 OK B 2 Y 4 OK C 3 Z 5 OK
Table_B
A 1000 Parm1 A 1010 Parm2 A 1030 Parm3 B 1000 Parm4 B 1030 Parm4 C 1000 Parm5 C 1010 Parm6
Should retrieve
ProtId ObjdId AlphaValue NumericValue Status ProtParmAlphaValue
====== ====== ========== ============ ====== ================== A 1 X 3 OK Parm2 B 2 Y 4 OK NULL C 3 Z 5 OK Parm6
The first row is has Parm2 since this matches DictTableCode = 1010, the second row has NULL, since there is no DictTableCode = 1010, and the third row has Parm6 since DictTableCode = 1010. Only one record is retrieved for each value in Table_A.
Any help? Received on Fri Oct 23 1998 - 04:01:26 CDT
 |
 |