I tried to display the entries in table where the order has more than one article: but it´s not working the way I tried it. Can somebody show me what´s wrong?!
Here´s what I tried:
WHERE (SELECT COUNT(Ordernumber) FROM TableX AS a WHERE a>1);
One option is to use a subquery to identify the order numbers having more than one article, then join this subquery to your original table to obtain the full records for these matching orders.
SELECT t1.* FROM TableX t1 INNER JOIN ( SELECT Ordernumber FROM TableX GROUP BY Ordernumber HAVING COUNT(*) > 1 ) t2 ON t1.Ordernumber = t2.Ordernumber
This query assumes that all articles within a given order are unique. If duplicate articles could occur, and you would not count duplicates, then you can use the following
HAVING clause instead:
HAVING COUNT(DISTINCT article) > 1
SELECT * FROM TableX WHERE Ordernumber IN ( SELECT Ordernumber FROM TableX GROUP BY Ordernumber HAVING COUNT(*) > 1 )