sql query count(*) returning unexpected result

Question

Hope you had a good christmas!

Im having an issue with a query of mine,

i have this query

SELECT * , group_concat( tt.tradeID ) AS tradeArray
FROM tbl_tradesmen AS t
LEFT JOIN tbl_tradesmen_trades AS tt ON tt.tradesmenID = t.tradesmenID
LEFT JOIN tbl_trades AS trade ON trade.tradeID = tt.tradeID
LEFT JOIN tbl_tradesmen_location AS loc ON loc.tradesmenID = t.tradesmenID
LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID
GROUP BY t.tradesmenID

which returns 1 record, which is what i expect as i have 1 tradesmen in the tbl_tradesmen table. However this tradesmen has 2 trades in the tbl_tradesmen_trades table and the group_concat field contains both of these.

now i want to find out the number of records so i thought the best way is to add a count to my query like so

SELECT COUNT(*) , group_concat( tt.tradeID ) AS tradeArray
FROM tbl_tradesmen AS t
LEFT JOIN tbl_tradesmen_trades AS tt ON tt.tradesmenID = t.tradesmenID
LEFT JOIN tbl_trades AS trade ON trade.tradeID = tt.tradeID
LEFT JOIN tbl_tradesmen_location AS loc ON loc.tradesmenID = t.tradesmenID
LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID
GROUP BY t.tradesmenID

But this is returning a count value of 2 and not 1 but im not sure why this is or how to get it to return 1.

any help will be greatly appreciated Thanks

i only have test data in my database at the moment so i have 1 tradesmen with 2 trades, and i expect my query to return a count of 1 as there is 1 tradesmen but it was returning 2? then i was helped out by a member on here whos comments have disappeared :( and i got it to return a count of 1 which was great now i've added another tradesmen but still the count is 1 :( as the guy who helped me before said it would. should i remove the group by? thanks — user2886669
– user2886669, Commented Dec 30, 2013 at 22:55
I am not sure what you are after, but it looks like you want to SELECT t.tradesmenID, group_concat(tt.tradeID)....Group By t.tradesmenID. — NoChance
– NoChance, Commented Dec 30, 2013 at 23:24

Melon · Accepted Answer · 2013-12-31 08:37:11Z

1

Try this and u will understand what happening

SELECT *
FROM tbl_tradesmen AS t
LEFT JOIN tbl_tradesmen_trades AS tt ON tt.tradesmenID = t.tradesmenID
LEFT JOIN tbl_trades AS trade ON trade.tradeID = tt.tradeID
LEFT JOIN tbl_tradesmen_location AS loc ON loc.tradesmenID = t.tradesmenID
LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID

You are using JOIN to merge tradesmen with trades so if your trader men is "Jon" and make trade 1 and 2 u will get something like this:

Trader Trade
Jon    1
Jon    2

So when u make a group by t.tradesmenID you will get 1 row, because you hav only 1 tradesmen but there are 2 rows so Count give u a result value of 2

I hope this help you, sorry for my english.

answered Dec 31, 2013 at 8:37

Melon

8838 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql query count(*) returning unexpected result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related