SQL query, SELECT not in more than 2 records on other table join

Question

so i have this two table: 1. private_information

| account_no | name   | adrress   |
-----------------------------------
| 123        | andrew | alberque  |
| 234        | melissa| california|
| 456        | matthew| newark    |

and then the 2nd table is transaction:

| account_no | transaction_num   |
----------------------------------
| 123        | 989890808         |
| 123        | 234247827         |
| 123        | 123621472         |
| 123        | 457465745         |
| 234        | 435446545         |

so i want to make this select condition:

SELECT * 
From private_information a 
JOIN transaction b ON a.account_no=b.account_no 
WHERE ( <= 2 records in transation table)

the account number should not be more than 2 records of transaction number, so account _no = 123 should not show.

Which are you using, SQL-Server or MySQL? Don't use inappropriate tags. — Barmar, Aug 22 '16 at 04:35

score 2 · Accepted Answer · answered Aug 22 '16 at 04:43

2

Add a join with a subquery that counts the number of transactions for each account.

SELECT p.*, t1.*
FROM private_information AS p
JOIN transaction AS t1 ON p.account_no = t1.account_no
JOIN (SELECT account_no
      FROM transaction
      GROUP BY account_no
      HAVING COUNT(*) <= 2) AS t2 ON p.account_no = t2.account_no

answered Aug 22 '16 at 04:43

Barmar

741,623
53
500
612

this serve me well, thanks. one more thing. i wanted to just show 1 row of record of the latest transaction (desc number transaction) – www.cahyonegoro.com Aug 22 '16 at 07:21
See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 – Barmar Aug 22 '16 at 07:22
Replace `transaction` in the first join with a subquery that finds the most recent transaction, using one of the methods in that question. – Barmar Aug 22 '16 at 07:23

zaidysf · Answer 2 · 2016-08-22T04:50:28.860

1

Here we go

SELECT * 
FROM private_information a JOIN 
    ( SELECT count(account_no) as counted, 
      account_no 
      FROM transaction 
      HAVING counted <= 2 
      GROUP BY account_no ) b 
ON a.account_no = b.account_no

edited Aug 22 '16 at 04:50

answered Aug 22 '16 at 04:25

zaidysf

492
2
14

You can't use `COUNT()` in the `WHERE` clause, it should be in `HAVING`. – Barmar Aug 22 '16 at 04:36
And `< 2` should be `<= 2`. – Barmar Aug 22 '16 at 04:37
You still didn't fix `WHERE` -> `HAVING` – Barmar Aug 22 '16 at 04:44
but i change to `count(account_no)` , better to use where than having – zaidysf Aug 22 '16 at 04:45
You can't use `COUNT()` in the `WHERE` clause. Try it and see. – Barmar Aug 22 '16 at 04:46
BTW, you should use ANSI JOIN syntax, not implicit joins. – Barmar Aug 22 '16 at 04:47

Riad · Answer 3 · 2016-08-22T04:57:53.627

0

    select AA.account_no, AA.name, AA.adrress, CC.transaction_num from 
    (
        select account_no, name, adrress from private_information 
    ) AA inner join
    ( select count(*) as cc, account_no from transation group by account_no having cc <= 2 
    ) BB ON BB.account_no = AA.account_no inner join 
    (
      select account_no, transaction_num from transaction
    ) CC ON CC.account_no = AA.account_no

edited Aug 22 '16 at 04:57

answered Aug 22 '16 at 04:29

Riad

3,822
5
28
39

Use `INNER JOIN`, not `LEFT JOIN`. Otherwise you'll still get a row for the accounts that don't match. – Barmar Aug 22 '16 at 04:38
This doesn't return any of the transaction information, it just returns the number of transactions. – Barmar Aug 22 '16 at 04:45

SQL query, SELECT not in more than 2 records on other table join

3 Answers3