Messy SQL

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