1

I am dealing with an issue that I have to backtrack as the entire project is already in production and system has been used for a while.

I need to backtrack all the data with the following parametars.

Select * from table where bundle = 5 and count(bundle) >= 3

This will be a joined table so technically I need count of bundles greater than 2 with same transaction.

eg

id | transaction | bundle
-------------------------
1  | 123         | 5
3  | 234         | 15
12 | 1111        | 5
13 | 1111        | 15
17 | 1111        | 5
18 | 1111        | 5

My code so far

select * from table_i as ti
right join table_r as tr
on tr.id = ti.t_id
where ti.type_id = x and ti.bundle = 5 and ti.online = 1 and count(ti.bundle) >=5

Thanks

EDIT REAL CODE:

SELECT ti.*, tr.*
FROM ticket_items AS ti
INNER join transactions as tr
ON tr.id = ti.trans_id
INNER JOIN
(
    SELECT tis.trans_id, COUNT(tis.bundle) AS bundle_count
    FROM ticket_items as tis
    INNER join transactions as trs
    ON trs.id = tis.trans_id
    WHERE tis.type_id = 2
    AND tis.bundle = 5 
    AND tis.online = 1 
    HAVING bundle_count > 2
) sub0
ON sub0.trans_id = ti.trans_id
WHERE ti.type_id = 2
AND ti.bundle = 5 
AND ti.online = 1

Result: 1328 1 1 766 2 5 25 1 1 2015-10-26 20:26:41 2015-10-27 00:00:02 0 766 1 0 John Doe 123-123-1234 NULL [email protected] NULL NULL NULL NULL 1 164 Cedar Square NULL 123 rrt city province country 125 2015-10-26 20:26:41 2015-10-26 20:26:41 125.00 0.00 0.00 0.00 0 1

Table ticket_items:

id | lot | system | trans_id | type | bundle | price | print | online | date                | update              | void
1    1     2        1          1      1        100     1       0        2015-10-01 23:30:12   2015-10-03 18:49:25   0
2    1     2        1          2      15       50      1       0        2015-10-01 23:30:12   2015-10-03 16:48:15   0
3    1     3        2          1      1        100     1       0        2015-10-02 00:13:57   2015-10-02 00:22:17   1
4    1     3        2          2      15       50      1       0        2015-10-02 00:13:57   2015-10-02 00:19:17   1

Table transactions:

id | lot_id | cust | first | last| number |||||||
1  | 1      | 23   | john  | doe | 123
Improve this question
6
  • If you simply query the records where bundle is 5, then you can easily see, if you have got more than 2, or 3, or 5 (all 3 numbers are in your question). So, I do not really understand what you are trying to achive with this. Commented Nov 9, 2015 at 16:28
  • Issue is I need to look trough 2000 records and I don't want to wast time on that, I would rather query and get only the ones that were affected by the system error. I need to look at every transaction and if error has occurred I need to fix it manually. If I need to do this on over 2000 records it is very ineffective. But if I can end up with 700 that were affected than it will save me some time. Commented Nov 9, 2015 at 16:31
  • Error is only on bundle 5s.. System has issued 3 - (5s) instead of 1-15 Commented Nov 9, 2015 at 16:32
  • Sorry, but I have no idea what your are talking about. Can you pls clarify in your post what you are trying to achieve? Commented Nov 9, 2015 at 16:38
  • With that test data I wouldn't expect any records to be output as nothing on ticket_items has online = 1 Commented Nov 9, 2015 at 17:09

3 Answers 3

Reset to default
1

I'm a little confused by the wording of your question, but try this:

SELECT ti.id, ti.transaction, count(ti.bundle)
FROM table_i as ti
JOIN table_r as tr
  ON tr.id = ti.id
WHERE ti.type_id = x 
  AND ti.bundle = 5 
  AND ti.online = 1 
  AND count(ti.bundle) >=5
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

If I understand correctly, you want to find transactions where there are more than 3 with that transaction code for a bundle of 5 and with online = 1, and then bring back all the rows from the 2 tables for that transaction with a bundle of 5 and with online = 1

If so, a sub query to get the transactions with a count of more than 3 and then join that back against the 2 tables:-

SELECT ti.*, tr.*
FROM table_i AS ti
INNER join table_r as tr
ON tr.id = ti.t_id
INNER JOIN
(
    SELECT tis.transaction, COUNT(tis.bundle) AS bundle_count
    FROM table_i as tis
    INNER join table_r as trs
    ON trs.id = tis.t_id
    WHERE tis.type_id = x 
    AND tis.bundle = 5 
    AND tis.online = 1 
    GROUP BY tis.transaction
    HAVING bundle_count >= 3
) sub0
ON sub0.transaction = ti.transaction
WHERE ti.type_id = x 
AND ti.bundle = 5 
AND ti.online = 1
Improve this answer

4 Comments

Thanks, however this also returns only 1 record.. and yes your idea is correct of what I am trying to achieve here.. However this only returns 1 record
Can you post a bit of test data for the 2 tables and the output for that test data please. Currently I cannot do anything to know whether the issue is with the SQL, or with the choice of rows in the 2 WHERE clauses.
I missed out the GROUP BY line which would have caused problems.
Took me few minutes but yeah group by was missing !
0

I think you need to use having. Something like this:

select ti.*, count(ti.bundle) from table_i as ti
right join table_r as tr
on tr.id = ti.t_id
where ti.type_id = x and ti.bundle = 5 and ti.online = 1
group by ti.bundle
having count(ti.bundle) >=5; /* or >= 3 depending on which you're using now*/
Improve this answer

1 Comment

This returns only 1 record, I already tried this... I just can't wrap my head around it. I need to return records that have 3 or more 5 bundles..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.