Forum Discussion
Return all fields in results
Hello. This SQL finds all records that the same in Field1 but are different in Field2. It works fine but only returns the Field1 and Field 2 in the results. I would like all 6 Fields of my r...
Hello,
I believe this is the one you need, this will return all columns where it matches
WITH A -- Get a list of unique combinations of Field1 and [Field2]
AS (
SELECT Distinct
Field1
[Field2]
FROM myTable
)
, B -- Get a list of all those Field1 values that have more than one [Field2] associated
AS (
SELECT Field1
FROM A
GROUP BY
Field1
HAVING COUNT(*) > 1
)
SELECT A.*
, B.*
FROM A
JOIN B
ON A.Field1 = B.Field1
I believe this is the one you need, this will return all columns where it matches
WITH A -- Get a list of unique combinations of Field1 and [Field2]
AS (
SELECT Distinct
Field1
[Field2]
FROM myTable
)
, B -- Get a list of all those Field1 values that have more than one [Field2] associated
AS (
SELECT Field1
FROM A
GROUP BY
Field1
HAVING COUNT(*) > 1
)
SELECT A.*
, B.*
FROM A
JOIN B
ON A.Field1 = B.Field1
Javier_Villegas Hello and thank you for that answer. That returns the search-for fields but not the other fields in the record. The record contains Field1, Field2, Field3, Field4, Field5, Field6.
I would like the result to return all those fields.
I appreciate yours and anyone's assistance.
