Forum Discussion

AppieMagz's avatar
AppieMagz
Copper Contributor
Aug 30, 2024

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 records returned in the result set.

 

Thank you.

 

- - - - - - - - - - - - - - - - - -

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.Field1
, A.[Field2]
FROM A
JOIN B
ON A.Field1 = B.Field1

  • Javier_Villegas's avatar
    Javier_Villegas
    MVP
    Aug 30, 2024
    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
    • AppieMagz's avatar
      AppieMagz
      Copper Contributor
      Aug 31, 2024

      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.

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Aug 31, 2024

    AppieMagz 

    I’m not sure if this will work, didn't have data to test, you can try SQL below

     

     

     

     

     

    WITH T AS 
(
  SELECT ROW_NUMBER() OVER( ORDER BY Field1, Field2, Field3, Field4, Field5, Field6 ) AS SN
    , * 
  FROM 
    myTable
)
,
A -- Get a list of unique combinations of Field1 and [Field2]
AS 
(
  SELECT Distinct
    MIN(SN) OVER(PARTITION BY Field1, Field2) AS SN,
    Field1,
    Field2
  FROM T
)
, 
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 
T.SN,
  A.Field1
  ,A.Field2
  ,T.Field3
  ,T.Field4
  ,T.Field5
  ,T.Field6
FROM A
INNER JOIN B
  ON A.Field1 = B.Field1
INNER JOIN T
  ON A.SN = T.SN  --If you want rows count from results with 6 fields same as result with 2 fields that shown your script, Use this line and block 2 lines below 
  --ON A.Field1 = T.Field1 --If you want all rows match field1&2 show with 6 fields, Use these 2 lines and block line up 
  --  AND A.Field2 = T.Field2

     

     

     

     

     

Resources