NO_CARRIER
asked on
where field B is duplicated and field K is not null (SQL)
I don't even know if this is possible in SQL.
Some records have a duplicate value in Column B (2 records). One of the records will also have a value in Column K. I don't want to see these records.
It this possible?
Some records have a duplicate value in Column B (2 records). One of the records will also have a value in Column K. I don't want to see these records.
It this possible?
better post sample data and identify which records you don't want to see.
ASKER
attached is some sample data.
as you'll notice the second field (Account) is the same for the last two records. However only the second last record has a comment of "PRIMARY PIN SAME AS SPOUSE PIN". This record I don't want to see.
Note that another record also has "PRIMARY PIN SAME AS SPOUSE PIN" as a comment, but the ACCOUNT is not duplicated, therefore I would still like to see this record.
as you'll notice the second field (Account) is the same for the last two records. However only the second last record has a comment of "PRIMARY PIN SAME AS SPOUSE PIN". This record I don't want to see.
Note that another record also has "PRIMARY PIN SAME AS SPOUSE PIN" as a comment, but the ACCOUNT is not duplicated, therefore I would still like to see this record.
Company Account ID PIN Received First Name Last Name Status Joint PIN Spouse Name Spouse PIN Comment
HAL PH399PBL 1P8P93U 9911UU98P I xxx yyy A 9928U9335 xxx yyy
HAL PJ1157AL 1P8PU19 9U298995U I xxx yyy A 9U8911UU5 xxx yyy
HAL PJ5528AL 1P89198 11UU7U359 I xxx yyy A xxx yyy 11UU7U359 PRIMARY PIN SAME AS SPOUSE PIN
HAL PJ1532AL 1P8P915 115789P7U I xxx yyy L 1127U5193 xxx yyy
HAL PJ1533AL 1P8P915 115789P7U I xxx yyy L 1127U5193 xxx yyy
HAL PJ159UAL 1P8P97P 11U3829P9 I xxx yyy A xxx yyy 1215P5U71
HAL PJ1739AL 1P81P81 9P89U2181 I xxx yyy A 93755P259 xxx yyy
HAL PJ18U2AL 1P81187 9379P9311 I xxx yyy L xxx yyy 9379P9311 PRIMARY PIN SAME AS SPOUSE PIN
HAL PJ18U2AL 1P81187 9379P9311 I xxx yyy L xxx yyy 92U231932
ASKER
If you paste the above sample in Excel as TEXT it will delimit properly into each column.
select *
from tableX
where [Account ID] not in(select [Account ID] from TableX as A where tableX.[Spouse PIN] <>null)
ASKER
for some reason that always returns no records.
if I use In instead of Not In (without checking Column K), I get all the records as expected. If the subquery is just Not In (select [Account ID] from temp as A) .. it still doesn't return anything.
if I use In instead of Not In (without checking Column K), I get all the records as expected. If the subquery is just Not In (select [Account ID] from temp as A) .. it still doesn't return anything.
post the sql of the query you are using
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.