Geek Answers Handbook

Attaching internally to a select statement, where is the internal select statement, where is the sentence referencing an external selection?

This is a tricky request for my big problem, but the bottom line is that I'm trying to do an internal join with a choice, where the choice is limited by the outer choice. Is it possible? I get an error with the identifier S.Item and S.SerialNum for internal selection.

The bottom line is that we need to group the / serial element, and the request is large enough, we do not want to go back and group everything in the entire request for this minor union.

SELECT S.Item, S.SerialNum, S.ReceiveDate FROM SALES S INNER JOIN (SELECT W.Item, W.SerialNum, MIN(W.SalesDate) FROM WARRANTY W WHERE W.Item = S.Item AND W.SerialNum = S.SerialNum GROUP BY Item, SerialNum, SalesDate) WW ON S.Item = WW.Item AND WW.SerialNum
+7
source share
2 answers

It looks like you have a JOIN link in the wrong place.

 SELECT S.Item, S.SerialNum, S.ReceiveDate FROM SALES S INNER JOIN ( SELECT W.Item, W.SerialNum, MIN(W.SalesDate) MinSalesDate FROM WARRANTY W GROUP BY Item, SerialNum ) WW ON S.Item = WW.Item AND S.SerialNum = WW.SerialNum

Change, based on your comments about filtering, you can put the WHERE on your inner SELECT :

 SELECT S.Item, S.SerialNum, S.ReceiveDate, WW.MinSalesDate FROM SALES S INNER JOIN ( SELECT W.Item, W.SerialNum, MIN(W.SalesDate) MinSalesDate FROM WARRANTY W WHERE yourFilter here GROUP BY Item, SerialNum ) WW ON S.Item = WW.Item AND S.SerialNum = WW.SerialNum
+13
source

You can try common_table_expression, not JOIN. Check out the WITH clause here.

It could be something like this:

 WITH Warranty_CTE (Item, SerialNum, MinSalesDate) AS ( SELECT W.Item, W.SerialNum, MIN(W.SalesDate) MinSalesDate FROM WARRANTY W GROUP BY Item, SerialNum ) SELECT S.Item, S.SerialNum, S.ReceiveDate FROM SALES S INNER JOIN Warranty_CTE WC ON S.Item = WC.Item AND S.SerialNum = WC.SerialNum
+2
source

More articles:


All Articles