Query to lookup value in another table, if it exists

[rcolon]

Help!

We can assign resource descriptions to both generic customer numbers (customer number zero) or a specific customer number (customer number 12345) which reside in a table.

For example, resource "ABC" might be described as "paint" for a generic customer (customer zero). But maybe customer number 12345 might want to call resource "ABC" "flat paint".

I have 2 tables, A and B. Table A contains Customer number (which is never zero) and resource number. Table B contain Customer number (which can be zero for a generic customer), resource number, and resource description.

What I would like to do is look up the customer number from table A in table B. If it exists, give me the resource description from table B for that customer and resource number. If the customer number doesn't exist in table B (Is null), give me customer zero's resource description and resource number.

Can this be done in a query???

Right now I prompt for the customer number in the query to get the description by checking table B to see if the customer number exists in the table, and if it does, enter the customer number, if not, I enter "0".

Any help/suggestions/directions would be greatly appreciated!

Bob

 

[r937]

try this --

select A.custno, A.resourceno
     , B.resourcedescr
  from tableA A
inner 
  join tableB B
    on A.custno = B.custno
   and A.resourceno = B.resourceno
union all
select A.custno, A.resourceno
     , B.resourcedescr
  from tableA A
inner 
  join tableB B
    on A.resourceno = B.resourceno
   and                B.custno=0
 where not exists
   ( select 1 
       from tableB
      where custno = A.custno
        and resourceno = A.resourceno )

caution: not tested

rudy

http://rudy.ca/

 

[rcolon]

Rudy,

Thanks for getting back with info so soon!

I am deciphering your query, and can you elaborate on it a little.

I see the first part as one query which would find the match, and the second part as another query that would use custno=0 if there is no match.

Barring any typos in the select statement (Should there be a number 1?) can you look/go over the logic for me?

Thanks,

Bob