You must Sign In to post a response.

16 Mar 2018 17:55

Joined:	31/01/2010
Level:	Silver
Points:	166

Points:	5

Inner join sql qry for same table with condition

table

tid customername category engineer
1 aaaaaa complaint king
2 bbbbb complaint queen
3 aaaaaa recomplaint queen

i need report below like

condition = recomplaint

tid customername enginner previousengineer
3 aaaaaa queen king

qry will taken recomplaint and previous engineer only. not take before previous engineer

am using qry like this

Select CCN.Refno,CCN.CustomerID,CCN.CompPerName,CCN.TakenEngineer,CC.TakenEngineer From CustomerComplaint as CCN inner join CustomerComplaintNew as CC On CCN.CustomerID =CC.CustomerID and CCN.WarrantyType='RECOMPLAINT' AND CC.RefNo < CCN.RefNo

But it will take previous all engineer

#769785

11 Oct 2018 16:58

Joined:	06/04/2011
Level:	Gold
Points:	857

Points:	2

You can use sub query to get the desired record. Use that sub query as condition and select the top record in an order.

Exp: SELECT TOP 1 FROM tablaA WHERE id < (SELECT id from tableA where CONDITION) ORDER BY id DESC / ASC.

Submit New Thread

Return to Return to Discussion Forum

Inner join sql qry for same table with condition

About dotnetspider.com

Quick Links

General

Services