How to use multi-column sql query result as input for new query

Question

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?

"use multi-column sql query result as input for new query" sounds like a temp table or a CTE — Mark Schultheiss
– Mark Schultheiss, Commented Apr 26, 2021 at 14:31
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
– Stu, Commented Apr 26, 2021 at 14:34
@Stu, it's the result of a left join. As you might have guessed, SQL is not daily work for me. — RolfBly
– RolfBly, Commented Apr 26, 2021 at 16:12

Andy D · Accepted Answer · 2021-04-26 14:35:39Z

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

answered Apr 26, 2021 at 14:35

Andy D

385 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RolfBly Over a year ago

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.

WAMLeslie · Accepted Answer · 2021-04-26 14:29:27Z

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

answered Apr 26, 2021 at 14:29

WAMLeslie

1,2711 gold badge7 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to use multi-column sql query result as input for new query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related