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...