select one recored - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select one recored

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2013-05-16 : 11:01:48

I have a table called exposure this table has multiple records for each exposure where each exposure has a client_number and exposure_type..

I have another table month_detail which also has multiple records for each exposure and also each exposure has a client_number and account_year

I am trying to do a inner join statement

INNER JOIN MPL_EXPOSURE M ON MONTH_DETAIL MI.CLIENT_NUMBER = M.CLIENT_NUMBER

When I do this I get all the records for the client number in mpl_exposure.

How can I just select one record from mpl_exposure that matches the client_number in month_detail.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-16 : 11:04:32

SELECT TOP 1 M.*
INNER JOIN MPL_EXPOSURE M ON MONTH_DETAIL MI.CLIENT_NUMBER = M.CLIENT_NUMBER

Cheers
MIK

divan
Posting Yak Master

153 Posts

Posted - 2013-05-16 : 11:15:25

Well this did not work.. Maybe a sample script will help

SELECT T.PHYSICIAN,
T.CLIENT_NUMBER,
T.TOTAL_PREMIUM
M.EXPOSURE_TYPE
FROM #TEMP1 T
INNER JOIN MPL_EXPOSURE M
ON T.CLIENT_NUMBER = (SELECT TOP 1 (CLIENT_NUMBER) FROM MPL_EXPOSURE M2 WHERE M2.CLIENT_NUMBER = M.CLIENT_NUMBER
AND M.EXPOSURE_TYPE IN (1,7,9))

So when I run this I get all records in MPL_EXPOSURE for each client_number in #temp1 and all I need is one of the records in MPL_EXPOSURE for each client_number in #temp1

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-16 : 11:33:04

I don't see a correct joining condition of the temp table with the MPL_exposure in your query.. Does temp table has Client_number field and that you are looking for a record where client_number of temp is same as that in MPL_Exposure?

Cheers
MIK

divan
Posting Yak Master

153 Posts

Posted - 2013-05-16 : 11:38:22

yes it is called T.CLIENT_NUMBER

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-16 : 11:41:56

SELECT Top 1
T.PHYSICIAN,
T.CLIENT_NUMBER,
T.TOTAL_PREMIUM
M.EXPOSURE_TYPE
FROM #TEMP1 T
INNER JOIN MPL_EXPOSURE M
ON T.CLIENT_NUMBER = M.CLIENT_NUMBER
WHERE M.EXPOSURE_TYPE IN (1,7,9)

Cheers
MIK

divan
Posting Yak Master

153 Posts

Posted - 2013-05-16 : 11:47:42

THIS ONLY SELECTS THE FIRST RECORD IN #TEMP1.. #TEMP1 HAS 2100 RECORDS WITH DISTINCT CLIENT_NUMBER

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-16 : 12:42:02

well its because of the "TOP 1" if you remove it and run the query it will return all the records where same client_number (having exposure_type in (1,7,9)) of one table exist in the other table

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources