How do I get the first instance of an inner join to do a "select into" in SQL Server?

Question

This is my SQL Statement thus far

INSERT INTO RegisteredDonors (firstname,
                              lastname,
                              organization,
                              OrganizationType,
                              OrganizationGroup,
                              OrganizationField1,
                              OrganizationField2,
                              OrganizationOther,
                              Website,
                              PrimaryEmail,
                              AltEmail,
                              PrimaryPhone,
                              PrimaryPhoneName,
                              AltPhone,
                              AltPhoneName,
                              FaxNumber,
                              BillingFirstName,
                              BillingLastName,
                              BillingCompany,
                              BillingAddressLine1,
                              BillingAddressLine2,
                              BillingCity,
                              BillingPostalCode,
                              BillingState,
                              BillingCountry,
                              ShippingFirstName,
                              ShippingLastName,
                              ShippingCompany,
                              ShippingAddressLine1,
                              ShippingAddressLine2,
                              ShippingCity,
                              ShippingPostalCode,
                              ShippingState,
                              ShippingCountry,
                              DateAdded,
                              DonorStatusCode,
                              qbId,
                              CreatedBy)

SELECT firstname,
       lastname,
       organization,
       organization_type,
       organization_group,
       organization_field_1,
       organization_field_2,
       organization_other,
       organization_website,
       email,
       email_alt,
       telephone,
       telephone_name,
       telephone_alt,
       telephone_alt_name,
       fax,
       payment_firstname,
       payment_lastname,
       payment_company,
       payment_address_1,
       payment_address_2,
       payment_city,
       payment_postcode,
       payment_zone,
       payment_country,
       shipping_firstname,
       shipping_lastname,
       shipping_company,
       shipping_address_1,
       shipping_address_2,
       shipping_city,
       shipping_postcode,
       shipping_zone,
       shipping_country,
       GETDATE()  as CreateDate,
       1          as DonorStatusCode,
       qb.Id,
       'Me' as CreatedBy
FROM 
    cart_order co
INNER JOIN
    qb_customers qb ON (co.organization + ' (' + co.payment_zone + ')') = qb.FullyQualifiedName
ORDER BY
    co.order_id DESC

How do I get the inner join to return the first row of results from cart_order? I am using SQL Server (Azure) and I can't figure this out. I looked at this StackOverflow answer and I am not sure if it applies correctly here.

SQL Left Join first match only

Thanks!

Please don't show a gazillion irrelevant columns. Using only 3 or 4 relevant columns makes your question exactly the same, and MUCH more readable. — Peter B, Oct 01 '18 at 18:14

score 2 · Answer 1 · answered Oct 01 '18 at 18:14

2

You can use top (1):

select top (1) . . .
from cart_order co inner join
    qb_customers qb
    on co.organization + ' (' + co.payment_zone + ')') = qb.FullyQualifiedName
order by co.order_id desc

answered Oct 01 '18 at 18:14

Gordon Linoff

1,242,037
58
646
786

I am not sure about this answer. The inner join returns multiple results based on the concatenation "inner join qb_customers qb on (co.organization + ' (' + co.payment_zone + ')') = qb.FullyQualifiedName" If I do TOP(1) that will just return one row of the entire query not just the one result of "inner join qb_customers qb on (co.organization + ' (' + co.payment_zone + ')') = qb.FullyQualifiedName" – MrTouya Oct 01 '18 at 18:41
@MrTouya . . . The question is: "How do I get the inner join to return the first row of results from cart_order?". This returns one row of results that match the `from` conditions ordered by the ordering clause. I can't think of any other interpretation of your question. – Gordon Linoff Oct 01 '18 at 18:43
The target table is RegisteredDonors. I think he want 1 row per customer, top (1) is only one row. Not one row per customer. This is how my interpretation of the question. – DanB Oct 01 '18 at 19:52
@DanielBlais . . . I am not denying that might be intention. However, I see nothing at all in the question that supports that interpretation. – Gordon Linoff Oct 01 '18 at 19:57

score 1 · Accepted Answer · answered Oct 01 '18 at 18:14

You have to use ranking function.

;with co as (
    select *, rank() over(partition by organization + ' (' + co.payment_zone + ')' order by organization + ' (' + co.payment_zone + ')') rnk
    from cart_order
)
    INSERT INTO RegisteredDonors (firstname,
                              lastname,
                              organization,
                              OrganizationType,
                              OrganizationGroup,
                              OrganizationField1,
                              OrganizationField2,
                              OrganizationOther,
                              Website,
                              PrimaryEmail,
                              AltEmail,
                              PrimaryPhone,
                              PrimaryPhoneName,
                              AltPhone,
                              AltPhoneName,
                              FaxNumber,
                              BillingFirstName,
                              BillingLastName,
                              BillingCompany,
                              BillingAddressLine1,
                              BillingAddressLine2,
                              BillingCity,
                              BillingPostalCode,
                              BillingState,
                              BillingCountry,
                              ShippingFirstName,
                              ShippingLastName,
                              ShippingCompany,
                              ShippingAddressLine1,
                              ShippingAddressLine2,
                              ShippingCity,
                              ShippingPostalCode,
                              ShippingState,
                              ShippingCountry,
                              DateAdded,
                              DonorStatusCode,
                              qbId,
                              CreatedBy)

SELECT firstname,
       lastname,
       organization,
       organization_type,
       organization_group,
       organization_field_1,
       organization_field_2,
       organization_other,
       organization_website,
       email,
       email_alt,
       telephone,
       telephone_name,
       telephone_alt,
       telephone_alt_name,
       fax,
       payment_firstname,
       payment_lastname,
       payment_company,
       payment_address_1,
       payment_address_2,
       payment_city,
       payment_postcode,
       payment_zone,
       payment_country,
       shipping_firstname,
       shipping_lastname,
       shipping_company,
       shipping_address_1,
       shipping_address_2,
       shipping_city,
       shipping_postcode,
       shipping_zone,
       shipping_country,
       GETDATE()  as CreateDate,
       1          as DonorStatusCode,
       qb.Id,
       'Me' as CreatedBy

FROM co
       inner join qb_customers qb on (co.organization + ' (' + co.payment_zone + ')') = qb.FullyQualifiedName
where co.rnk = 1
order by co.order_id desc

I'm not 100% sure of rank() over(partition by organization + ' (' + co.payment_zone + ')' order by organization + ' (' + co.payment_zone + ')') rnk. If the result is not the expected one, run only the CTE part to ensure rnk = 1 is once per customer.

Ok. I think this is close. I am still getting the dupes with organization + ' (' + payment_zone + ')'. — MrTouya, Oct 01 '18 at 19:03
https://www.red-gate.com/simple-talk/sql/learn-sql-server/working-with-window-functions-in-sql-server/ — DanB, Oct 01 '18 at 19:54

How do I get the first instance of an inner join to do a "select into" in SQL Server?

2 Answers2