Messy SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Messy SQL

Author

Topic

Simon13579
Starting Member

10 Posts

Posted - 2008-05-06 : 06:21:39

Hello,

I have a messy (and slow) piece of SQL that I'm sure can be written much more efficiently.

I have a table called parts:


Part(id, stopcode, s_num, a_num, tagnum, description)

The nature of the table is such that it is possible for two entries to share a tag number; when this is the case, it represents an 'in' part and an 'out' part. I would like to run a query which returns the in and out details in one row for parts. The id field is simply an autonumber and is unique for each entry.
Currently, I am pairing parts on their tag number using a join, so I have

Part p1, Part p2

I join them on the tagnum and select p1.s_num AS 's_num in' and p2.s_num AS 's_num out'. I also know how to tell that p1 is definitely an in part and that p2 is definitely an out part by their description, which will always be 'in' and 'out' respectively. So I have:


SELECT p1.s_num as 's_num in' p2.s_num as 's_num out' ... etc
FROM Part p1 JOIN Part p2 ON (p1.tagnum = p2.tagnum AND p1.description = 'in' AND p2.description = 'out')

This is great -- it will pair my in and out parts perfectly.

However, there isn't always an out part for an in part, meaning that I have a table filled with stuff to be paired and not-paired. I would like to display both of these in the result, with null values for any out value when there isn't a pair. So for this, I can do:


SELECT s_num as 's_num in', NULL as 's_num out' ... etc
FROM Part
WHERE description = 'in'

This will return ALL in parts, even the ones where there IS an associated out part for it. Consequently, when I union the two queries, I get an entry for the paired parts twice -- one where it thinks it just shows its in data (with out fields set to null), and one where it shows both the in and out, i.e., the correct result. I don't want the former.

To get round this, I have to use a NOT IN on the second query before I union it with the first. So, the final query looks like this:


SELECT p1.s_num as 's_num in' p2.s_num as 's_num out' ... etc
FROM Part p1 JOIN Part p2 ON (p1.tagnum = p2.tagnum AND p1.description = 'in' AND p2.description = 'out')

UNION

SELECT s_num as 's_num in', NULL as 's_num out' ... etc
FROM Part
WHERE description = 'in' AND NOT IN (
  SELECT p1.id
  FROM Part p1 JOIN Part p2 ON (p1.tagnum = p2.tagnum AND p1.description = 'in' AND p2.description = 'out')
)

As you can see, this is horribly messy and very inefficient.

I am not very competent with SQL (as you can see!), and I feel there must be a nicer way to accomplish what I'm trying to do.

If anyone can help me on this I would appreciate it (and if I haven't explained very well please do say).

I apologise if I have posted this in the wrong forum, too, and would ask that it could be moved accordingly if this is the case.

Thanks,

Simon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 06:29:09

[code]SELECT TagNum,
MAX(CASE WHEN Description = 'In' THEN s_num ELSE '' END),
MAX(CASE WHEN Description = 'Out' THEN s_num ELSE '' END)
FROM Part
GROUP BY TagNum[/code]

E 12°55'05.25"
N 56°04'39.16"