TalkPHP - mysql JOIN challange

TalkPHP (http://www.talkphp.com/forums.php)

- Absolute Beginners (http://www.talkphp.com/absolute-beginners/)

- - mysql JOIN challange (http://www.talkphp.com/absolute-beginners/4769-mysql-join-challange.html)

captainmerton

07-21-2009 08:05 PM

mysql JOIN challange

I have spent the last week trying to create a query that returns the data from one table in the format i want it but without success. I am sure I need to JOIN a table with itself but its the COUNTing of types of row which is proving most challenging. Wonder if anyone can help or even further resolve for me. Its a challange indeed.

Essentially I want a query that will take this table:

Code:

betid                 customer         group         result

34                joebloggs        1        Win

34                another                1        Lose

36                mrjones                1        Win

37                joebloggs        1        Win

38                mrJones                1        Lose

38                joebloggs        1        Lose

40                joebloggs        2        Win

and reformat it in this way counting up wins and losses GROUPing by group, customer and then win and loss counts.

Code:

group                customer        Wins        Losses

1                joebloggs        2        1

1                mrjones                1        1

1                another                0        1

2                joebloggs        1        0

Handling 0 losses is not easy as I am essentially trying to return a count of value for a row that does not exist.

Village Idiot

07-21-2009 08:36 PM

Use 0 and 1 instead of "win" and "loose". Numbers are better for machines to parse. I would also use IDs for customers instead of names, attach a related table if you want info on them. I also don't see how a join statement is needed for what you have, join is for adding results from associated tables.

I wrote this off the top of my head, this should be something close to what you need.

Code:

SELECT

    b.group, 

    b.customer, 

    (SELECT COUNT(betid) WHERE result="win" AND customer=b.customer) as wins, 

    (SELECT COUNT(betid) WHERE result="lose" AND customer=b.customer) as losses 

FROM 

    bets b

GROUP BY 

    b.customer

ORDER BY 

    b.group ASC

I'm not fully sure how to do the grouping here, I don't have the time to play with it because I am at work.

Wildhoney

07-21-2009 08:51 PM

Building onto Village Idiot's example:

sql Code:

SELECT
    `customer` AS `customer_id`, `group`, `customer`,
    (SELECT COUNT(`id`) FROM `results` WHERE `result` = 'Win' AND `customer` = `customer_id`) AS `wins`,
    (SELECT COUNT(`id`) FROM `results` WHERE `result` = 'Lose' AND `customer` = `customer_id`) AS `losses`
FROM
    `results`
GROUP BY
    `customer`
ORDER BY
    `wins`
DESC

captainmerton

07-21-2009 09:33 PM

That is pretty much what i'm looking for thanks. Glad this was in the absolute beginners section as that is what i obviously am. Only issue i have is its returning th extra customer_id column which matches customer but its an extra column i dont really want to return. Any way i could adjust the query to return the 4 columns instead of the 5?

Wildhoney

07-21-2009 09:50 PM

Naturally! :-)

sql Code:

SELECT
    `group`, `customer`,
    (SELECT COUNT(`id`) FROM `results` WHERE `result` = 'Win' AND `customer` = `r`.`customer`) AS `wins`,
    (SELECT COUNT(`id`) FROM `results` WHERE `result` = 'Lose' AND `customer` = `r`.`customer`) AS `losses`
FROM
    `results` `r`
GROUP BY
    `customer`
ORDER BY
    `wins`
DESC

captainmerton

07-22-2009 03:56 PM

Perfect! Much appreciated.

captainmerton

07-23-2009 12:09 PM

Quote:

Use 0 and 1 instead of "win" and "loose". Numbers are better for machines to parse. I would also use IDs for customers instead of names, attach a related table if you want info on them.

Thanks for all your help guys but i wanted to pick up on these earlier comments. I think going with 0 and 1 for wins and losses makes sense. As for using ids instead of character usernames in my table - will this make a big difference. i thought about this at design stage but decided against it as i didnt really need ids as all usernames would be unique and assumed they would just be stored as hex or some kind of binary number value anyway and stored in the db in the most efficent way. Can someone explain whether going with numeric ids and using a userdetail table to get the actual character name would be better as in essence i would be reading 2 tables instead of 1 everytime i wanted a username. But will change if it is more efficient to go with numeric ids. Any thoughts?

All times are GMT. The time now is 10:32 PM.