finding 2 item in one database 1

[jcisco]

I have noticed that there is alot of posts on this subject but i'm unable to find a solution that fits my needs. I hope someone can help me

--background--
I have two tables one that is called tblLocations, and the other is tblLocationsItemsJoin

in my tblLocations I have a pkey of LocationID, and a LocationType (which can either be dry or frozen.)

in tblLocationsItemsJoin I have LocationID, ItemUPC, ItemLotCode (these 3 items make up the pkey).

--TODO--
Now what i need to do is this. check my tblLocationsItemsJoin for any ItemLotCodes that are different and in the same LocationID.

and example of the data I have is
ItemLotCode LocationID
------------------------------------
32791W08 001FK121
40332W06 001FK433
40332W07 001FP430
40341W03 001FP536
40392W01 001FP536
40343W08 001FP536

so my result set would be:
40341W03 001FP536
40392W01 001FP536
40343W08 001FP536

any suggestions?
thanks
john

--------------

 

[hneal98]

I believe the correct syntax is like this:

Select
lij1.LocationID,
lij1.ItemLotCode
from
tblLocationsItemsJoin lij1
inner join tblLocationsItem lij2 on lij1.LocationID = lij2.LocationID
and lij1.ItemLotCode < lij2.ItemLotCode

 

[jcisco]

that worked!

--------------

 

[hneal98]

Awesome.