|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Limiting View To Distinct Records
I am creating a view using two tables.
TableA ID CUST_ORDER_ID NOTES TableB ID CUST_ORDER_ID LINE_NO PART_ID The new view select statement is: SELECT dbo.TableA.ID, dbo.TableA.CUST_ORDER_ID, dbo.TableA.NOTES, dbo.TableB.LINE_NO, dbo.TableB.PART_ID FROM dbo.TableA INNER JOIN dbo.TableB ON dbo.TableA.CUST_ORDER_ID = dbo.TableB.CUST_ORDER_ID Sometimes TableB will contain multiple lines for the same CUST_ORDER_ID so my view will sometimes return multiple lines. I would like to change it to only return the first line that it finds. I'm pretty sure that I need to use a DISTINCT statement somewhere in there, but am not finding the right syntax. Any suggestions? Thanks in advance, Sheldon |
|
#2
|
||||
|
||||
|
Code:
SELECT DISTINCT A.ID, A.Cust_OrderID, A.Notes, B.Line_No, B.PartID FROM TableA As A INNER JOIN TableB As B On (A.Cust_OrderID = B.Cust_OrderID) |
|
#3
|
|||
|
|||
|
select a.cust_order_id,
a.notes, b.line_no, b.part_id from tableA as a inner join (select distinct cust_order_id, line_no, part_id from tableB) as b on a.cust_order_id = b.cust_order_id i think this is one of the solution. best regards Tonny |
![]() |
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Limiting View To Distinct Records |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|