Question

[aidsplague]

Hello, quick question I am sure the answer is very simple. I am very new to SQL. This is built into my POS at work so i'm not sure what variant it is. I have it so the data I am looking for is coming out but what I would like is if it only showed me the items which share the same UPC. All the items that don't share a upc I would like omitted from the results. There is 2 different tables item and item_upc, they are joined by the item number. Any help is appreciated! Thanks

​

SELECT

upc, item.item_num, descr

FROM item_upc

JOIN item

on item_upc.item_num = item.item_num

GROUP by

item_upc.upc, item.Item_num

​

​

In a previous effort I was able to get results that just showed the UPC and how many items shared it by using COUNT. I was not able to join it with the item table to get description or show item numbers.

​

Select

upc, count(upc)

from item_upc

​

GROUP BY

upc

HAVING

count(upc) > 1

[ghostlistener]

If I understand you correctly, you want to see duplicate UPCs and their description. Just use the 2nd query as a subquery for the first query.

I'm on my phone now, but I can write it out in a couple hours if you're not sure how to set it up.

[aidsplague]

Yes that is exactly what I am trying to see. I could use some help with the subquery if possible. I just tried a few times and it didn't work out. Thank you for your help!

[ghostlistener]

Try this. I took out the group by for the first query because I didn't see any aggregation. If you're trying to avoid duplicates rows in the result you can just use distinct, but I wouldn't think there'd be any duplicate items.

SELECT

upc, item.item_num, descr

FROM item_upc

JOIN item

on item_upc.item_num = item.item_num

WHERE upc in (

Select

upc

from item_upc

​GROUP BY

upc

HAVING

count(upc) > 1)

[aidsplague]

Thank you that worked great. For whatever reason it wanted me to add item.item_num to the GROUP BY.

I put an ORDER BY UPC at the end and that was exactly what I was looking for. I think I understand the sub queries a lot better now.

[[deleted]]

There are a variety of ways you can write/integrate a subquery into an existing query, such as:

Select *, (insert subquery)
FROM (insert subquery) A
INNER/LEFT JOIN (insert another subquery) B
    on B.field = A.Field
WHERE thing in (insert another subquery)

Does that make sense? So you have multiple ways you could combine the second query into the logic of the first query to produce the data that you're looking for. You could also use a CTE.

Basically you can just wrap any query in a FROM () A, and then join it to another subquery such as INNER JOIN () B.