MySql select from already selected data

Question

Hello I am trying to select data from the already selected values.

Current query:

select t.ticket_num, t.item_id, t.trans_id
from tickets as t
inner join ticket_items as ti
on ti.id = t.item_id
where ti.online = 1
    and ti.bundle = 5
    and ti.type_id = 2

Data sample:

num   |   item    | tran
5699    1328    766
5700    1328    766
5701    1328    766
5702    1328    766
5703    1328    766
5704    1330    767
5705    1330    767
5706    1330    767
5707    1330    767
5708    1330    767
5709    1332    768
5710    1332    768
5711    1332    768
5712    1332    768
5713    1332    768
5714    1333    768
5715    1333    768
5716    1333    768
5717    1333    768
5718    1333    768
5719    1334    768
5720    1334    768
5721    1334    768
5722    1334    768
5723    1334    768
5724    1336    769
5725    1336    769
5726    1336    769
5727    1336    769
5728    1336    769
5729    1338    770
5730    1338    770
5731    1338    770
5732    1338    770
5733    1338    770
5734    1339    770
5735    1339    770
5736    1339    770
5737    1339    770
5738    1339    770
5739    1340    770
5740    1340    770
5741    1340    770
5742    1340    770

From this data I want only the ones that have 15 or more items on tran, as you can see 766 has only 5 but 768 has 15 so I want everything from this data that has more than or equal to 15.

Thanks

Jason Q. Ng · Answer 1 · 2015-11-10T00:58:07.413

Perform a subquery that looks up all groups of tran in your desired query that have 15 or more records (the key is using the GROUP BY and HAVING clauses). Then in your main query select all records where tran is found IN the subquery. So for instance:

select 
    t.ticket_num, t.item_id, t.trans_id 
from tickets as t
inner join ticket_items as ti
    on ti.id = t.item_id
where ti.online = 1
    and ti.bundle = 5
    and ti.type_id = 2
    and trans_id in 
    (select t.trans_id
        from tickets as t
        inner join ticket_items as ti
            on ti.id = t.item_id
        where ti.online = 1
            and ti.bundle = 5
            and ti.type_id = 2
        GROUP BY t.trans_id 
        HAVING count(t.trans_id)>=15
    )

You could probably clean up the redundant JOIN by pulling it out into a Common Table Expression like so:

WITH tquery as (SELECT * 
    from tickets
    inner join ticket_items as ti
        on ti.id = t.item_id
    where ti.online = 1
        and ti.bundle = 5
        and ti.type_id = 2
)
select 
    ticket_num, item_id, trans_id 
from tquery
where trans_id in 
    (select t.trans_id
        from tquery as t
        GROUP BY t.trans_id 
        HAVING count(t.trans_id)>=15
    )

More about the having clause in case you're wondering how it works: What's the difference between HAVING and WHERE?

MySql select from already selected data

1 Answers1