Solved

SQL on Pairs of Records in a Table

Posted on 2014-02-19
21
238 Views
Last Modified: 2014-03-03
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
Comment
Question by:Kim Neesgaard
  • 10
  • 4
  • 2
  • +3
21 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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

by:Jim P.
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

Open in new window

0
 

Author Comment

by:Kim Neesgaard
ID: 39870231
Thank you - I will try your suggestions!
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 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

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
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 31

Expert Comment

by:awking00
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

by:Kim Neesgaard
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
Please see under Jim P.

-> awking00
I get this error:


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

Expert Comment

by:Jim P.
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

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

Expert Comment

by:awking00
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Kim Neesgaard
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 31

Expert Comment

by:awking00
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

by:Kim Neesgaard
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 69

Expert Comment

by:ScottPletcher
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 31

Expert Comment

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

Author Comment

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

Author Comment

by:Kim Neesgaard
ID: 39882090
-> ScottPletcher
I get this error:

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

Author Comment

by:Kim Neesgaard
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 92

Expert Comment

by:Patrick Matthews
ID: 39882891
neesgaard0,

Glad to have helped.  If your question is resolved, please remember to close it by accepting one or more comments as the answer.

Patrick
0
 

Author Comment

by:Kim Neesgaard
ID: 39885286
I have just done so!


BR Kim
0
 

Author Comment

by:Kim Neesgaard
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now