CODEWITHSUNDEEP
mysql
ATMathew
ATMathew

Reputation: 12856

Adding subqueries to a query

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 =

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.

Upvotes: 2

Views: 72

Answers (2)

Johan
Johan

Reputation: 76537

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)

Upvotes: 1

Topher Hunt
Topher Hunt

Reputation: 4804

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.

Upvotes: 0

Related Questions