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.
| Author |
Topic |
|
MRdNk
Starting Member
6 Posts |
 Posted - 2010-05-21 : 14:41:49
|
| Hello All,I have a bit of a problem, that I'm trying to solve.I have two tables (actually I have several, but the issue is with one particular join).My end result is to have one row for each entry in the first table, using SSIS to export the data to a CSV file.So the first table holds a list of Customers (tblCustomers) which includes a unique record ID (RID), the second table has a list of key contacts (tblKeyContacts) which links to the customers via the record ID (RID) - more than one entry has the same RID; and no unique ID. I'm using MS SQL Server 2005, and SSIS in Visual Studio 2005. The problem is that the second table (tblKeyContacts) has more than 1 key contact for each of the Customers, whereas I need the final end result to have only one line for each customer: One Customer, One Key Contact. So how do I get my end result?Currently I have a stored procedure that gets my Customer Information (tblCustomers - sp_customers), which includes LEFT JOINS, CASE SELECTS etc from other tables, and I have a stored procedure that has a parameter for the RID to find the TOP 1 result for the first key contact (tblKeyContacts - sp_keyContact_byRID):SELECT TOP 1 * FROM tblKeyContacts kc WHERE kc.RID = @strRecord_IDIs there a FOR EACH type statement, where I can use the stored procedures together? Any help greatly appreciated, I'm happy to use Views or Store Procedures or whatever is required. It's quite annoying as there is no unique identifier for the key contacts, unfortunately I can't do anything about the design or implementation of the tables as systems outside of my control put the data into these tables.I need something like:SELECT c.*, kc.* FROM tblCustomer c LEFT JOIN tblKeyContacts kc ON c.RID = kc.RID AND <TOP 1 FROM tblKeyContacts>xP.s. I've noticed that there are a few cases that have the same contact but with the surname spelt slightly differently, I can't have duplicates - as this would be very wrong/bad for the end CSV file, the RID must be unique! We have a updated date, but I've noticed that this can be the exact same date in some examples I've seen, including the different surname spellings. |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-05-21 : 16:18:08
|
| If there are multiple rows in tblKeyContacts, for the same RID, which row do you want in the output? How to identify this particular row from tblKeyContacts? |
 |
|
|
MRdNk
Starting Member
6 Posts |
Posted - 2010-05-22 : 04:13:28
|
| Unfortunately it just has to be the first one. TOP 1. I wish there was a better way of getting the best result but it has to just be the first entry, or potentially the last. In most cases there is just one entry but some have two and though I haven't seen any there could be three. I know its not good design / practice but I have no control over it.Perhaps I could have a procedure that gets the data from tblKeyContacts and put it in a second new table with one entry for each RID, and use this table instead, but I'm not sure how to go about this. |
|
|
|
|
|
|
|
|
Return "Top 1" via Join