0

An alternative title would be "how to join two external tables with two different values, by row, from query result". I'm open to suggestions/edits.

I have this query result (2 of 6000+ lines shown)

objectpriref    packpriref  locpriref
------------    ----------  ----------
30889           229         16672
30990           267         16697

and 2 tables like this

objname                 location
id        name          id        name
---------.--------      ---------.----          
30889     MACQ_001      16672     A16
30890     BLAH_002      16673     A17
30990     FOOH_009      16697     B300

The desired result should look something like this

objectpriref    objname     locname
------------    ----------  ----------
30889           MACQ_001    A16
30990           FOOH_009    B300

If this can be done within SQL, what would be the best approach? What I've tried so far:

  • Put the query result into a temp table using INTO #mtt (for MyTempTable) from here and then trying to address the various columns as #mtt.objectpriref etc. This gets me invalid object name #mtt. This might deserve a separate question.
  • Put the query inside another select, but that runs into this, using IN works on single columns only.

I may be using the wrong keywords to google for. Any suggestions?

RolfBly
  • 3,612
  • 5
  • 32
  • 46
  • "use multi-column sql query result as input for new query" sounds like a temp table or a CTE – Mark Schultheiss Apr 26 '21 at 14:31
  • 1
    Many ways to join get intermediate results for reuse using a cte, table variable, temp table, even an apply operator. Your sample data appears to just be a simple inner join between the tables so clearly is not representative of your issue? – Stu Apr 26 '21 at 14:34
  • Just a straight join or left join should do it? – Charlieface Apr 26 '21 at 14:53
  • @Stu, it's the result of a left join. As you might have guessed, SQL is not daily work for me. – RolfBly Apr 26 '21 at 16:12

2 Answers2

1

Try this with your table names.

select t1.objectpreiref, t2.name, t3.name
from table1 t1
left join table2 t2 on t1.objectpriref = t2.objnameid
left join table3 t3 on t1.locpriref = t3.locationid
WAMLeslie
  • 1,241
  • 1
  • 5
  • 14
1

Something like

select
        T1.objectpriref,
        T2.name as objname,
        T3.name as locname
from
        table1 T1
        inner join table2 T2
                on T2.objnameid = T1.objectpriref
        inner join table3 T3
                on T3.locationid = T1.locpriref
Andy D
  • 38
  • 5
  • Thanks! The first join should be a left join, but otherwise I learned a few things here. I like the coding style as it helps to clarify the assignment, and I didn't know you can stack joins. – RolfBly Apr 26 '21 at 20:46