Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SQL on Pairs of Records in a Table

Posted on 2014-02-19
Medium Priority
250 Views
Hi!

I have a large table with the following structure and content - here is an example:

Order Number     Order Detail     Code
4711                       1                        100
4711                       1                        200
4712                        4                       100
4713                        2                       100
4713                        2                        200
4714                        6                        100
4714                         6                       200
4715                         3                       100

I have read that it is possible to perform one SQL involving two records but cannot figure out how to do.

I want to exclude the pairs of order number and detail having code 100 and 200 but do have shown all records that have only code 100 and not code 200: in above example 4712 and 4715. Is that possible?
0
Question by:Kim Neesgaard
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 10
• 4
• 2
• +3

LVL 48

Expert Comment

ID: 39870204
you need a sub-query to select all of the records that contain thee 200 value, and then exclude those that fall into that category.

select * from your table where [order num] not in (select [order num] from yourtable where code = 200)
0

LVL 38

Expert Comment

ID: 39870222
Or you can use a row number to do it.

``````SELECT *
FROM
(SELECT [Order Number], [Order Detail], [Code],
ROW_NUMBER ( )  OVER ( [ PARTITION BY [Order Number] ORDER BY  [Order Detail], [Code]) As Row_num) SubQ
WHERE SubQ.Row_num = 1
``````
0

Author Comment

ID: 39870231
Thank you - I will try your suggestions!
0

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 39870644
Another way:

``````SELECT x.OrderNumber, x.OrderDetail, x.Code
FROM
(SELECT OrderNumber, OrderDetail, Code
FROM SomeTable
WHERE Code = 100) x LEFT JOIN
(SELECT OrderNumber, OrderDetail, Code
FROM SomeTable
WHERE Code = 200) y ON x.OrderNumber = y.OrderNumber
WHERE y.OrderNumber IS NULL
``````
0

LVL 70

Expert Comment

ID: 39870697
If there's only one row per order number for the order numbers to be listed, you can use the query below, which only scans the table once vs. at least two times for other methods:

SELECT
[Order Number],
MAX([Order Detail]) AS [Order Detail]
--,MAX([other column]) AS [other column] --...
FROM dbo.tablename
GROUP BY
[Order Number]
HAVING
MAX(Code) = 100
0

LVL 32

Expert Comment

ID: 39871212
select [Order Number], [Order Detail] from table where code = 100
except
select [Order Number], [Order Detail] from table where code = 200
0

Author Comment

ID: 39876425
Thank you for all suggestions - I have the following comments:

-> fyed
I have tried your suggestion but get to many records returned. Not only code 100 and 200 are in the column - also many other codes exist.

-> Jim P.
Many other codes than 100 and 200 are in the column so I think the specific codes need to be present in the query?

-> matthewspatrick
The returned number of records is pretty much as expected so this is a very relevant proposal to an SQL. However, I have a question: does your proposal take into account that 'pairs' of orders+order details must be searched for?

-> ScottPletcher

-> awking00
I get this error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXCEPT'.
0

LVL 38

Expert Comment

ID: 39876594
My method allows you to just get the top X rows regardless of the Codes on an order.

But if you want to include or exclude for specific codes that would be adding a where clause in the subq.
0

Author Comment

ID: 39876638
-> Jim P.
Ok. I have investigated a bit more and the result seems to be completely correct!
0

LVL 32

Expert Comment

ID: 39876719
>>-> awking00
I get this error:<<
Don't know why. The syntax for using except can be found at this link -
http://technet.microsoft.com/en-us/library/ms188055.aspx
What was the exact query you submitted?
0

Author Comment

ID: 39876764
-> awking00

select Order number, Order detail from table where code = '100'
EXCEPT
select Order number, Order from table where code = '200'
0

LVL 32

Expert Comment

ID: 39876858
You can't use a column name with spaces (e.g. order number) unless you enclose it in brackets and make sure the case sensitivity is correct. Also, what is the data type of code?
0

Author Comment

ID: 39876867
No - and I did not. The actual SQL has no spaces - I have just converted the real and recognisable fields to reflect the example.
0

LVL 70

Expert Comment

ID: 39876971
SELECT tn.*
FROM dbo.tablename tn
INNER JOIN (
SELECT
[Order Number]
FROM dbo.tablename
WHERE
CASE IN (100, 200)
GROUP BY
[Order Number]
HAVING
MAX(CASE WHEN Code = 100 THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN Code = 200 THEN 1 ELSE 0 END) = 0
) AS tn2 ON
tn2.[Order Number] = tn.[Order Number]
0

LVL 32

Expert Comment

ID: 39876995
Can you then post the actual query you submitted?
0

Author Comment

ID: 39882084
-> awking00
Do you have an email I can forward it to?
0

Author Comment

ID: 39882090
-> ScottPletcher
I get this error:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IN'.
0

Author Comment

ID: 39882109
Thank you all for your contributions!! I have investigated the real data and it seems that I have found the solution in matthewspatrick answer.

But again: thank you very much to your all.
0

LVL 93

Expert Comment

ID: 39882891
neesgaard0,

Patrick
0

Author Comment

ID: 39885286
I have just done so!

BR Kim
0

Author Comment

ID: 39900342
-> Patrick Matthews

I 'dare' to put an additional question to you regarding the SQL: when data is extracted from a table, '20.000' is extracted as 20000.000 i.e. 1000 times too high. I have tried to use a ROUND in the SQL but I get an error. Could you suggest something that brings the SQL to extract the right figures?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month12 days, 1 hour left to enroll