Geek Answers Handbook

SELECT with GROUP_CONCAT, GROUP BY, HAVING

I have a table with odd_id and I want to choose combinations for different ticket_id. Here is my request:

SELECT ticket_id, GROUP_CONCAT(odd_id) as oddsconcat FROM ticket_odds GROUP BY ticket_id

And that gives me the following:

 '28', '14472510,14472813,14472546,14472855,14472746,14472610,14472647' '29', '14471149,14471138,14471125,14475603' '30', '14471823,14471781,14471655,14471865,14471597,14471968,14471739,14471697,14471923' '31', '14473874,14473814,14473862,14473838,14473790,14473802,14473826,14473850' '32', '14471588,14472766,14471651,14471777,14471419' '33', '14472647,14472605,14471650,14471734' '34', '14471588,14472704,14471817' '35', '14475279,14474499' '282', '14472756,14472652,14472813' '283', '14471588,14472766,14471419,14471777,14471651' '284', '14474521' '285', '14474529' '286', '14474547' '287', '14471134,14471199,14473636,14471242,14471398,14471237'

But if I use the Function function, it does not give me results. Clear: this gives me the following:

 SELECT ticket_id, GROUP_CONCAT(odd_id) as oddsconcat FROM ticket_odds GROUP BY ticket_id HAVING oddsconcat = '14475279,14474499'

Returns ticket_id 35, and everyone is happy, and the code works fine, but if oddsconcat is larger than this, it does not return any value. for ex:

 SELECT ticket_id, GROUP_CONCAT(odd_id) as oddsconcat FROM ticket_odds GROUP BY ticket_id HAVING oddsconcat = '14473874,14473814,14473862,14473838,14473790,14473802,14473826,14473850'
+4
source share
2 answers

I would rewrite it as:

 SELECT ticket_id, GROUP_CONCAT(DISTINCT odd_id ORDER BY odd_id ASC) as oddsconcat FROM ticket_odds GROUP BY ticket_id HAVING oddsconcat = .....

The output in oddsconcat is now determinative, since duplicates are excluded and the elements are in ascending order.
This should make matching a lot easier.

+13
source

Assuming that (ticket_id, odd_id) a UNIQUE combination, this will give you all the tickets containing these two factors (and possibly other odds):

 SELECT ticket_id, GROUP_CONCAT(odd_id) as oddsconcat FROM ticket_odds WHERE odd_id IN (14475279,14474499) GROUP BY ticket_id

And this will give you all the tickets that contain exactly these two chances (and not others):

 SELECT ticket_id, GROUP_CONCAT(odd_id) as oddsconcat FROM ticket_odds WHERE odd_id IN (14475279,14474499) --- list GROUP BY ticket_id HAVING COUNT(*) = 2 --- size of list
+1
source

More articles:


All Articles