当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - TSQL - Multiple Values in INSERT (because of joins)

问题描述:

Im trying to insert a data from one database to another. This is what i have so far, on the select side:

USE [db2]

SELECT

sP.pers_FirstName

,sp.pers_LastName

,sPH.Phon_Number

,CASE WHEN LEFT(sPH.Phon_Number, 2) = '04' THEN sPH.Phon_number ELSE NULL END

,CASE WHEN sp.pers_gender = 1 THEN 'M' WHEN sp.pers_gender = 2 THEN 'F' ELSE 'U' END

,CASE

WHEN sP.pers_salutation = '10' THEN 8

WHEN sp.pers_salutation = '6' THEN 2

WHEN sp.pers_salutation = '7' THEN 1

WHEN sp.pers_salutation = '8' THEN 4

WHEN sp.pers_salutation = '9' THEN 5

WHEN sp.pers_salutation = 'APROF' THEN 6

WHEN sp.pers_salutation = 'Ms.' THEN 4

WHEN sp.pers_salutation = 'PROF' THEN 6

END

,sp.pers_dob

,sp.pers_CreatedDate

,sp.pers_UpdatedDate

,'Candidate'

,1

,e.Emai_EmailAddress

,sP.pers_personID

FROM [db1].dbo.person sP

LEFT JOIN [db1].dbo.PhoneLink sPL ON sp.pers_personID = sPL.PLink_recordID

LEFT JOIN [db1].dbo.Phone sPH ON sPL.PLink_PhoneId = sPH.Phon_PhoneID

LEFT JOIN [db1].dbo.EmailLink eL ON sP.pers_personID = eL.ELink_RecordID

LEFT JOIN [db1].dbo.Email e ON eL.Elink_EmailID = e.Emai_EmailID

WHERE

(

sP.pers_employedby NOT IN (

'Aspen'

,'ACH'

)

)

OR

(

sP.pers_employedby IN (

'Aspen'

,'ACH'

)

AND sP.pers_personID NOT IN (

SELECT c.oppo_PrimaryPersonID FROM [SageCRM].dbo.Opportunity c

WHERE (c.oppo_contractcompleted <= '2016-01-01' OR c.oppo_contractterminated <= '2016-01-01') and c.Oppo_Deleted is null)

AND

sp.pers_isanemployee != 'ECHO'

AND sP.pers_personID IN (

SELECT c.oppo_PrimaryPersonID FROM [SageCRM].dbo.Opportunity c

WHERE c.oppo_Status != 'In Progress' OR c.oppo_Status = 'Completed')

AND sP.pers_dod IS NULL

AND sP.pers_FirstName NOT LIKE '%test%'

AND sP.pers_LastName NOT LIKE '%test%'

AND sp.pers_isanemployee != 'SalesContact'

)

Due to the fact that each person record can have multiple phone numbers linked to them, i end up with multiple records for each person, which obviously wont work as i will end up with duplicates when i actually insert the data.

The problem is, that i need to have all of the phone numbers for each record, just displayed in a different field (home phone, work phone, mobile phone).

Any Ideas, other than doing this in a separate insert statement for each phone / email link?

-------- EDIT: -----------------------------------------------------------------

Ok so, my bad for not giving you enough information. Both of your answers were good answers so thanks for that (@Horaciux, @John Wu).

However, there is no phoneType column, just a phone number. That being said, since every mobile starts with 04 and every home phone with anything else, i can pretty easily distinguish between the two phone types.

There are duplicates in the phone table though, so i will have to delete these, most likely via CTE, shouldn't be too hard.

So, i will end up with something like this for the two phone numbers:

SELECT (phon_number FROM phone p INNER JOIN PhoneLink p1 on p1.PhoneLinkID = p.PhoneLink WHERE LEFT(p.Phon_Number, 2) = '04')

SELECT (phon_number FROM phone p INNER JOIN PhoneLink p1 on p1.PhoneLinkID = p.PhoneLink WHERE LEFT(p.Phon_Number, 2) != '04')

My duplicate removal will be something like this:

WITH CTE AS

(

SELECT phon_linkID, phon_phonNumber, ROW_NUMBER() OVER (PARTITION BY phon_phonNumber ORDER BY phon_linkID) AS RN

FROM phone

)

DELETE FROM CTE WHERE RN<>1

网友答案:

Two easy steps.

  1. Get rid of the joins to the phone number table.

  2. Lookup the phone numbers per record by using a subquery in the select clause, one for each type of phone. Example

    SELECT sP.pers_FirstName,
           sP.pers_LastName,
           (SELECT Phon_Number FROM Phone p JOIN PhoneLink pl ON pl.PhoneLinkID = p.PhoneLinkID WHERE pl.Person_ID = sP.pers_personID AND pl.Type = 'WORK') WorkPhone,
           (SELECT Phon_Number FROM Phone p JOIN PhoneLink pl ON pl.PhoneLinkID = p.PhoneLinkID WHERE pl.Person_ID = sP.pers_personID AND pl.Type = 'HOME') HomePhone
    FROM person
    
网友答案:

Without knowing your table's structure, I'll do some example.

select person.id,
  max(case when phone.type='home' then phone.vlaue else 0 end) 'home',
  max(case when phone.type='work' then phone.vlaue else 0 end) 'work'
from person,phone where...
group by person.id

Then use this query to join all other tables needed

分享给朋友:
您可能感兴趣的文章:
随机阅读: