Potential set based solution

Question

Potential set based solution

kangarolf

Hall of Fame

Points: 3432
More actions
May 12, 2021 at 6:59 am

#3882209
Hi all,
I'm a total novice when it comes to thinking about set based queries and something I am trying to achieve might be suitable!
```
CREATE TABLE PEOPLE
[PEOPLEID] [bigint] IDENTITY(10000,1) NOT NULL,
[NAME] [varchar(250)] NOT NULL


CREATE TABLE THING
[THINGID] [bigint] IDENTITY(10000,1) NOT NULL,
[NAME] [varchar(250)] NOT NULL

CREATE TABLE PEOPLETHINGS
[ID] [bigint] IDENTITY(10000,1) NOT NULL,
[PEOPLEID] [bigint] NOT NULL,
[THINGID] [bigint] NOT NULL


INSERT INTO PEOPLE
SELECT 'Mark'
UNION ALL
SELECT 'Mary'

INSERT INTO THING
SELECT 'Someting 1'
UNION ALL 
SELECT 'Something 2'
```
What I want to do is insert multiple people and multiple things into people things without multiple round trips, people cannot be inserted twice for the same thing into the peoplethings table.
If I use IN clauses like WHERE PEOPLEID NOT IN (SELECT PEOPLEID FROM PEOPLETHINGS WHERE PEOPLEID IN (ID,ID,ID) AND THINGID IN (ID,ID,ID) that will exclude any people that are already added to ANY thing in the peoplething table.
I dont really want to iterate through every thing and add the people. Is there a better way rather than iterate in the app and do multiple round trips or farm off to a stored procedure loop after splitting the strings of people and things.
Thanks for any help

Viewing 4 posts - 1 through 3 (of 3 total)

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

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 1

You can use the except operator. Here is an example that is based on your script

CREATE TABLE PEOPLE (
[PEOPLEID] [bigint] IDENTITY(10000,1) NOT NULL,
[NAME] varchar(250) NOT NULL);


CREATE TABLE THING (
[THINGID] [bigint] IDENTITY(10000,1) NOT NULL,
[NAME] varchar(250) NOT NULL);

CREATE TABLE PEOPLETHINGS (
[ID] bigint IDENTITY(10000,1) NOT NULL,
[PEOPLEID] bigint NOT NULL,
[THINGID] bigint NOT NULL);
go


INSERT INTO PEOPLE
SELECT 'Mark'
UNION ALL
SELECT 'Mary'

INSERT INTO THING
SELECT 'Someting 1'
UNION ALL 
SELECT 'Something 2'

--Inserting first record for mark
INSERT INTO PEOPLETHINGS (PEOPLEID,THINGID) values (10000,10000)

--An example of usage of except to insert only record that doesn't exist in the table
INSERT INTO PEOPLETHINGS (PEOPLEID,THINGID)
SELECT 10000, THINGID
FROM THING
EXCEPT
SELECT PEOPLEID, THINGID
FROM PEOPLETHINGS
WHERE PEOPLEID = 10000;

SELECT * FROM PEOPLETHINGS

go
drop table PEOPLE;
drop table thing;
drop table PEOPLETHINGS

Adi

kangarolf Hall of Fame Points: 3432 More actions · Answer 2

Thanks for the reply, I dont think this will help me in this instance as the except operator will be affected by the two sets on in clauses just the same...ie I have to check that multiple people are not in peoplethings connected with multiple things so I have to use an in clause and this means it excludes any PEOPLE/THING tuplets that are connected with any THING, rather than only with that THING in the tuplets.

Sorry if that is as clear as mud or that I have misunderstood the EXCEPT operator.

Thanks

kangarolf Hall of Fame Points: 3432 More actions · Answer 3

I've solved this by using two sub queries and a join on PEOPLE and THINGS and then only pulling out the ones where the right have subquery/table (the existing rows) have NULL values.

It smells a bit so I would still be interested in finding out about set based ways of achieving the same thing

SELECT A.PEOPLEID, A.THINGID, B.PEOPLEID, B.THINGID
FROM
(SELECT PEOPLEID, THINGID FROM PEOPLE CROSS JOIN THINGS WHERE PEOPLEID IN (ID,ID,ID) AND THINGID IN (ID,ID,ID) AS A
LEFT OUTER JOIN
(SELECT PEOPLEID, THINGID FROM PEOPLETHINGS WHERE PEOPLEID IN (ID,ID,ID) AND THINGID IN (ID,ID,ID)) AS B
ON B.PEOPLEID = A.PEOPLEID AND B.THINGID = A.THINGID
WHERE B.PEOPLEID = NULL

thanks

Rolf