Lberteh Lberteh - 1 year ago 52
SQL Question

mySQL Select where results from column "b" have column "a" in common

This one is kinda hard to explain, I'll give it a shot.

I have this table where one of the columns is the type column. The salesperson will insert records that will contain a b_id and also an action_id.

with the following code I retrieve some info,

SELECT entry_type, COUNT(DISTINCT(b_name)) AS '# of prospects',
SUM(case when entries.out_id = '1' then 1 else 0 end) 'No Interest',
SUM(case when entries.out_id = '2' then 1 else 0 end) 'Needs Follow Up',
SUM(case when entries.out_id = '3' then 1 else 0 end) 'Appointment Booked'
FROM entries
LEFT JOIN outcomes on outcomes.out_id = entries.out_id
LEFT JOIN type on type.type_id = entries.type_id
LEFT JOIN business on entries.b_id = business.b_id
LEFT JOIN users on users.user_id = entries.user_id
WHERE b_name LIKE 'July%' AND (entries.type_id = 1 OR entries.type_id = 2 OR entries.type_id = 14)
GROUP BY entry_type;

The result is the following

Call 4 1 2 1
Follow Up Contact 2 0 0 2
Walk In 1 1 0 0

The thing is, There are 2 possible initial actions, "Call" or "Walk In". "Follow Up Contact" is used if necessary after a initial call or walk in. As you can see, I have 2 appointments booked originated from this follow up. Here is the question. How do I know if this follow up contact is related to an initial call or an initial walk in?

I need to be able to generate a report specifying how many appointments were originated from each type of approach ( call or walk in ).

Thanks in advance

Answer Source

Use a self-join:

SELECT e1.type AS original_type, COUNT(e2.b_id) AS count
FROM entries AS e1
LEFT JOIN entries AS e2 ON e2.b_id = e1.b_id AND e2.entry_type = 'Follow Up Contact'
WHERE e1.entry_type IN ('Call', 'Walk In')
GROUP BY original_type