T
TheRealKernel
Guest
I have the following query that returns the rows where there is duplicates on some field,
SELECT customer_id, first_nm, last_nm, middle_nm, member_id, secret_field
FROM cust_tbl
WHERE secret_field <> '' AND
secret_field IN (SELECT secret_field
FROM cust_tbl
WHERE secret_field <> ''
GROUP BY secret_field
HAVING COUNT(secret_field) >= 2
)`
Which returns the desired results, for example,
customer_id | first_nm | last_nm | middle_nm | member_id | secret_field
1 | jane | doe | | 1005 | secretvalue1
2 | jane | doe | | 1005 | secretvalue1
5 | jane | doe | m | 1060 | secretvalue1
3 | jon | doe | | 1010 | secretvalue2
4 | jon | doe | a | 3030 | secretvalue2
The problem is I want only the rows where 'member_id' isn't duplicated, so the desired return value would be
customer_id | first_nm | last_nm | middle_nm | member_id | secret_field
1 | jane | doe | | 1005 | secretvalue1
5 | jane | doe | m | 1060 | secretvalue1
3 | jon | doe | | 1010 | secretvalue2
4 | jon | doe | a | 3030 | secretvalue2
The duplicated member_id fields are removed. Additionally, there are rows where the the only thing that is different is the customer_id, I need these completely removed.
That is, if all of the rows with the same secret_field values have the same member_ids I would like those to not show at all but if the secret value is the same and there are rows with some of the same member_ids and some different memeber_ids I would want the distinct member_ids in this case.
I could work with a query that will give the distinct member ids where the secret value is the same, but there is more than one row.
Continue reading...
SELECT customer_id, first_nm, last_nm, middle_nm, member_id, secret_field
FROM cust_tbl
WHERE secret_field <> '' AND
secret_field IN (SELECT secret_field
FROM cust_tbl
WHERE secret_field <> ''
GROUP BY secret_field
HAVING COUNT(secret_field) >= 2
)`
Which returns the desired results, for example,
customer_id | first_nm | last_nm | middle_nm | member_id | secret_field
1 | jane | doe | | 1005 | secretvalue1
2 | jane | doe | | 1005 | secretvalue1
5 | jane | doe | m | 1060 | secretvalue1
3 | jon | doe | | 1010 | secretvalue2
4 | jon | doe | a | 3030 | secretvalue2
The problem is I want only the rows where 'member_id' isn't duplicated, so the desired return value would be
customer_id | first_nm | last_nm | middle_nm | member_id | secret_field
1 | jane | doe | | 1005 | secretvalue1
5 | jane | doe | m | 1060 | secretvalue1
3 | jon | doe | | 1010 | secretvalue2
4 | jon | doe | a | 3030 | secretvalue2
The duplicated member_id fields are removed. Additionally, there are rows where the the only thing that is different is the customer_id, I need these completely removed.
That is, if all of the rows with the same secret_field values have the same member_ids I would like those to not show at all but if the secret value is the same and there are rows with some of the same member_ids and some different memeber_ids I would want the distinct member_ids in this case.
I could work with a query that will give the distinct member ids where the secret value is the same, but there is more than one row.
Continue reading...