Prev: select from the middle
Next: SUM doesnt work
From: John Wright on 22 Jul 2008 09:35
I have a problem. I just inherited a program that is full of holes and bugs
and I need to do some patching. Just to note everyone, I did not set up
this database and I did not code this program, I am stuck with it as is.
That being said, I have to do some checks on a database to make sure pairs
of material are in the same carton.

For example. In carton 12345 pieces 1A and 1B must be in the carton.

The table is set up as follows:

Piece varchar(12)
InspectedByDate Datetime
InspectedBy varchar(20)
CartonNumber varchar(15)
Traveler varchar(8)

So the data should look like the following:

1A 5/5/2008 RR 081064 55132R
1B 5/5/2008 RR 081064 55132R
2A 5/5/2008 RR 081064 55132R
2B 5/5/2008 RR 081064 55132R
....

I can do a quick check to make sure there are an even number in the carton,
what I need help on is matching up the A and B pairs in the table. I though
of grabbing all the "A" pieces then using a sub-query to do a like search
for "B" pieces, but am stuck. Anyone have any suggestions?

John



From: Paddy on 22 Jul 2008 09:44
Select * from

(
Select
Piece ,
InspectedByDate,
InspectedBy,
CartonNumber,
Traveler
where piece like '%A'
)
a
full outer join
(
Select
Piece ,
InspectedByDate,
InspectedBy,
CartonNumber,
Traveler
where piece like '%B'
)
b
on a.InspectedByDate = b.InspectedByDate
and a.InspectedBy = b.InspectedBy
and a.CartonNumber = b.CartonNumber
and a.Traveler = b.Traveler

If you get any nulls on either the left or right hand side that will
highlight a missing record.

you should check the joins as they maybe too restrictive.

Paddy
From: Plamen Ratchev on 22 Jul 2008 10:47
I believe you already posted the same question yesterday and had good
answers:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/3f73500d2957d796?hl=en#

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: John Wright on 22 Jul 2008 12:16
Sorry for the double post, but this my last post did not show up in my
newsreader. I apologize and thank everyone for the help.

John
"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:EBEBDCC7-867B-4A9B-8CF8-D78FC612D6C1(a)microsoft.com...
>I believe you already posted the same question yesterday and had good
>answers:
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/3f73500d2957d796?hl=en#
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com


 | 
Pages: 1
Prev: select from the middle
Next: SUM doesnt work