0

So I have a table of values associated with different sessions using a field called sessionid, lets say the table has 3 other fields: itemcode, itemcost, & itemeffect.

What I end up doing, is getting a standard set of items (rows) from another table that contains the defaults for each country, and then store it in this sessiontable with a guid in the sessionid field. The user can then modify the items for their session only.

What I want to do instead is to search for an existing session match that has the same items and respective values, to avoid session repetition as the processing later in the application is expensive (reports etc). A match would return the sessionid, otherwise I will stash the new session and items for repeated use.

I'm struggling to search for a solution on stackExchange (im working off this q/a, I've realised EXIST and UNION won't work because I dont know the sessionid and my users session guid wont match (hence the question to begin with).

I'm thinking something to do with group by sessionid, and check with the worlds longest sql query (or LINQ madness) with a check for each item and item-field, and addtionally a check that no other (unmatched) items exist in matching sessions, and in theory there will only ever be one match or none!

Here's my pseudo-code:

useritems = getCountryDefaultItems();
var existingid =( 
                select grpresult from
                   (select item from db.sessionints
                   group by item.sessionid
                   where item in useritems (ignore sessionid)
                   ) g
                where g.count == useritems.count 
                )
          .first().sessionid;

I've had a go, and I believe it should be FULL OUTER JOIN so I hear, but sqlfiddle says no so I played with left + right + inner joins :/ sqlfiddle

SELECT COUNT(1),a.SessionID
    FROM sessionitems a 
    RIGHT JOIN useritems b   -- should be FULL OUTER JOIN ?
        USING (`SessionID`,`InterventionCode`, `Included`, `IntLvl`, `Uptake Baseline Smokers`, `RelativeEffect`, `Cost`, `IntType`, `Baseline`, `Current`, `Alternative`, `UpPopulation`, `EffPopulation`, `Name`)
       WHERE  (a.`InterventionCode` IS NULL OR b.`InterventionCode` IS NULL) AND  (a.`Included` IS NULL OR b.`Included` IS NULL) AND  (a.`IntLvl` IS NULL OR b.`IntLvl` IS NULL) AND  (a.`Uptake Baseline Smokers` IS NULL OR b.`Uptake Baseline Smokers` IS NULL) AND  (a.`RelativeEffect` IS NULL OR b.`RelativeEffect` IS NULL) AND  (a.`Cost` IS NULL OR b.`Cost` IS NULL) AND  (a.`IntType` IS NULL OR b.`IntType` IS NULL) AND  (a.`Baseline` IS NULL OR b.`Baseline` IS NULL) AND  (a.`Current` IS NULL OR b.`Current` IS NULL) AND  (a.`Alternative` IS NULL OR b.`Alternative` IS NULL) AND  (a.`UpPopulation` IS NULL OR b.`UpPopulation` IS NULL) AND  (a.`EffPopulation` IS NULL OR b.`EffPopulation` IS NULL) AND  (a.`Name` IS NULL OR b.`Name` IS NULL)
GROUP BY a.SessionID;

, but sqlfiddle is having errors doing MSSQL schema today, so I've used mysql for now which doesnt support FULL OUTER JOIN on sqlfiddle: I can get a count of exactly matching rows when there's an exact match, but the id comes back null, and if I remove the COUNT(1) and return * it returns only one row (is this a restriction on results in nested select queries) which might be okay as the session is a match, but I'm worried I have overlooked something and don't appreciate where the query will fall down...

0

It's a mess but I think it works:

http://sqlfiddle.com/#!9/585394/2

SELECT SessionID as ExistingMatch FROM (

    SELECT COUNT(c.ainc) as intcodematch,SUM(c.bnos) as Incorrect, c.SessionID FROM
      (
        SELECT a.SessionID,a.InterventionCode as ainc, b.InterventionCode as binc, IF(b.InterventionCode IS NULL,1,0) as bnos
            FROM sessionitems a 
            left JOIN useritems b 
              ON  a.`InterventionCode` = b.`InterventionCode` AND  a.`Included` = b.`Included` AND  a.`IntLvl` = b.`IntLvl` AND  a.`Uptake Baseline Smokers` = b.`Uptake Baseline Smokers` AND  a.`RelativeEffect` = b.`RelativeEffect` AND  a.`Cost` = b.`Cost` AND  a.`IntType` = b.`IntType` AND  a.`Baseline` = b.`Baseline` AND  a.`Current` = b.`Current` AND  a.`Alternative` = b.`Alternative` AND  a.`UpPopulation` = b.`UpPopulation` AND  a.`EffPopulation` = b.`EffPopulation` 
        GROUP BY a.SessionID,a.InterventionCode

     ) c GROUP BY c.SessionID

) q WHERE q.Incorrect = 0 AND q.intcodematch = (SELECT COUNT(InterventionCode) FROM useritems)

I think I need to add support for nulls in Cost which is a possible situation or cast them to a '0'.

Any thoughts?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.