SQL Query Modification Request

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 21, 2018 at 7:01 am

  #2017060

  OK, I've got what you're looking for:
  CREATE TABLE tableA(
 A VARCHAR(3) NOT NULL PRIMARY KEY
,B VARCHAR(3) NOT NULL
,C VARCHAR(3) NOT NULL
,D VARCHAR(3) NOT NULL
,E VARCHAR(3) NOT NULL
,F VARCHAR(3) NOT NULL
);
INSERT INTO tableA(A,B,C,D,E,F) VALUES ('xx','bb','dd','TYH','ADF','AD');
INSERT INTO tableA(A,B,C,D,E,F) VALUES ('HJA','FAD','DAD','YUJ','AA','QWE');
INSERT INTO tableA(A,B,C,D,E,F) VALUES ('AD','DT','GYT','UU','ST','SF');
INSERT INTO tableA(A,B,C,D,E,F) VALUES ('rf','gsd','rq','jn','re','AD');

  CREATE TABLE tableB(
 A VARCHAR(2) NOT NULL PRIMARY KEY
,B VARCHAR(3) NOT NULL
,C VARCHAR(3) NOT NULL
,D VARCHAR(3) NOT NULL
,E VARCHAR(3) NOT NULL
,F VARCHAR(3) NOT NULL
);
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('xx','bb','dd','TYH','BIG','UP');
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('AD','ADF','NH','OL','NM','GU');
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('FH','IJJ','GYT','GGT','ST','HDF');
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('tg','ar','afa','ar','af','af');

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 21, 2018 at 7:31 am

  #2017069

  carlton 84646 - Friday, December 21, 2018 7:01 AM

  OK, I've got what you're looking for:
  CREATE TABLE tableA(
   A VARCHAR(3) NOT NULL PRIMARY KEY
  ,B VARCHAR(3) NOT NULL
  ,C VARCHAR(3) NOT NULL
  ,D VARCHAR(3) NOT NULL
  ,E VARCHAR(3) NOT NULL
  ,F VARCHAR(3) NOT NULL
  );
  INSERT INTO tableA(A,B,C,D,E,F) VALUES ('xx','bb','dd','TYH','ADF','AD');
  INSERT INTO tableA(A,B,C,D,E,F) VALUES ('HJA','FAD','DAD','YUJ','AA','QWE');
  INSERT INTO tableA(A,B,C,D,E,F) VALUES ('AD','DT','GYT','UU','ST','SF');
  INSERT INTO tableA(A,B,C,D,E,F) VALUES ('rf','gsd','rq','jn','re','AD');

  CREATE TABLE tableB(
 A VARCHAR(2) NOT NULL PRIMARY KEY
,B VARCHAR(3) NOT NULL
,C VARCHAR(3) NOT NULL
,D VARCHAR(3) NOT NULL
,E VARCHAR(3) NOT NULL
,F VARCHAR(3) NOT NULL
);
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('xx','bb','dd','TYH','BIG','UP');
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('AD','ADF','NH','OL','NM','GU');
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('FH','IJJ','GYT','GGT','ST','HDF');
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('tg','ar','afa','ar','af','af');

  Thanks!

  SELECT x.*
  FROM #tableA a
  INNER JOIN #tableB b
   ON b.A = a.A
   AND b.B = a.B
   AND b.c = a.C
  CROSS APPLY (
  VALUES (0, a.A, a.B, a.C, a.D, a.E, a.F), (1, b.A, b.B, b.C, b.D, b.E, b.F)
  ) x (IsDeleted, A,B,C,D,E,F)

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 21, 2018 at 8:51 am

  #2017080

  ChrisM@Work - Friday, December 21, 2018 7:31 AM

  carlton 84646 - Friday, December 21, 2018 7:01 AM

  OK, I've got what you're looking for:
  CREATE TABLE tableA(
   A VARCHAR(3) NOT NULL PRIMARY KEY
  ,B VARCHAR(3) NOT NULL
  ,C VARCHAR(3) NOT NULL
  ,D VARCHAR(3) NOT NULL
  ,E VARCHAR(3) NOT NULL
  ,F VARCHAR(3) NOT NULL
  );
  INSERT INTO tableA(A,B,C,D,E,F) VALUES ('xx','bb','dd','TYH','ADF','AD');
  INSERT INTO tableA(A,B,C,D,E,F) VALUES ('HJA','FAD','DAD','YUJ','AA','QWE');
  INSERT INTO tableA(A,B,C,D,E,F) VALUES ('AD','DT','GYT','UU','ST','SF');
  INSERT INTO tableA(A,B,C,D,E,F) VALUES ('rf','gsd','rq','jn','re','AD');

  CREATE TABLE tableB(
 A VARCHAR(2) NOT NULL PRIMARY KEY
,B VARCHAR(3) NOT NULL
,C VARCHAR(3) NOT NULL
,D VARCHAR(3) NOT NULL
,E VARCHAR(3) NOT NULL
,F VARCHAR(3) NOT NULL
);
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('xx','bb','dd','TYH','BIG','UP');
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('AD','ADF','NH','OL','NM','GU');
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('FH','IJJ','GYT','GGT','ST','HDF');
INSERT INTO tableB(A,B,C,D,E,F) VALUES ('tg','ar','afa','ar','af','af');

  Thanks!

  SELECT x.*
  FROM #tableA a
  INNER JOIN #tableB b
   ON b.A = a.A
   AND b.B = a.B
   AND b.c = a.C
  CROSS APPLY (
  VALUES (0, a.A, a.B, a.C, a.D, a.E, a.F), (1, b.A, b.B, b.C, b.D, b.E, b.F)
  ) x (IsDeleted, A,B,C,D,E,F)

  That is perfect mate. Really, really appreciate it. 
  I will be submitting another question with a very similar query that someone helped me with that I will like to be modified. I hope you're able to help me.

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  December 21, 2018 at 9:08 am

  #2017087

  carlton 84646 - Friday, December 21, 2018 8:51 AM

  That is perfect mate. Really, really appreciate it. 
  I will be submitting another question with a very similar query that someone helped me with that I will like to be modified. I hope you're able to help me.

  If not Chris, then someone else will.
  Just take the time to set up the sample data, explain the requirement and provide sample results and you'll find that many people will step in to help.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin