Question 20712 in Mysql Question posted in
The official MySQL documentation can be found here.

how can i get order number containing one item only? – Mysql

For example, I have a table with multiple order number and each order number has multiple itemnum. Then, each order number has same item but some order number has only one item. How can I get them?

How can I get the ordernum with itemnum 123456 only?

sample table

with select ordernum from table where itemnum=123456

It displays me all ordernum with that itemnum but not the ordernum containing only that itemnum

Tags:

2

Answers


  1. 0

    It will be solved by using SQL HAVING clause. HAVING COUNT(ordernum) = 1 AND itemnum = 123456 ensures that only ordernum with a single record has itemnum 123456 Try this:

    SELECT ordernum
FROM table
GROUP BY ordernum
HAVING COUNT(ordernum) = 1 AND itemnum = 123456;
    Login or Signup to reply.
  2. 0

    You can try a nested subquery like this:

    SELECT
    t1.ordernum
FROM
    `test` AS t1,
    (SELECT t2.ordernum, COUNT(t2.ordernum) AS cnt FROM `test` AS t2 GROUP BY t2.ordernum) AS t3
WHERE
    t1.itemnum=123456
    && t1.ordernum=t3.ordernum
    && t3.cnt = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search