-2

I am fairly new to SQL and have been able to run queries from a single table in a single DB. But now I need to access the codes and their true values from another table in another DB and I'm not sure how to do that. It probably involves the JOIN command but I haven't been able to make it work.

Here is a summary of my data:

DB1.Table1

Procedure Code  Procedure Description   PIC Code ID
----------------------------------------------------
10005001        INJECTION 20ML          707
10005002        INJECTION 30ML          707
20008850        BLOOD CBC               254

DB2.Table2

PIC Code ID PIC Code    PIC Code Description
---------------------------------------------
707         250         PHARMACY
707         250         PHARMACY
254         300         LABORATORY

My query starts in DB1.Table1 but I am to be able to link to DB2.Table2 and return the value in column PIC Code instead of PIC Code ID.

So the output would be similar to:

Procedure Code  Procedure Description   PIC Code
-------------------------------------------------
10005001        INJECTION 20ML          250
10005002        INJECTION 30ML          250
20008850        BLOOD CBC               300

The PIC Code ID is an index that is used at run time and not the true code that the end user would recognize.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
stratplaya
  • 1
  • 1
    There are plenty of questions like this on S.O. and plenty of answers with examples. I suggest you take a look at https://stackoverflow.com/a/21085546/3950497. And next time you should provide an example of what you have tried so far, so people can point you in the right direction. – ingkevin Oct 15 '20 at 21:52

3 Answers3

0

You seem to want a simple join:

select t1.procedure_code, t1.procedure_description, t2.pic_code
from table1 t1
inner join table2 t2 
    on t2.pic_code_id = t1.pic_code_id

Your data is showing duplicates in table2, but I am unsure that's what you really have, because your question does not mention that - so the query does not handles that. But if you really need to:

select t1.procedure_code, t1.procedure_description, t2.pic_code
from table1 t1
inner join (select distinct pic_code_id, pic_code from table2) t2 
    on t2.pic_code_id = t1.pic_code_id
GMB
  • 216,147
  • 25
  • 84
  • 135
  • the data in OP's question has duplicates and you are admittedly posting an answer that does not work? – Bryan Dellinger Oct 15 '20 at 22:24
  • @BryanDellinger: I don't see your point. The answer explains the rationale for the first query, and addresses the duplicates in the second query. – GMB Oct 15 '20 at 22:26
0

First you should remove duplicates from table2, then you can join result with table1.

select
     t1.Procedure_Code
    ,t1.Procedure
    ,t1.Description
    ,t2.PIC_Code
from DB1.Table1 t1
left join (
    select
        PIC_Code_ID
        ,max(PIC_Code) as PIC_Code
    from DB2.Table2
    group by
        PIC_Code_ID
    ) t2 on t1.PIC_Code_ID = t2.PIC_Code_ID
BezginYuriy
  • 29
  • 3
0

how about a correlated subquery.

select Procedure_Code, Procedure_Description,
(select Max(PIC_Code)
 from Table2 b
 where a.PIC_Code_ID = b.PIC_Code_ID
) as PIC_Code
from Table1 a

run fiddle here https://www.db-fiddle.com/f/sUAjQdMVyyhqwF6okdaiVQ/0

Bryan Dellinger
  • 4,724
  • 7
  • 33
  • 79