Adding subqueries to a query

Question

I'm new to sub-queries and think I need them in the following situation, but am not sure how to proceed.

I have the following query. Now, I have two things I need to add to the query =

For CA consumers, they can only have 1 car accident (car_acc), while in other states it does not matter.

In FL, MD, NH, and WA currently insured can be 0 (boolean), while in the other states it should only be 1.

SELECT Count(*),
       Avg(sl.our_cost),
       Avg(sl.purchase_price)
FROM   sold_leads AS sl
       INNER JOIN leads AS l
               ON l.id = sl.lead_id
       INNER JOIN contacts AS c
               ON c.lead_id = l.id
       INNER JOIN drivers AS d
               ON d.lead_id = l.id
WHERE  c.state IN( 'AK', 'AL', 'AR', 'AZ',
                   'CA', 'CO', 'CT', 'DC',
                   'DE', 'FL', 'GA', 'HI',
                   'IA', 'ID', 'IL', 'IN',
                   'KS', 'KY', 'LA', 'MD',
                   'ME', 'MI', 'MN', 'MO',
                   'MS', 'MT', 'NC', 'ND',
                   'NE', 'NH', 'NJ', 'NM',
                   'NV', 'NY', 'OH', 'OK',
                   'OR', 'PA', 'RI', 'SC',
                   'SD', 'TN', 'TX', 'UT',
                   'VA', 'VT', 'WA', 'WI',
                   'WV', 'WY' )
       AND l.leg = 0
       AND ( Datediff(CURRENT_DATE, d.date_of_birth) / 365 ) >= 50
       AND l.create_date >= '2012-1-1';

How would I go about adding these subqueries to my query? Is there a better method to find that information?

EDIT:

I should mention that it should have read 1 'car accident' or 'violation'. I have two different tables with car accidents and violations, and so for CA residents, I need only those who have 1 accident OR 1 violation.

Important as Wyoming certainly is, do we need to list it twice? — Strawberry
– Strawberry, Commented Jan 28, 2013 at 17:42
Also, provide an indication of what the output should look like. — Strawberry
– Strawberry, Commented Jan 28, 2013 at 17:46

Johan · Accepted Answer · 2013-01-28 18:21:24Z

Because the data for car_accidents, violations is in separate tables, you need to join them using a left join.

Hope this makes sense :-)

SELECT Count(*),
       Avg(sl.our_cost),
       Avg(sl.purchase_price)
FROM   sold_leads AS sl
       INNER JOIN leads AS l
               ON l.id = sl.lead_id
       INNER JOIN contacts AS c
               ON c.lead_id = l.id
       INNER JOIN drivers AS d
               ON d.lead_id = l.id
       LEFT JOIN 
  (SELECT ca1.driver_id, COUNT(*) AS cnt FROM car_accidents ca1 GROUP BY ca1.driver_id) ca 
               ON (c.state = 'CA' and ca.driver_id = d.id)
       LEFT JOIN 
  (SELECT v1.driver_id, COUNT(*) AS cnt FROM violations v1 GROUP BY v1.driver_id) v
               ON (c.state IN ('CA') and v.driver_id = d.id)
WHERE  c.state IN( 'AK', 'AL', 'AR', 'AZ','CA', 'CO', 'CT', 'DC',
                   'DE', 'FL', 'GA', 'HI','IA', 'ID', 'IL', 'IN',
                   'KS', 'KY', 'LA', 'MD','ME', 'MI', 'MN', 'MO',
                   'MS', 'MT', 'NC', 'ND','NE', 'NH', 'NJ', 'NM',
                   'NV', 'NY', 'OH', 'OK','OR', 'PA', 'RI', 'SC',
                   'SD', 'TN', 'TX', 'UT','VA', 'VT', 'WA', 'WI',
                   'WV', 'WY' )
       AND l.leg = 0
       AND ( Datediff(CURRENT_DATE, d.date_of_birth) / 365 ) >= 50
       AND l.create_date >= '2012-1-1'
       AND IFNULL(v.cnt,0) < 2 AND ifnull(ca.cnt.0) < 2
       AND (c.state IN ('FL','MD','NH','WA') OR c.IsInsured = 1)

note It would make much more sense to have the states in a separate table. That way you can put the terms and conditions in that table and use them in your join criteria.

Table state
-----------
id : varchar(2) primary key not null
country : varchar(45) not null
max_accidents : int null
max_violations : int null
must_be_insured : boolean not null

Then you can rewrite the query as:

SELECT Count(*),
       Avg(sl.our_cost),
       Avg(sl.purchase_price)
FROM   sold_leads AS sl
       INNER JOIN leads AS l
               ON l.id = sl.lead_id
       INNER JOIN contacts AS c
               ON c.lead_id = l.id
       INNER JOIN drivers AS d
               ON d.lead_id = l.id
       INNER JOIN state s ON (s.id = c.state)
       LEFT JOIN 
(SELECT ca1.driver_id, COUNT(*) AS cnt FROM car_accidents ca1 GROUP BY ca1.driver_id) ca 
           ON (s.max_accidents IS NOT NULL AND ca.driver_id = d.id)
   LEFT JOIN 
(SELECT v1.driver_id, COUNT(*) AS cnt FROM violations v1 GROUP BY v1.driver_id) v
           ON (s.max_violations IS NOT NULL AND v.driver_id = d.id)
       AND l.leg = 0
       AND (Datediff(CURRENT_DATE, d.date_of_birth) / 365.25 ) >= 50
       AND l.create_date >= '2012-1-1'; 
       AND IFNULL(v.cnt,0) < 2 AND ifnull(ca.cnt.0) < 2
       AND (s.must_be_insured = 0 OR c.IsInsured = 1)

Whoops, I see this response treated "car accidents" as a separate table. I assumed you had a column called "car_acc" somewhere. If it's its own table, go with Johan's approach over mine.
Wow, thanks. 'currently insured' isn't it's own table though. it's just in the contacts table as a boolean.

Topher Hunt · Accepted Answer · 2013-01-28 17:57:21Z

0

I know it's not the same as subqueries, but I often solve this "subconditions" problem with a CASE statement. You can add one to the end of your WHERE clause:

WHERE c.state IN (...)
    AND l.leg = 0
    AND ( Datediff(CURRENT_DATE, d.date_of_birth) / 365 ) >= 50
    AND l.create_date >= '2012-1-1'
    AND CASE 
        # Identify cases where record should be excluded
        WHEN c.state = 'CA' AND c.car_acc > 1   THEN 0
        WHEN c.state NOT IN ('FL', 'MD', 'NH', 'WA') AND c.currently_insured = 1   THEN 0
        ELSE 1
    END = 1

Caveat: I don't know how this affects performance, compared to other filtering approaches.

answered Jan 28, 2013 at 17:57

Topher Hunt

4,8833 gold badges32 silver badges60 bronze badges

Collectives™ on Stack Overflow

Adding subqueries to a query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related