How to improve this query ?

Question

0.00/5 (No votes)

See more:

I have 2 tables customer and customer address. CustomerId is the foreign key in customer address table. Each customer can have multiple addresses in different countries i.e. US, Canada and Mexico. I am joining these tables to get the combined result. Now if a customer doesn't have a record in the customer address table , it should show the customer records with null address. If customer have multiple addresses then US address take precedence followed by Canada and then mexico address meaning it should show only one record per customer with address with topmost precedence. I am trying to write this query using left join and union. Something like

customer left join customeraddress on (customer.customerid = customeraddress.customerid and customeraddress.countrycode = 'US')

UNION

customer left join customeradress on (customer.customerid = customeraddress.customerid and customeraddress.countrycode = 'CA')
and customer.customerid not in (select customerid's that have a us address)

I want to know if there is a better way to do this without using UNION. Also I am re-querying to get the customer ids with us address which doesn't seem the efficient way to do it.Is there any particular name for this kind of queries ? hierarchical ?

Posted 26-Aug-13 8:23am

fixthebugg

Add a Solution

2 solutions

Solution 1

I'm no sql ninja, but if I had to write I query I'd do it this way:

SQL

WITH tmpview (id, n)
     AS (SELECT a.id,
                Max([priority]) n
         FROM   (SELECT id,
                        CASE
                          WHEN countrycode = 'US' THEN 1
                          WHEN countrycode = 'CA' THEN 2
                          WHEN countrycode = 'MX' THEN 3
                        END [priority]
                 FROM   customer c
                        JOIN customeraddress a
                          ON c.id = a.customerid
                 WHERE  countrycode IN ( 'US', 'CA', 'MX' )) orderedadr
         GROUP  BY a.id)
SELECT *
FROM   address a
       JOIN tmpview t
         ON a.id = t.id

I'm pretty sure this query can still be further optimized by them sql ninjas.

Posted 26-Aug-13 9:08am

onelopez

Comments

[no name] 26-Aug-13 15:28pm

Thanks for your reply, I will accept it once I complete my testing.

[no name] 26-Aug-13 15:53pm

I have a question, In the group by you are grouping by customeraddress.id which will not give you correct result as it should be on the customeraddress.customerid. But if you do that then in the last select statement the join will not give the correct information as tmpview has a customerid and priority and customeraddress table has no concept of priority.

onelopez 26-Aug-13 22:53pm

That is true, but before the grouping was done, I retrieved all the data from the customers whose addresses where in those 3 countries. From there the address links back to the customer when you do the join statement again from the tmpview to the address table.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2013-08-26T09:19:00

You can do this by creating an AddressPriority table:

SQL

Priority Countrycode
1        'US'
2        'CA'
3        'MX'

and then use a query similar to this:

SQL

WITH Prio as (
    SELECT  CustomerID,Min(priority)
    FROM    customeraddress ca join AddressPriority ap ON ca.countrycode = p.countrycode
    GROUP BY CustomerID
    )
SELECT  *
FROM    customer c JOIN prio p ON p.CustomerID = c.CustomerID
JOIN    AddressPriority ap ON p.Priority = ap.Priority
JOIN    CustomerAddress ca ON ca.CustomerID = c.CustomerID
AND     ca.CountryCode= ap.CountryCode

Using a Select Case instead:

SQL

WITH Prio as (
    SELECT  CustomerID,Min(priority)
    FROM    (
        Select  CustomerID
               ,Case CountryCode
                    When 'US' Then 1
                    When 'CA' Then 2
                    When 'MX' Then 3
                END as Priority
        FROM    CustomerAddress
        ) as Prio
    GROUP BY CustomerID
    )
,addressprio as (
    select  CustomerID
           ,Case prio
                when 1 then 'US'
                when 2 then 'CA'
                When 3 Then 'MX'
            End as CountryCode
    From    Prio
    )
SELECT  *
FROM    customer c JOIN addressprio ap ON ap.CustomerID = c.CustomerID
JOIN    CustomerAddress ca ON ca.CustomerID = c.CustomerID
AND     ca.CountryCode = ap.CountryCode