Query syntax to find missing combinations

Let's say I have a data set like

FLD1,FLD2,FLD3,FLD4,FLD5
010,1000,2000,3000,4000
020,1000,2000,3000,4000
030,1000,2000,3005,4000
...
998,1000,2000,3000,4000
999,1000,2000,3000,4000

Using the first row as the template I need to loop through thousands of records based on the value of the first field looking for any records that do not contain the same four values of the first record. So in this case it would find row 30. But how do I get the query to loop back on itself to find the mismatches? So I would define the first record as the master
010,1000,2000,3000,4000 so for every record that begins with 010 I need to search through all the other records for every FLD1 value and make sure that every combination for FLD1 010 exists in 020 through 999.


SELECT '010' AS F1,'1000' AS F2,'2000' AS F3,'3000' AS F4,'4000' AS F5 INTO #MyTempTable
SELECT '010' AS F1,'1001' AS F2,'2001' AS F3,'3001' AS F4,'4001' AS F5 INTO #MyTempTable
SELECT '010' AS F1,'1002' AS F2,'2002' AS F3,'3002' AS F4,'4002' AS F5 INTO #MyTempTable
SELECT '010' AS F1,'1003' AS F2,'2003' AS F3,'3003' AS F4,'4003' AS F5 INTO #MyTempTable
SELECT '010' AS F1,'1004' AS F2,'2004' AS F3,'3004' AS F4,'4004' AS F5 INTO #MyTempTable

SELECT '999' AS F1,'1000' AS F2,'2000' AS F3,'3000' AS F4,'4000' AS F5 INTO #MyTempTable
SELECT '999' AS F1,'1001' AS F2,'2001' AS F3,'3001' AS F4,'4001' AS F5 INTO #MyTempTable
SELECT '999' AS F1,'1002' AS F2,'2002' AS F3,'3002' AS F4,'4002' AS F5 INTO #MyTempTable
SELECT '999' AS F1,'1013' AS F2,'2003' AS F3,'3003' AS F4,'4003' AS F5 INTO #MyTempTable
SELECT '999' AS F1,'1004' AS F2,'2004' AS F3,'3004' AS F4,'4004' AS F5 INTO #MyTempTable

In this case 010,1003,2003,3003,4004 would be found
rwheeler23Asked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
If one is to present variations on a theme for every answer (with "fully certified tested answers") then I can contribute too

How about a "cross join?"
select
      table1.*
from table1
cross join (select fld2 x2, fld3 x3, fld4 x4, fld5 x5 
            from table1 where fld1 = 010) cj
where fld2<> x2 or fld3 <> x3 or fld4 <> x4 or fld5 <> x5
;

Open in new window


or, Here's one you might not have considered:
select
      fld1, colname, code, compare
from table1
cross join (select fld2 x2, fld3 x3, fld4 x4, fld5 x5 
            from table1 where fld1 = 010) cj
cross apply (
  values
    (fld2,  x2, 'fld2' ), (fld3,x3, 'fld3'), (fld4,x4, 'fld4'), (fld5,x5, 'fd5')
  ) ca(code,compare, colname)
where code <> compare
;

Open in new window

The output is a little more focused as it specifies which column is different and what the value was compared to
| fld1 | colname | code | compare |
|------|---------|------|---------|
|   30 |    fld4 | 3005 |    3000 |

Open in new window


or, perhaps one could just concatenate the 4 columns and do an NOT IN

SELECT *
FROM table1
WHERE (fld2+fld3+fld4+fld5) NOT IN  (
      SELECT (fld2+fld3+fld4+fld5)  
      FROM table1
      where fld1 = 010
    )
;

Open in new window


or reverse the strings then concatenate ..... & I bet we could even use full outer join and or a set of unions if we set our minds to it but they would be silly.


=========================================
rwheeler23
I apologize if too many options adds to  confusion

Paul
1
 
PortletPaulfreelancerCommented:
What database are you using. (Please always tell us this, "SQL" isn't enough to indicate the many dbms differences)
0
 
PortletPaulfreelancerCommented:
Use a "self join"

select
        t1.*
from table1 t1
left join table1 t2 
      on t1.fld2 = t2.fld2
      and t1.fld3 = t2.fld3
      and t1.fld4 = t2.fld4
      and t1.fld5 = t2.fld5
      and t2.fld1 = '010'      --<< this needs to be the FLD1 "of the first row"
where t2.fld2 IS NULL

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
@Author -

can you please provide the sample input and expected output you need.
0
 
PortletPaulfreelancerCommented:
Both input (selects into temp table) & output given in the question
>>"In this case 010,1003,2003,3003,4004 would be found"


 
  CREATE TABLE Table1
        ([FLD1] int, [FLD2] int, [FLD3] int, [FLD4] int, [FLD5] int)
    ;
        
    INSERT INTO Table1
        ([FLD1], [FLD2], [FLD3], [FLD4], [FLD5])
    VALUES
        (010, 1000, 2000, 3000, 4000),
        (020, 1000, 2000, 3000, 4000),
        (030, 1000, 2000, 3005, 4000),
        (998, 1000, 2000, 3000, 4000),
        (999, 1000, 2000, 3000, 4000)
    ;
    

Open in new window

    select
    *
    from table1 t1
    left join table1 t2 
          on t1.fld2 = t2.fld2
          and t1.fld3 = t2.fld3
          and t1.fld4 = t2.fld4
          and t1.fld5 = t2.fld5
          and t2.fld1 = 010
    where t2.fld2 IS NULL
    

Open in new window


    | FLD1 | FLD2 | FLD3 | FLD4 | FLD5 |   FLD1 |   FLD2 |   FLD3 |   FLD4 |   FLD5 |
    |------|------|------|------|------|--------|--------|--------|--------|--------|
    |   30 | 1000 | 2000 | 3005 | 4000 | (null) | (null) | (null) | (null) | (null) |

Open in new window

DEMO available here: http://sqlfiddle.com/#!6/9819c3/9
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please try this solution using NOT Exists.

Data Generation

CREATE TABLE MyTempTable
(
  [F1] int
, [F2] int
, [F3] int
, [F4] int
, [F5] int
)
GO
    
INSERT INTO MyTempTable ([F1], [F2], [F3], [F4], [F5]) VALUES
(010, 1000, 2000, 3000, 4000),
(020, 1000, 2000, 3000, 4000),
(030, 1000, 2000, 3005, 4000),
(998, 1000, 2000, 3000, 4000),
(999, 1000, 2000, 3000, 4000)
GO

Open in new window


SOLUTION

SELECT *
FROM MyTempTable a
WHERE NOT EXISTS 
(
      SELECT NULL 
      FROM MyTempTable b 
      WHERE 
	      b.f1 = 10
      and a.f3 = b.f3
      and a.f4 = b.f4
      and a.f5 = b.f5
      and a.f2 = b.f2
)

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
F1          F2          F3          F4          F5
----------- ----------- ----------- ----------- -----------
30          1000        2000        3005        4000

(1 row(s) affected)

Open in new window

0
 
rwheeler23Author Commented:
Thanks folks. There can never be too many solutions to a problem. I was born confused so I am used to it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.