Guest david gill Posted February 24, 2009 Report Share Posted February 24, 2009 Hello everyone, I have a problem, i'm trying to pair up all registered houses in the same postcode from one table. So like in one table i've got like 6 different postcodes and i need to pair them up. The answer should be like this. Example: HNo PC HNo PC ____________________________________ 1 LE1 2hg | 1 LE1 2hg 1 LE1 2hg | 2 LE1 2hg 1 LE1 2hg | 3 LE1 2hgetc......HNO = house numberPC = Post codeI've tried the cartesian product but it gives me a return of 1640 rows? :S Can someone help me or give me a clue as to which statement or command i should use Link to comment Share on other sites More sharing options...
Stefano Posted March 24, 2009 Report Share Posted March 24, 2009 select ahno, apc, bhno, bpcfrom( select a.hno as ahno, a.pc as apc, b.hno as bhno, b.pc as bpc from ( select rowno, hno, pc from yourtable) as a, ( select rowno, hno, pc from yourtable) as b where a.rowno mod 2 = 0 and b.rown mod 2 = 1 and (a.rowno-1)=b.rowno) I haven't debuged it let me know if helpsregardsStefano De Boni Link to comment Share on other sites More sharing options...
Stefano Posted March 24, 2009 Report Share Posted March 24, 2009 select ahno, apc, bhno, bpcfrom( select a.hno as ahno, a.pc as apc, b.hno as bhno, b.pc as bpc from ( select rowno, hno, pc from yourtable) as a, ( select rowno, hno, pc from yourtable) as b where a.rowno mod 2 = 0 and b.rown mod 2 = 1 and (a.rowno-1)=b.rowno) I haven't debuged it let me know if helpsregardsStefano De Boni Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now