Cross compare from within the same table.

• jcobb 20350

  Default port

  Points: 1438

  More actions

  January 7, 2019 at 3:57 pm

  #379006

  I have a list of items with a substitute and a Y/N box to determine that they are old items. (Code to follow.)
  A10 has a sub of B10 and B10 is old. B10 is subbed back to A10, but A10 is marked N as being old.
  A10 also has a sub of C10. The catch is that C10 is not subbed back to A10, but is subbed to B10.
  Here is how the table looks. Question is, How do I find the sub items tagged with a Y, then compare their sub to see if it was the parent item?
  
  I want to look at the first row and see B10 has a Y, now look and make sure B10 has a sub back to A10 (row 2)(don't care about the Y/N). Row 3, A10 has a sub of C10 which is Y, but C10 does not sub back to A10 at all. I need to know this. I'm thinking a LOOP but I'm not too good at that, or a nested query??
  CREATE TABLE jec_sub (item varchar(40), sub varchar(40), old varchar(1))
INSERT INTO jec_sub (item, sub, old)
VALUES ('A10', 'B10', 'Y'),
('B10', 'A10', 'N'),
('A10', 'C10', 'Y'),
('C10', 'B10', 'Y')
SELECT * FROM jec_sub

• drew.allen

  SSC Guru

  Points: 76984

  More actions

  January 7, 2019 at 4:23 pm

  #2018239

  A loop is definitely not necessary (and will perform badly to boot).  Have you tried the sub-query?  My first thought was a LEFT OUTER JOIN.  See if you can figure it out with those hints.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• tb_db_gg

  SSC Rookie

  Points: 42

  More actions

  January 8, 2019 at 7:27 am

  #2018280

  Is this close to what you are looking for? EXISTS can be useful for this kind of comparison.

  
SELECT jec_olds.*
FROM jec_sub AS jec_olds
WHERE EXISTS (
   SELECT 1 FROM jec_sub AS subs_back
   WHERE subs_back.item = jec_olds.sub
   AND subs_back.sub = jec_olds.item
)
AND jec_olds.old = 'Y'

• allinadazework

  SSCarpal Tunnel

  Points: 4506

  More actions

  January 8, 2019 at 8:07 am

  #2018292

  Not 100 percent sure what you're trying to do but if you join the table back to itself then you can identify the substitutes that are "Y" for old and where the sub item substitute is the same as the original item.

  
SELECT *
FROM
    jec_sub
    LEFT JOIN jec_sub tSub ON jec_sub.sub = tsub.item
WHERE
    tsub.old = 'Y'
    AND tsub.sub = dbo.jec_sub.item


• jcobb 20350

  Default port

  Points: 1438

  More actions

  January 8, 2019 at 1:47 pm

  #2018347

  All of these are close, but maybe I didn't explain it correctly. I have a main item and it's substitute. The sub can also be  a main item with a sub listed for it. The Y/N box is just for interchangeable and is only for reporting. I need to look at the main and sub items and make sure they are linked both ways.

  1. A is an item and has a sub of B and the inter box checked
  2. B is an item and has a sub of A
  CREATE TABLE jec_main_sub (main varchar(5), sub varchar(5), inter varchar(1))
INSERT INTO jec_main_sub (main, sub, inter)
VALUES ('A', 'B', 'Y'),
('B', 'A', NULL),
('F', 'R', NULL),
('D', 'Q', 'Y'),
('Q','X', NULL)
  A and B are cross subbed but when B is the main item, the inter box is not checked.
  There's not a sub from R back to F but the inter box is not checked (so I don't even need to see this).
  D has a sub of Q and the inter box is Y, but Q is not linked back to D. (so I don't even need to see this).
  All I care about is that A to B is checked and that there is a B to A so I need to see if it is checked.

  I just need to report the matching items which are cross referenced only and also note if the inter box is checked or not.

• ScottPletcher

  SSC Guru

  Points: 101075

  More actions

  January 8, 2019 at 1:58 pm

  #2018348

  I'm still not 100% sure of your rules, but hopefully if needed you can adjust this to get what you need:

  
SELECT
    jec.main, jec.sub,
    'cross sub entry found, but the inter box is not checked' AS message
FROM dbo.jec_main_sub jec
INNER JOIN dbo.jec_main_sub jec_cross_sub ON
    jec_cross_sub.main = jec.sub AND
    jec_cross_sub.sub = jec.main
WHERE jec.inter = 'Y' AND
     (jec_cross_sub.inter IS NULL OR jec_cross_sub.inter = 'N')
ORDER BY main, sub

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Rob Buecker

  SSChasing Mays

  Points: 639

  More actions

  January 8, 2019 at 2:42 pm

  #2018355

  Sounded like you wanted to know the inter column value of the second set, regardless of value.

  SELECT    jms.main, jms.sub, jms.inter, jms2.main, jms2.sub, jms2.inter
FROM    jec_main_sub AS jms
INNER JOIN jec_main_sub AS jms2
ON        jms.sub = jms2.main
AND        jms.main = jms2.sub
WHERE    jms.inter = 'Y'
;

  This also assumes you are not looking for multi-level connections.  I.e. ('M', 'N', 'Y'), ('N', 'O', NULL), ('O', 'M', NULL)

• jcobb 20350

  Default port

  Points: 1438

  More actions

  January 8, 2019 at 2:54 pm

  #2018358

  I think I can take it from here. All of these are very helpful, now just to move them into a CTE so I can expand the report. Thank you all.