CODEWITHSUNDEEP
sqlaggregate-functionssubquery
Chris Wright
Chris Wright

Reputation: 31

SQL query assistance (subselect or aggregate)

We need a SQL query that can span two different tables and essentially needs an aggregate function in the WHERE clause (or a subselect), but I am new to SQL. Essentially, the first table is for the header of the order (only ever one record) which states how many items are on the order. The next table shows a record for each item on the order. The query needs to be able to check if there are multiple records in table 2 when there should only be one.

The logic is:

SELECT * from table2 
JOIN table1 on table1id = table2id
WHERE table1.numberofitems = 1
AND count(table2.products) > 1

Obviously this won't work as there is an aggregate in the where clause. Can anybody suggest anything that may work?

Upvotes: 0

Views: 43

Answers (1)

Vulcronos
Vulcronos

Reputation: 3456

A subselect could work. Or if you just need the primary key of table 2 a group by would be fine as well.

Example with group by:

SELECT table2id from table2 
JOIN table1 on table1id = table2id
WHERE table1.numberofitems = 1
GROUP BY table2.table2id
HAVING count(table2.products) > 1

Example with subselect that returns all columns of table2:

SELECT * from table2 t2
JOIN table1 on table1id = table2id
WHERE table1.numberofitems = 1
AND 1 < (SELECT COUNT(table2.products) FROM table2 t2s 
WHERE t2.table2id = t2s.table2id)

Upvotes: 1

Related Questions