CODEWITHSUNDEEP
mysqlsqlselectcount
user2886669
user2886669

Reputation: 251

sql query count(*) returning unexpected result

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

Upvotes: 1

Views: 182

Answers (1)

Melon
Melon

Reputation: 883

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.

Upvotes: 1

Related Questions