CODEWITHSUNDEEP
sql-server
Crazy Cucumber
Crazy Cucumber

Reputation: 479

replace nested where condition with join

I have a SQL query that looks like this

Select a.* 
From table1 a
where a.ColumnName in
    (Select MAX(b.ColumnName)
    from table2 b
    where b.ColumnName2 in
        (
        Select MAX(c.columnName)
        from table3 c
        Group by c.ColumnName2
        )
    Group by b.ColumnName2
    )

I am trying to write this in a join statement. I am positive inner join is what I need to get the right information. If someone could translate this to a join statement, I would be really glad.

Thank you.

EDIT 1: I tried the typical Join statement that a rookie would.

Select a.* 
from table1 a
inner join table2 b
on a.columnname = (Select max(b.columnName) from table2)
inner join table3 c
on b.columnName = (select max(c.columnName) from table3)

Obviously, that didn't work because I get 100,000+ results when I should be getting 800. I tried using an alias for table2 and table3 INSIDE the subselect statements and selecting the columnname using THAT alias like this:

Select max(bPart.columnName from table2 bPart)
Select max(cPart.columnName from table3 cPart)

Still the same result.

Upvotes: 1

Views: 57

Answers (1)

xQbert
xQbert

Reputation: 35333

PERHAPS....

Though I'm not sure why a join is needed. Performance wise exists would likely be fastest, and since you're not returning values from table2 or 3 it seems like it would be the best approach.

SELECT a.* 
FROM table1 a
INNER JOIN (SELECT MAX(ColumnName) MColumnName, columnname2
            FROM table2
            GROUP BY columnName2) B
  ON A.columnName = B.mColumnName
INNER JOIN (SELECT MAX(columnName) mColumnName
            FROM table3
            GROUP BY ColumnName2) C
  ON B.columname2 = C.MColumnName

Upvotes: 1

Related Questions