Hogwilde1 Posted January 27, 2015 Share Posted January 27, 2015 I am wondering why this is not working. I have a table of order line items. each order can have multiple line items with the same transaction ID just different item codes. I am trying to find single transaction ID's with a specific item code. I also tried doing a count (transactionnumber)<2 but could not figure out how to add a where for the item number. So an order for just this item select distinct(transactionnumber) from Orders (I thought this would select any unique transaction number IE only one occurance)where Code='9987' When I run the above and check the transaction numbers it is any transaction with that code not transactions with only that code? I also tried this select * from OrdersWhere (select COUNT(transactionnumber)from Device_Orders) <2 and ItemCode='9987' This returns no records when I know there are single transaction numbers with that item code? Any suggestions would be appreciated Link to comment Share on other sites More sharing options...
justsomeguy Posted January 27, 2015 Share Posted January 27, 2015 When I run the above and check the transaction numbers it is any transaction with that code not transactions with only that code?Correct, distinct returns all values in the column, but it only returns each value once. It will not return duplicates, it only returns the list of distinct values in the column.You need to use count and having:SELECT transactionnumber, COUNT(*) AS num FROM Orders WHERE Code='9987' GROUP BY transactionnumber HAVING COUNT(*) = 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now