Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free ForumsDatabaseMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old March 30th, 2004, 05:56 PM
stroyer74 stroyer74 is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Mar 2004
Posts: 1 stroyer74 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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

Reply With Quote
  #2  
Old March 30th, 2004, 09:23 PM
Memnoch's Avatar
Memnoch Memnoch is offline
Unholy Moderator
ASP Free God 14th Plane (11500 - 11999 posts)
 
Join Date: Oct 2003
Location: In hell, where did you think?
Posts: 11,760 Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 5 h 22 m 16 sec
Reputation Power: 443
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)

Reply With Quote
  #3  
Old June 24th, 2004, 05:55 AM
Tonny-Soeroso Tonny-Soeroso is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Jun 2004
Posts: 36 Tonny-Soeroso User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 h 56 m 27 sec
Reputation Power: 0
Thumbs up

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

Reply With Quote
Reply

Viewing: ASP Free ForumsDatabaseMicrosoft SQL Server > Limiting View To Distinct Records


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway