Help with query

Question

Post reply

Help with query

edsmith

Old Hand

Points: 333
More actions
February 5, 2020 at 9:30 pm

#3723446

I need help writing a query
I have 2 table with below structure
Table : RMmaster
RMID | RMname|
Table : Relationship
ID | RelationshipName | RM1 | RM2 | RM3 |
(RM1, RM2, RM3 map to RMID values from RMmaster table.)
I want to write a query to show all the records from RMmaster table which do not exist in RM1 or RM2 or RM3 fields of the Relationship table.
Thanks for your help
ED

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Could you please post create table scripts and some sample data and expected output?

Since you're new here, welcome! And some reading: How to post a question to get the best help

If you read and follow the instructions in the article, you're much more likely to get a tested answer... and it makes it much easier for people to help you. Enjoy!

Something like

 CREATE TABLE RMmaster (
  rmID INT,
  rmName VARCHAR(15)
 ); -- I didn't add indexes, because I don't totally understand what you're trying to do


 CREATE TABLE Relationship (
  ID INT,
  RelationshipName VARCHAR(15),
  RM1 VARCHAR(15),
  RM2 VARCHAR(15),
  RM3 VARCHAR(15)
 );
 GO

This reply was modified 4 years, 9 months ago by pietlinden.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Here's some test data... You'll need to build the fnTally function that I have a link for in my signature line below unless you already have one of your own.

/*
  DROP TABLE #RMmaster
  DROP TABLE #Relationship
*/
--=====================================================================================================================
--      Create and populate the RMmaster table as a temp table for testing.
--      We''ll populate it with RMIDs from 1 to 9999 and some made up RMnames.
--=====================================================================================================================
--===== Create the test table
 CREATE TABLE #RMmaster
        (
         RMID   INT      NOT NULL PRIMARY KEY CLUSTERED
        ,RMname CHAR(10) NOT NULL
        )
        ;
--===== Populate the test table
 INSERT INTO #RMmaster WITH (TABLOCK)
        (RMID,RMname)
 SELECT  RMID   = t.N
        ,RMname = 'RMName'+RIGHT(t.N+10000,4)
   FROM dbo.fnTally(1,9999) t
  ORDER BY t.N
;
--=====================================================================================================================
--      Create and populate the Relationship table as a temp table for testing.
--      Once the table has been populated, we''ll update it to not include 50, 500, or 5000 for RM values.
--      Heh... and no test is worth a hoot unless you use at least a million rows. :D
--=====================================================================================================================
--===== Create the test table
 CREATE TABLE #Relationship
        (
         ID                 INT         NOT NULL PRIMARY KEY CLUSTERED
        ,RelationshipName   CHAR(22)    NOT NULL
        ,RM1                INT         NULL
        ,RM2                INT         NULL
        ,RM3                INT         NULL
        )
;
--===== Populate the test table (takes about 11 seconds on my laptop)
 INSERT INTO #Relationship WITH (TABLOCK)
        (ID,RelationshipName,RM1,RM2,RM3)
 SELECT  ID                 = t.N
        ,RelationshipName   = 'RelationshipName'+RIGHT(t.N+1000000,6)
        ,RM1                = ABS(CHECKSUM(NEWID())%10000)+1
        ,RM2                = ABS(CHECKSUM(NEWID())%10000)+1
        ,RM3                = ABS(CHECKSUM(NEWID())%10000)+1
   FROM dbo.fnTally(1,1000000) t
  ORDER BY t.N
;
--===== Remove any rows that contain 50, 500, or 5000 in the RMn columns
 DELETE FROM r
   FROM #Relationship r
  CROSS APPLY (VALUES (50),(500),(5000)) ca (N)
  WHERE RM1 = ca.N
     OR RM2 = ca.N
     OR RM3 = ca.N
;

The original problem was to return items from the RMmaster table that aren't in the Relationship table. I figured that while we're there, let's find items in the Relationship table that aren't in RMmaster table.

--=====================================================================================================================
--      Solve the problem:
--      Returns RMIDs from the RMmaster table that aren''t in the Relationship table.
--      As a bit of a bonus, it also returns RMn''s that are in the Relationship table that aren''t in the RMmaster.
--      It runs nearly instantly on my machine.
--=====================================================================================================================
 SELECT  MissingFromMaster       = dr.RMID
        ,MissingFromRelationship = m.RMID
   FROM #RMmaster m
   FULL JOIN
        (--==== This unpivots the 3 RMn columns and uniquifies the result for fast comparison.
         SELECT DISTINCT ca.RMID
           FROM #Relationship r
          CROSS APPLY (VALUES (RM1),(RM2),(RM3)) ca (RMID)
        ) dr
     ON m.RMID = dr.RMID
  WHERE m.RMID  IS NULL 
     OR dr.RMID IS NULL
  ORDER BY ISNULL(m.RMID,dr.RMID)
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 3

This is not as fast as Jeff's method but is a more straight forward way of writing the SQL:

SELECT * 
  FROM #RMmaster m
 WHERE NOT EXISTS(SELECT *
                    FROM #Relationship r                 
                   WHERE m.RMID IN (r.RM1,r.RM2,r.RM3))

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

>> I need help writing a query. I have 2 table with below structure <<

Why did you fail to post DDL?? What you did post has huge problems. Columns are not fields, so you not even gotten the basic term_inology correct. The term_ "master" goes back to the old days of magnetic tape files and has no place in RDBMS. Why do you think that "relationship" is a precise and meaningful data element name? What you have only one of them. Please look up the term_ "repeated group" and First Normal Form (1NF)

CREATE TABLE Foobar

(rm_id CHAR(5) NOT NULL PRIMARY KEY,

rm_name CHAR(10) NOT NULL);

CREATE TABLE Vague_Relationships

(rm_id CHAR(5) NOT NULL

REFERENCES Foobar (rm_id),

vague_relationship_name CHAR(10) NOT NULL

CHECK (vague_relationship_name IN ('rm_1', 'rm_2', 'rm_3' )

PRIMARY KEY (rm_name, vague_relationship_name));

I'm making a lot of guesses about the nature of your data. I'm assuming I have a repeated group because of the way you name them. Why do you think these names are clear and precise? Why do you think a relationship is an attribute? That's a violation of the whole idea of a relational database.

>> I want to write a query to show all the records [sic] from Foobar table which do not exist in rm_1 or rm_2 or rm_3 fields [sic] of the Vague_Relationship table. <<

SELECT rm_id FROM Foobar

EXCEPT

SELECT rm_id FROM Vague_Relationships;

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

jcelko212 32090 wrote:

>>
SELECT rm_id FROM Foobar
EXCEPT
SELECT rm_id FROM Vague_Relationships;

Since I DID provide the DDL and a substantial amount of readily-consumable data for anyone (including you) to test with, you should get off your high horse and test your code to see why you shouldn't have recommended it even if the design of the table was perfect. 😉 And for the description of the table the OP posted, you're code won't actually work.

Also, if you're going to rip the nipples off the OP about how wrong his table is, the very least you could do is demonstrate, with actually working code (although I know how tough that is for you), how to unpivot and normalize the data. It's only a million rows... it should be a piece of cake for you.

And, seriously... get over the rows v.s. records thing... at least the way you do it. While I agree that not all rows are records, records can be single rows in a single table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

Jeff Moden wrote:

jcelko212 32090 wrote:

>>
SELECT rm_id FROM Foobar
EXCEPT
SELECT rm_id FROM Vague_Relationships;

>> Since I DID provide the DML and a substantial amount of data for anyone (including you) to test with, you should get off your high horse <<

Shouldn't the OP be doing that? How do you know you got the data types, keys, etc. right?

>> .. how to unpivot and normalize the data. <<

No, when the schema is bad, I would not kludge it like that. Pivoting is what you do to repair a mess on the fly, so you do not have to learn to do it right.

>> And, seriously... get over the rows v.s. records thing...<<

Are you going to get over YOUR rows vs columns rant on the bottom of your postings? 🙂 I have been teaching SQL for a few decades. My experience is that when people understand to think in an abstraction, escaping the tyranny of sequential physical files, their coding improves drastically.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Yes, the OP should be doing that but you also presumed that the OP knows how to convert the data to a normalized form. In order for the OP to fix the design problem, he definitely needs to know how to convert the data. So, if you're going to presume to advise the OP about bad design, you should also presume that they also don't know how to transfer the existing data to a better designed table and you should show them that as well rather than posting a half-baked solution in the form of an ad hominem attack.

Also, if you look at what you're calling my "rows vs columns rant" at the bottom of my postings, look at how it is presented. It is not worded as an attack on people. It's worded as friendly advice. That's the difference. Your advice is absolutely sound... your method of delivery of the advice is not. And, my so called "rant" supports the idea of avoiding serial/procedural processing.

You have a huge amount to offer, Joe. But people will listen much better if you take on the persona of a sage mentor rather than a wild man yelling at everyone that you attempt to teach. A lot of people say that you're a nice guy in person. How about you let that nice guy do some talking and advising on the forums instead of the cartoon version of the Tasmanian Devil you currently portray. Notice that's not a question. It's a recommendation from a fellow successful teacher.

Yes, I know that people don't have to like a teacher to learn something from them. Unfortunately, you've become a poster child for that.

The cool part is, it's not too late for you to be able to change that. The choice is yours and, IMHO, you made the wrong choice so far.

You say you've been teaching for a few decades. While probably not as long in the tooth at teaching as you, I have been teaching for more than 4 decades myself and have won many awards for doing so. So let's stop the ring-knocking there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)