Select
-
Hi, I have the following SQL: select a.codeid, b.codeidalias from tblcodes a left outer join tblcodeslookup b on a.codeid = b.codeid where a.codeid not in (select codeidalias from tblcodeslookup) and it produces the following kind of data codeid codeidalias 1 null 2 3 2 4 2 5 6 7 6 8 6 9 10 null 11 12 11 13 14 null if you can see the pattern. I have been trying for hours to modify it but i cant . Basically, of the codeids that appear more than once, I just want the first appear, ie. 1 null 2 3 6 7 10 null 11 12 14 null Can anyone help? Thanks in advance Eddie
-
Hi, I have the following SQL: select a.codeid, b.codeidalias from tblcodes a left outer join tblcodeslookup b on a.codeid = b.codeid where a.codeid not in (select codeidalias from tblcodeslookup) and it produces the following kind of data codeid codeidalias 1 null 2 3 2 4 2 5 6 7 6 8 6 9 10 null 11 12 11 13 14 null if you can see the pattern. I have been trying for hours to modify it but i cant . Basically, of the codeids that appear more than once, I just want the first appear, ie. 1 null 2 3 6 7 10 null 11 12 14 null Can anyone help? Thanks in advance Eddie
Try this (with a MIN and GROUP BY added):
select a.codeid, MIN(b.codeidalias) as MinCodeIdalias from tblcodes a
left outer join tblcodeslookup b on a.codeid = b.codeid
where a.codeid not in (select codeidalias from tblcodeslookup)
group by a.codeid