CODEWITHSUNDEEP
sqlsql-servert-sql
Art F
Art F

Reputation: 4200

SQL query join conditions

I have a query (exert from a stored procedure) that looks something like this:

SELECT S.name 
INTO #TempA
from tbl_Student S
INNER JOIN tbl_StudentHSHistory TSHSH on TSHSH.STUD_PK=S.STUD_PK
INNER JOIN tbl_CODETAILS C
on C.CODE_DETL_PK=S.GID
WHERE TSHSH.Begin_date < @BegDate

Here is the issue, the 2nd inner join and corresponding where statement should only happen if only a certain variable (@UseArchive) is true, I don't want it to happen if it is false. Also, in TSHSH certain rows might have no corresponding entries in S. I tried splitting it into 2 separate queries based on @UseArchive but studio refuses to compile that because of the INTO #TempA statement saying that there is already an object named #TempA in the database. Can anyone tell me of a way to fix the query or a way to split the queries with the INTO #TempA statement?

Upvotes: 1

Views: 103

Answers (4)

paparazzo
paparazzo

Reputation: 45096

SELECT S.name 
INTO #TempA 
from tbl_Student S 
INNER JOIN tbl_StudentHSHistory TSHSH 
   on TSHSH.STUD_PK = S.STUD_PK
INNER JOIN tbl_CODETAILS C
   on C.CODE_DETL_PK = S.GID 
  and @UseArchive = true 
WHERE TSHSH.Begin_date < @BegDate

But putting @UseArchive = true in the join in this case is the same as where
Your question does not make much sense to me
So what if TSHSH certain rows might have no corresponding entries in S?

If you want just one of the joins to match 

SELECT S.name 
INTO #TempA 
from tbl_Student S 
LEFT OUTER JOIN tbl_StudentHSHistory TSHSH 
   on TSHSH.STUD_PK = S.STUD_PK
LEFT OUTER JJOIN tbl_CODETAILS C
   on C.CODE_DETL_PK = S.GID 
  and @UseArchive = true 
WHERE TSHSH.Begin_date < @BegDate 
  and ( TSHSH.STUD_PK is not null or C.CODE_DETL_PK id not null )

Upvotes: 0

Semih Yagcioglu
Semih Yagcioglu

Reputation: 4101

You can split the queries and then insert into a temp table easily.

SELECT * INTO #TempA FROM
(
    SELECT * FROM Q1

    UNION ALL

    SELECT * FROM Q2
) T

Upvotes: 0

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Looks like you're asking 2 questions here.

1- How to fix the SELECT INTO issue:

SELECT INTO only works if the target table does not exist. You need to use INSERT INTO...SELECT if the table already exists.

2- Conditional JOIN:

You'll need to do a LEFT JOIN if the corresponding row may not exist. Try this.

SELECT S.name 
FROM tbl_Student S
INNER JOIN tbl_StudentHSHistory TSHSH 
    ON TSHSH.STUD_PK=S.STUD_PK
LEFT JOIN tbl_CODETAILS C 
    ON C.CODE_DETL_PK=S.GID
WHERE TSHSH.Begin_date < @BegDate
    AND CASE WHEN @UseArchive = 1 THEN c.CODE_DETL_PK ELSE 0 END =
        CASE WHEN @UseArchive = 1 THEN S.GID ELSE 0 END

Putting the CASE statement in the WHERE clause and not the JOIN clause will force it to act like an INNER JOIN when @UseArchive and a LEFT JOIN when not.

Upvotes: 2

roman m
roman m

Reputation: 26521

I'd replace it with LEFT JOIN

LEFT JOIN tbl_CODETAILS C ON @UseArchive = 1 AND C.CODE_DETL_PK=S.GID

Upvotes: 0

Related Questions