[SOLVED] Tricky Query

[bcoffin]

table: member

---

id

name

zipcode

gender

 

 

table: trips

---

id

mid (member.id)

trip (could be 1 or 2: 1=domestic; 2=abroad)

 

 

I'd like to query to get:

member.name, member.id, count of domestic trips, count of abroad trips

 

I'm thinking:

SELECT

m.id as mid,

m.name,

count(t.trip)

FROM member m

LEFT JOIN trip t

ON m.id = t.mid

GROUP BY mid,trip

 

or something like that, to return:

 

{1, adam, 4, 2}

{2, barb, 7, 16}

{4, chris, 42, 2}

 

and so on..

 

 

 

 

 

[bcoffin]

Or maybe I'm supposed to use the SUM(IF(t.trip=1)) AS local, SUM(IF(t.trip=2)) AS domestic?

[wsantos]

Sad that I couldn't come up with a single query...

But try this and post the result of each step.

 

DROP TEMPORARY TABLE IF EXISTS tmp_domestic;
CREATE TEMPORARY TABLE tmp_domestic SELECT mid,COUNT(trip) AS dom FROM trip WHERE trip = 1 GROUP BY mid;
DROP TEMPORARY TABLE IF EXISTS tmp_international;
CREATE TEMPORARY TABLE tmp_international SELECT mid,COUNT(trip) AS intern FROM trip WHERE trip = 2 GROUP BY mid;
SELECT m.id AS ID,m.name AS NAME,d.dom AS DOMESTIC,i.intern AS INTERNATIONAL FROM member m LEFT JOIN tmp_domestic d ON d.mid = m.id LEFT JOIN tmp_international i ON i.mid=m.id;

 

I'm hoping to see a better solution for this from our colleagues.

And also use code tags.

 

[toplay]

Or maybe I'm supposed to use the SUM(IF(t.trip=1)) AS local, SUM(IF(t.trip=2)) AS domestic?

 

Yeah, something like this:

 

SELECT 
         m.id as "mid"
       , m.name
       , SUM(IF(t.trip = 1, 1, 0)) AS total_domestic_trips
       , SUM(IF(t.trip = 2, 1, 0)) AS total_abroad_trips

  FROM member m
LEFT JOIN trips t ON m.id = t.mid

GROUP BY 
    t.mid
;

 

[wsantos]

Or maybe I'm supposed to use the SUM(IF(t.trip=1)) AS local, SUM(IF(t.trip=2)) AS domestic?

 

Yeah, something like this:

 

SELECT 
         m.id as "mid"
       , m.name
       , SUM(IF(t.trip = 1, 1, 0)) AS total_domestic_trips
       , SUM(IF(t.trip = 2, 1, 0)) AS total_abroad_trips

  FROM member m
LEFT JOIN trips t ON m.id = t.mid

GROUP BY 
    t.mid
;

 

 

Just wondering ... what would be the overhead effect of that if the database got bigger?

[toplay]

Just wondering ... what would be the overhead effect of that if the database got bigger?

 

Joins are much more efficient than let's say sub-queries, but it all depends on what primary keys and indexes are created and available for use by MySQL.

[bcoffin]

Thanks you guys.

WOrked like a charm!

 

ps. Anybody in the NYC area for php/mysql work?