SELECT with GROUP_CONCAT, GROUP BY, HAVING

Question

I Have table with odd_id and i want to select combinations for different ticket_id's. Here's my query:

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

And it gives me 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 Having function, it doesn't give me results. Clearly: it gives me for 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 code works fine, but if oddsconcat is larger then this one, it doesn't returns 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'

What is the `group_concat_max_len`? Does your string exceed this length? — Salman A, Oct 27 '11 at 12:52
but to me it doesn't matter, because when i select without HAVING STATEMENT it gives me what i want, but in HAVING CLAUSE something goes wrong :/ — , Oct 27 '11 at 13:38

score 18 · Accepted Answer · answered Oct 27 '11 at 13:51

18

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 = .....

Now the output in oddsconcat is determinatistic, because duplicates are eliminated and the items are in ascending order.
This should make matching much easier.

answered Oct 27 '11 at 13:51

Johan

74,508
24
191
319

OMG. Thank you mate. If you were here i'd buy some beer for you – Oct 27 '11 at 13:56
what to put in `HAVING oddsconcat = here` is the column that are being `group_concat` have String values? for example if there are 3 types of oddsconcat (assuming string type) a, b, c now i only want to group concat column having a and b only! – Muhammad Babar Feb 09 '15 at 14:34

ypercubeᵀᴹ · Answer 2 · 2011-10-27T14:08:37.957

1

Assuming that (ticket_id, odd_id) combination is UNIQUE, this will give you all tickets that contain those two odds (and possibly more 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 tickets that contain exactly those two odds (and no 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

edited Oct 27 '11 at 14:08

answered Oct 27 '11 at 14:01

ypercubeᵀᴹ

113,259
19
174
235

Nice way @ypercube. But due a lot of reasons i couldn't use this algorythim. Thanks anyway :) – Nov 02 '11 at 13:25
i tried your version (second one) and it doesn't works.. if i have 'odd_id in (1,2) and COUNT = 2 it gives me '1,2' and '1,2,?' too, because it takes only '1,2' in both case. :( – Nov 27 '11 at 12:50
What do you mean with "it gives me `1,2,?`". What does the `?` stand for? Another number? – ypercubeᵀᴹ Nov 27 '11 at 19:37
Do you have `NULL` values stored in `odd_id` column? – ypercubeᵀᴹ Nov 27 '11 at 19:39
? means any number value. No. i have no NULL values in odd_id. – Dec 15 '11 at 07:28

SELECT with GROUP_CONCAT, GROUP BY, HAVING

2 Answers2