Potential set based solution

  • 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

  • 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

  • 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

  • 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

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

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