SQL Multi-Table Query Question

Question

Post reply

SQL Multi-Table Query Question

Leslie-202186

SSC Enthusiast

Points: 174
More actions
February 18, 2005 at 2:12 pm

#88122

I have 3 tables - SOHEADER (sales order header info) SOLINE (sales order line-item info) and XCCTRAN (credit card transaction table).
SOHEADER is 1 to many SOLINE
SOHEADER is 1 to many XCCTRAN
The problem is because I have two 1:* joins (All joins on the same OrdNbr field), my line-item detail is duplicated if there are more than 1 credit card table entry for the join on order number.
I want to basically just pull the first XCCTRAN record that exists.
Any ideas on how to do the select on XCCTRAN to pull just the "first" transaction XCCTRAN if there are dupes.
Thanks,
Leslie

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

See thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=162816

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

(Don't post in multiple places... you will get an answer...)

You can try something along the lines:

SELECT x, y, z

FROM SOHEADER HDR

JOIN SOLINE LINE ON( HDR.OrdNbr = LINE.OrdNbr)

JOIN (SELECT TOP 1 TRAN.OrdNbr FROM XCCTRAN TRAN

INNER JOIN SOHEADER SHDR ON TRAN.OrdNbr = SHDR.OrdNbr) XCC ON( HDR.OrdNbr = XCC.OrdNbr)

I wasn't born stupid - I had to study.

Leslie-202186 SSC Enthusiast Points: 174 More actions · Answer 3

Farrell,

Thanks for the suggestion. Unfortunately the version of SQL I have (MS SQL 8.0) doesn't support "TOP" as a selection criteria. I was bummed when I found this out! I get an error when I try using "TOP".

Cheers,

Leslie

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

That is very odd. I though SQL 8.0 was basically a slimmed version of SQL 2000. If you do a SELECT TOP 1 * FROM CommonTable on one of your well known tables, what error do you get?

I wasn't born stupid - I had to study.

Leslie-202186 SSC Enthusiast Points: 174 More actions · Answer 5

Hi Farrell,

This is my query syntax:

Select top 1 from xcctran

This is my error:

Server: Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'from'.

Leslie

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Try this:

SELECT TOP 1 * FROM xcctran (you need to either specify a field or use them all with the *)

I wasn't born stupid - I had to study.

Leslie-202186 SSC Enthusiast Points: 174 More actions · Answer 7

Farrell,

Thank you so much. Boy do I feel like a do-do! It's funny cuz when I searched the TSQL help files, there is no reference to "TOP" or "BOTTOM", tho' years ago (back in DBase II days) I knew you could use Top and Bottom as selection qualifiers.

Thank you! You made my day (and late on Friday to boot!)

Leslie