The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Complex (maybe?) SQL Query Help

This is bugging me, and I'm drawing a blank on how to solve it, so I'm hoping someone can offer a suggestion.

I have three tables; Psuedo-structure for them is as follows:

TABLE A, TABLE B (both have the same columns, but different data)
-------
ID
Description

TABLE C (Lookup)
--------
table_a_id
table_b_id

The business requirement is that wherever there's a match in table C for a column in table A, I need to grab the corresponding values from table B instead of the ones currently in A.

It doesn't need to be an update, I am trying to create a view that does it so I can keep the tables separate, since I will need the real value in Table A at a later date.

Maybe I Just had a brain fart, but I can't figure out how to do this.  It's more than a simple join, I know, because I have to basically check every row of Table A and see if there's a match in Table C; if there is then I need to pull the value from Table B based on the ID contained in Table C, otherwise I can use the value in Table A.

Any help would be appreciated.
SQL Junkie?
Wednesday, September 10, 2008
 
 
Your description is not eactly clear in some parts, but I think you are looking for something like:

SELECT COALESCE(a.Description, b.Description, 'UNKNOWN') AS Description
FROM C
LEFT JOIN A
  ON a.id = c.table_a_id
LEFT JOIN B
  ON b.id = c.table_a_id
Cade Roux Send private email
Wednesday, September 10, 2008
 
 
Sorry, that should be:

SELECT COALESCE(a.Description, b.Description, 'UNKNOWN') AS Description
FROM C
LEFT JOIN A
  ON a.id = c.table_a_id
LEFT JOIN B
  ON b.id = c.table_b_id
Cade Roux Send private email
Wednesday, September 10, 2008
 
 
And you can even short circuit successive searches by doing (not a lot here but in very complex queries, which have a lot of fallback "searches" and returning intermediate results to track which "search" is actually successful I have found this to be useful):

SELECT COALESCE(a.Description, b.Description, 'UNKNOWN') AS Description
FROM C
LEFT JOIN A
  ON a.id = c.table_a_id
LEFT JOIN B
  ON b.id = c.table_b_id
  AND a.id IS NULL
Cade Roux Send private email
Wednesday, September 10, 2008
 
 
Hmm.. I'll look at COALESCE, I think it might be what I'm looking for.  Thanks!
SQL Junkie?
Wednesday, September 10, 2008
 
 
Okay I was able to solve it.. or it looks that way.  Stupid me forgot that I need all of the info in table B anyways, and just a subset of table A (i.e. the ones not in C) so all I had to do was do something like:

SELECT * FROM A
WHERE A.ID NOT IN (SELECT table_a_id FROM C)
UNION
SELECT * FROM B
SQL Junkie?
Thursday, September 11, 2008
 
 
Your second solution looks incorrect to me.

The part after the union is including the wrong ids.  Unless you have a rule that the Aid and the Bid are always equal in table C.
Walter Mitty Send private email
Thursday, September 11, 2008
 
 
Maybe:

SELECT ID, Description FROM A
WHERE A.ID NOT IN (SELECT table_a_id FROM C)
UNION
SELECT Table_a_id as ID, Description
FROM C INNER JOIN B on B.ID = C.Table_B_id
Walter Mitty Send private email
Thursday, September 11, 2008
 
 
If the IDs are not the same you will need to know where a given row's ID came from.  So:

select 'A' as SourceTable,
a.ID,
a.Description
from A with(nolock)
where a.ID not in (select table_a_id from c with(nolock))
union all
select 'B',
b.ID,
b.Description
from B with(nolock)
JBrooks Send private email
Thursday, September 11, 2008
 
 
Jbrooks,

You and I are interpreting the original requirements differently. 

I understood the view as one that would relate the id in table A to the Description in table A unless there is a corresponding description in table B next to an id from table B that is different from the Id in table A, but where the table A id and the table B id are next to each other in table C.

Thwe view you offered  keeps the table A and table B id's separate,  but doesn't allow  a lookup knowing only the table A id. 
If the original requirments are NOT this,  then your solution might be the best.
Walter Mitty Send private email
Friday, September 12, 2008
 
 
@CadeRoux:

Didn't you mean rather:

SELECT COALESCE(b.Description, a.Description, 'UNKNOWN') AS Description
FROM C
LEFT JOIN A
  ON a.id = c.table_a_id
LEFT JOIN B
  ON b.id = c.table_b_id

That way if b.Description is not null, it is used. Else non-null a.Description is used or UNKNOWN.
Wojtek Send private email
Wednesday, September 17, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz