• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

SQL on Pairs of Records in a Table

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
Kim Neesgaard
Asked:
Kim Neesgaard
  • 10
  • 4
  • 2
  • +3
1 Solution
 
Dale FyeCommented:
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
 
Jim P.Commented:
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
 
Kim NeesgaardAuthor Commented:
Thank you - I will try your suggestions!
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Patrick MatthewsCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
awking00Commented:
select [Order Number], [Order Detail] from table where code = 100
except
select [Order Number], [Order Detail] from table where code = 200
0
 
Kim NeesgaardAuthor Commented:
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
 
Jim P.Commented:
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
 
Kim NeesgaardAuthor Commented:
-> Jim P.
Ok. I have investigated a bit more and the result seems to be completely correct!
0
 
awking00Commented:
>>-> 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
 
Kim NeesgaardAuthor Commented:
-> awking00

select Order number, Order detail from table where code = '100'
EXCEPT
select Order number, Order from table where code = '200'
0
 
awking00Commented:
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
 
Kim NeesgaardAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
awking00Commented:
Can you then post the actual query you submitted?
0
 
Kim NeesgaardAuthor Commented:
-> awking00
Do you have an email I can forward it to?
0
 
Kim NeesgaardAuthor Commented:
-> ScottPletcher
I get this error:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IN'.
0
 
Kim NeesgaardAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
Kim NeesgaardAuthor Commented:
I have just done so!


BR Kim
0
 
Kim NeesgaardAuthor Commented:
-> 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 10
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now