SQL Query Select from 1 table and return data based on 2 columns

Question

I'm working on a SQL query where I have data stored in table r. Based on the columns of give or receive, I need to store the data in a temp table to then store in a new table.

Essentially, the data is pulling from 3 tables and the end goal is to get the binary code for an image and store to display in .net

I'm trying to figure out the multi select at this time

So give or receive in r equals Username in S, display all the data that pertains and get the image if employee equals employee

I've tried a good amount of code and I feel like an OR would do the trick but it doesn't seem to.

Thanks for any help you may provide.

SELECT  
    r.id, r.give, r.[receive], r.[type], r.[description], r.isApproved, 
    r.photoGive, r.photoReceive 
INTO
    #temp2 
FROM 
    Intranet.dbo.Recgonize r

SELECT s.Employee, s.Username 
INTO #temp3 
FROM Vision7.dbo.SEUser s

SELECT p.Photo, p.Employee 
INTO #temp4 
FROM Vision7.dbo.EMPhoto p

SELECT * 
FROM #temp2 AS a
INNER JOIN #temp3 b ON a.give = b.Username 
INNER JOIN #temp4 c ON b.Employee = c.Employee

DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp4

Answer 1

Why are you using temporary tables for this? These just make code harder to debug and maintain, more expensive to run, and more complicated to understand.

JOINs will work without temporary tables. But you do have to additional logic to get the give and receive values in separate columns, so more JOINs are necessary:

SELECT r.id, r.give, r.[receive], r.[type], r.[description], 
       r.isApproved, r.photoGive, r.photoReceive,
       pg.Photo as give_photo, pg.Employee as give_employee,
       pr.Photo as receive_photo, pr.Employee as receive_employee
FROM Intranet.dbo.Recognize r LEFT JOIN
     Vision7.dbo.SEUser sg
     ON r.give = sg.Username LEFT JOIN
     Vision7.dbo.SEUser sr
     ON r.receive = sr.Username LEFT JOIN
     Vision7.dbo.EMPhoto pg
     ON sg.Employee = pg.Employee LEFT JOIN
     Vision7.dbo.EMPhoto pr
     ON sr.Employee = pr.Employee