SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select one recored
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divan
Posting Yak Master

153 Posts

Posted - 05/16/2013 :  11:01:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/16/2013 :  11:04:32  Show Profile  Reply with Quote
SELECT TOP 1 M.*
INNER JOIN MPL_EXPOSURE M ON MONTH_DETAIL MI.CLIENT_NUMBER = M.CLIENT_NUMBER

Cheers
MIK
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 05/16/2013 :  11:15:25  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/16/2013 :  11:33:04  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 05/16/2013 11:33:25
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 05/16/2013 :  11:38:22  Show Profile  Reply with Quote
yes it is called T.CLIENT_NUMBER
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/16/2013 :  11:41:56  Show Profile  Reply with Quote
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
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 05/16/2013 :  11:47:42  Show Profile  Reply with Quote
THIS ONLY SELECTS THE FIRST RECORD IN #TEMP1.. #TEMP1 HAS 2100 RECORDS WITH DISTINCT CLIENT_NUMBER
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/16/2013 :  12:42:02  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000