Question Coditional Inner Join

[Tom]

If the result from b.Col1 = 1 then I'm to use b.Col2 as the result. However if b.Col1 is any other value (including null) then I need to include two more Inner Joined tables. I'm trying not to break it up in to seperate blocks of queries if at all possible.

 Select
	a.Col1,
	a.Col2,
	b.Col1,
	b.Col2
From Table1 As a
	Inner Join Table2 As b
		On a.Col1 = b.Col1

/*
Conditional: If b.Col1 is anything besides 1

	Inner Join Table3 As c
		On b.Col1 = c.Col1

	Inner Join Table4 As d
		On c.Col1 = d.Col1
*/

 

[rcombs4]

Are you pulling data from Table3 and Table4, or do you only want the results from Table1 and Table2 that are also in Table3 and Table4?

If you are not returning the data from Table3 and Table4, you can add a subquery in the WHERE clause

SELECT A.Col1, A.Col2, B.Col1, B.Col2
FROM Table1 A INNER JOIN Table2 B
      ON A.Col1 = B.Col1
WHERE ((B.Col1 <> 1) AND A.Col1 IN (SELECT Col1 FROM Table3)) OR (B.Col1 = 1)

Basically its the same query you have but in the the WHERE clause have this logic

If B.Col1 is NOT 1 then only show the records where A.Col1 is in Table3
If B.Col1 is 1 then show all results




If you do need to pull data from Table3/4 then you might have to do a LEFT OUTER JOIN. Which will pull the data when B.Col1 is 1 and pull NULL values when B.Col1 is not 1. So adding the WHERE clause from above and i think you should have it.

 

[InertiaM]

Does this help?

Select
  a.Col1,
  a.Col2,
  [B]CASE WHEN b.Col1 = 1 THEN b.Col1 ELSE c.Col1 END AS ConditionalResult[/B],
  b.Col2
From
  (
    (
      Table1 As a LEFT JOIN Table2 As b On a.Col1 = b.Col1
    )
    LEFT JOIN Table3 As c On b.Col1 = c.Col1
  )
  LEFT JOIN Table4 As d On c.Col1 = d.Col1

Subqueries should be avoided whereever possible, especially when querying large quantities of data - the overheads are enormous.

 

[Tom]

The additional tables is more detailed filter to validate the record. I considered using Left Outer Joins but that than invalidates the filter considering I will get the parent record back (Table2) in either case and whether or not it matches to anything in Tables 3 & 4.

 

[InertiaM]

I'm confused (it isnt taking much after the day I've had )

if b.Col1 is any other value (including null)

Which fields from which tables do you want to return in this situation?

 

[Tom]

sorry it is a bit confusing. No matter what, the only answer I need is b.Col2

If b.Col1 = 1 I only need the first inner join, else I need to drill down further tables to validate it's child records exists too.

 

[rcombs4]

I know subqueries are not the best but I use them a good bit in reporting and don't have any problem with them.

Would something like this work:

SELECT B.Col2
FROM Table1 As a INNER JOIN Table2 as B
     ON A.Col1 = B.Col1
         LEFT OUTER JOIN Table3 as C
     ON C.Col1 = B.Col1
         LEFT OUTER JOIN Table4 as D
     ON D.Col1 = B.Col1
WHERE ((B.Col1 <> 1) AND C.Col1 IS NOT NULL AND D.Col1 IS NOT NULL) OR B.Col1 = 1

 

[Tom]

I'll give that a shot and agree I dont like subquieries but like even less the thought of breaking it up in seperate queries or functions....

 

[Tom]

Not working, I'm getting results back for tables A & B regardless whether or not the where in the outer joins. Basically what I want is that if b.Col1 = 1, it doesnt need to joing to tables C & D. But if b.Col1 is anything else, I must force the inner joins to ensure those connecting records exist.