CODEWITHSUNDEEP
mysqlsqljoinleft-joininner-join
George Rapley
George Rapley

Reputation: 25

Complex Joins (Joining Joins)

I have 4 tables: booking, address, search_address & search_address_log

Tables: (relevant cols)

booking: (pickup_address_id, dropoff_address_id)

address: (address_id, postcode)

search_address: (address_id, postcode)

search_address_log: (id, from_id, to_id)

What I need to do is have a count from both booking and search_address_log grouped by the pickup/dropoff & from/to postcodes.

I can do this individually for each i.e.:

booking:

SELECT
a1.postcode b_From,
a2.postcode b_to,
COUNT(*) b_count
FROM booking b
INNER JOIN address a1 ON b.pickup_address_id = a1.address_id
INNER JOIN address a2 ON b.destination_address_id = a2.address_id
GROUP BY b_From, b_To
ORDER BY COUNT(*) DESC
LIMIT 10

search_address_log:

SELECT
sa1.postcode s_From, 
sa2.postcode s_To,
COUNT(*) s_count
FROM search_address_log sal
INNER JOIN search_address sa1 ON sal.from_id=sa1.address_id
INNER JOIN search_address sa2 ON sal.to_id=sa2.address_id
GROUP BY s_From, s_To
ORDER BY COUNT(*) DESC
LIMIT 10

Returning tables like:

|    b_To   b_From    b_count   ||     s_To   s_From    s_count   |
|    x      y         10        ||     x       y       50         |
|    a      b         5         ||     a       b       60         |

WHAT I NEED:

|    To    From    b_count   s_count   |
|    x      y         10       50      |
|    a      b         5        60      |

Thanks,

George

Upvotes: 2

Views: 68

Answers (3)

dan b
dan b

Reputation: 1182

If every booking has a pickup_address and a destination_address and every search_address_log from_id an to_id has a search address and also that the codes in booking are the same as those in the search address log then you can do a join as in

select t1.b_from, t1.b_to,  t1.b_count, t2.s_count from
(SELECT
a1.postcode b_From,
a2.postcode b_to,
COUNT(*) b_count
FROM booking b
INNER JOIN address a1 ON b.pickup_address_id = a1.address_id
INNER JOIN address a2 ON b.destination_address_id = a2.address_id
GROUP BY b_From, b_To) t1
inner join 
(SELECT
sa1.postcode s_From, 
sa2.postcode s_To,
COUNT(*) s_count
FROM search_address_log sal
INNER JOIN search_address sa1 ON sal.from_id=sa1.address_id
INNER JOIN search_address sa2 ON sal.to_id=sa2.address_id
GROUP BY s_From, s_To) t2 on (t1.b_from =  t2.s_from and t1.b_to = t2.s_to)

Upvotes: 0

zebediah49
zebediah49

Reputation: 7611

Proposal: select on the addresses -- get counts of the to/from pairs, and then add them up by postcode

SELECT t.postcode, f.postcode, SUM(sal.count), SUM(b.count) 
    FROM search_address t, search_address f
    LEFT JOIN ( SELECT from_id, to_id, COUNT(*) count 
                    FROM search_address_log GROUP BY from_id, to_id ) sal 
        ON sal.from_id=f.address_id AND sal.to_id=t.address_id
    LEFT JOIN ( SELECT pickup_address from_id, destination_address_id to_id, COUNT(*) count 
                    FROM booking GROUP BY from_id, to_id) b
        ON b.from_id=f.address_id AND b.to_id=t.address_id
    WHERE sal.count > 0 OR b.count > 0
    GROUP BY t.postcode, f.postcode;

This will scale based on number of addresses squared, which may end up worse than the "generate independent summaries and then union them" scheme outlined in another answer. It's a bit more concise, however.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Technically, what you want is a full outer join, but MySQL doesn't support that. However, the following should do what you want -- getting summaries for each from and to value for the two columns:

SELECT b_from, b_to, sum(b_count) as b_count, sum(s_count) as s_count
FROM ((SELECT a1.postcode as b_From, a2.postcode as b_to, COUNT(*) as b_count, 0 as s_count
       FROM booking b INNER JOIN
            address a1
            ON b.pickup_address_id = a1.address_id INNER JOIN
            address a2
            ON b.destination_address_id = a2.address_id
       GROUP BY b_From, b_To
      ) UNION ALL
      (SELECT sa1.postcode as s_From, sa2.postcode as s_To, 0, COUNT(*) as s_count
       FROM search_address_log sal INNER JOIN
            search_address sa1
            ON sal.from_id = sa1.address_id INNER JOIN
            search_address sa2
            ON sal.to_id = sa2.address_id
       GROUP BY b_From, b_To
      )
     ) ft
GROUP BY s_From, s_to;

Upvotes: 1

Related Questions