SQL Query Modification Request

• carlton 84646

  SSC Eights!

  Points: 902

  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

  SSC Eights!

  Points: 902

  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: 245813

  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.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.