"patch" join?
This is a discussion on "patch" join? within the MySQL Database forums, part of the Database Forums category; Hi all, I'm try to set up a mySQL database as a repository for some experiment data. Most of ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
12-07-2007
|
|||
|
|||
"patch" join?
Hi all, I'm try to set up a mySQL database as a repository for some
experiment data. Most of it will be related to a sort of baseline. I am keen to avoid replicating an entire dataset for what would be fairly minor changes (one or two rows). Is there some kind of self join I could use? Example: id scenario data patchid 1 1 100 0 2 1 100 0 3 1 100 0 4 1 100 0 5 1 100 0 6 1 100 0 7 1 100 0 8 1 100 0 9 1 100 0 10 1 100 0 11 2 110 3 12 2 130 4 13 3 200 7 so to access the baseline I would use SELECT * FROM `table` where 'scenario' = 1 if scenario 2 is identical to the baseline data except that the third value becomes 110 and the fourth value becomes 130 what would be the best way of rendering the table? I have a feeling its some kind of outer self join. Any help is appreciated. Dan 
|
|
12-07-2007
|
|||
|
|||
|
Re: "patch" join?
On 7 Dec, 10:11, Dan Braun <danbra...@gmail.com> wrote:
> Hi all, I'm try to set up a mySQL database as a repository for some > experiment data. Most of it will be related to a sort of baseline. I > am keen to avoid replicating an entire dataset for what would be > fairly minor changes (one or two rows). Is there some kind of self > join I could use? > > Example: > id scenario data patchid > 1 1 100 0 > 2 1 100 0 > 3 1 100 0 > 4 1 100 0 > 5 1 100 0 > 6 1 100 0 > 7 1 100 0 > 8 1 100 0 > 9 1 100 0 > 10 1 100 0 > 11 2 110 3 > 12 2 130 4 > 13 3 200 7 > > so to access the baseline I would use > SELECT * FROM `table` where 'scenario' = 1 > > if scenario 2 is identical to the baseline data except that the third > value becomes 110 and the fourth value becomes 130 what would be the > best way of rendering the table? > > I have a feeling its some kind of outer self join. > > Any help is appreciated. > > Dan Can you post what the output data you would wish to see is?
|
|
12-07-2007
|
|||
|
|||
|
Re: "patch" join?
On Dec 7, 12:04 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> Can you post what the output data you would wish to see is?- Hide quoted text - something like this: (im assuming that the patchid column can store the unique integer that is the primary key of the row that should be replaced, so for the example data: scanario 1 (baseline) id scenario data patchid 1 1 100 0 2 1 100 0 3 1 100 0 4 1 100 0 5 1 100 0 6 1 100 0 7 1 100 0 8 1 100 0 9 1 100 0 10 1 100 0 scenario 2 id scenario data patchid 1 1 100 0 2 1 100 0 11 2 110 3 12 2 130 4 4 1 100 0 5 1 100 0 6 1 100 0 7 1 100 0 8 1 100 0 9 1 100 0 10 1 100 0 scenario 3 id scenario data patchid 1 1 100 0 2 1 100 0 3 1 100 0 4 1 100 0 5 1 100 0 6 1 100 0 13 3 200 7 8 1 100 0 9 1 100 0 10 1 100 0 cheers Dan
|
|
12-07-2007
|
|||
|
|||
|
Re: "patch" join?
On 7 Dec, 12:48, Dan Braun <danbra...@gmail.com> wrote:
> On Dec 7, 12:04 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote: > > > Can you post what the output data you would wish to see is?- Hide quoted text - > > something like this: > (im assuming that the patchid column can store the unique integer that > is the primary key of the row that should be replaced, so for the > example data: > > scanario 1 (baseline) > id scenario data patchid > 1 1 100 0 > 2 1 100 0 > 3 1 100 0 > 4 1 100 0 > 5 1 100 0 > 6 1 100 0 > 7 1 100 0 > 8 1 100 0 > 9 1 100 0 > 10 1 100 0 > > scenario 2 > id scenario data patchid > 1 1 100 0 > 2 1 100 0 > 11 2 110 3 > 12 2 130 4 > 4 1 100 0 > 5 1 100 0 > 6 1 100 0 > 7 1 100 0 > 8 1 100 0 > 9 1 100 0 > 10 1 100 0 > > scenario 3 > id scenario data patchid > 1 1 100 0 > 2 1 100 0 > 3 1 100 0 > 4 1 100 0 > 5 1 100 0 > 6 1 100 0 > 13 3 200 7 > 8 1 100 0 > 9 1 100 0 > 10 1 100 0 > > cheers > > Dan Why does Row 4 appear in scenario 2?
|
|
12-07-2007
|
|||
|
|||
|
Re: "patch" join?
On Dec 7, 1:19 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 7 Dec, 12:48, Dan Braun <danbra...@gmail.com> wrote: > > > > > > > On Dec 7, 12:04 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote: > > > > Can you post what the output data you would wish to see is?- Hide quoted text - > > > something like this: > > (im assuming that the patchid column can store the unique integer that > > is the primary key of the row that should be replaced, so for the > > example data: > > > scanario 1 (baseline) > > id scenario data patchid > > 1 1 100 0 > > 2 1 100 0 > > 3 1 100 0 > > 4 1 100 0 > > 5 1 100 0 > > 6 1 100 0 > > 7 1 100 0 > > 8 1 100 0 > > 9 1 100 0 > > 10 1 100 0 > > > scenario 2 > > id scenario data patchid > > 1 1 100 0 > > 2 1 100 0 > > 11 2 110 3 > > 12 2 130 4 > > 4 1 100 0 > > 5 1 100 0 > > 6 1 100 0 > > 7 1 100 0 > > 8 1 100 0 > > 9 1 100 0 > > 10 1 100 0 > > > scenario 3 > > id scenario data patchid > > 1 1 100 0 > > 2 1 100 0 > > 3 1 100 0 > > 4 1 100 0 > > 5 1 100 0 > > 6 1 100 0 > > 13 3 200 7 > > 8 1 100 0 > > 9 1 100 0 > > 10 1 100 0 > > > cheers > > > Dan > > Why does Row 4 appear in scenario 2?- Hide quoted text - > > - Show quoted text - because I cocked up when I pasted in the scenario 2 entries, oops! yeah, it would be good if scenario 2 looked like: scenario 2 id scenario data patchid 1 1 100 0 2 1 100 0 11 2 110 3 12 2 130 4 5 1 100 0 6 1 100 0 7 1 100 0 8 1 100 0 9 1 100 0 10 1 100 0
|
|
12-07-2007
|
|||
|
|||
|
Re: "patch" join?
On 7 Dec, 13:40, Dan Braun <danbra...@gmail.com> wrote:
> On Dec 7, 1:19 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote: > > > > > On 7 Dec, 12:48, Dan Braun <danbra...@gmail.com> wrote: > > > > On Dec 7, 12:04 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote: > > > > > Can you post what the output data you would wish to see is?- Hide quoted text - > > > > something like this: > > > (im assuming that the patchid column can store the unique integer that > > > is the primary key of the row that should be replaced, so for the > > > example data: > > > > scanario 1 (baseline) > > > id scenario data patchid > > > 1 1 100 0 > > > 2 1 100 0 > > > 3 1 100 0 > > > 4 1 100 0 > > > 5 1 100 0 > > > 6 1 100 0 > > > 7 1 100 0 > > > 8 1 100 0 > > > 9 1 100 0 > > > 10 1 100 0 > > > > scenario 2 > > > id scenario data patchid > > > 1 1 100 0 > > > 2 1 100 0 > > > 11 2 110 3 > > > 12 2 130 4 > > > 4 1 100 0 > > > 5 1 100 0 > > > 6 1 100 0 > > > 7 1 100 0 > > > 8 1 100 0 > > > 9 1 100 0 > > > 10 1 100 0 > > > > scenario 3 > > > id scenario data patchid > > > 1 1 100 0 > > > 2 1 100 0 > > > 3 1 100 0 > > > 4 1 100 0 > > > 5 1 100 0 > > > 6 1 100 0 > > > 13 3 200 7 > > > 8 1 100 0 > > > 9 1 100 0 > > > 10 1 100 0 > > > > cheers > > > > Dan > > > Why does Row 4 appear in scenario 2?- Hide quoted text - > > > - Show quoted text - > > because I cocked up when I pasted in the scenario 2 entries, oops! > yeah, it would be good if scenario 2 looked like: > > scenario 2 > id scenario data patchid > 1 1 100 0 > 2 1 100 0 > 11 2 110 3 > 12 2 130 4 > 5 1 100 0 > 6 1 100 0 > 7 1 100 0 > 8 1 100 0 > 9 1 100 0 > 10 1 100 0 Are the IDs necessary in the output. I.E. would an output like: 1 1 100 0 2 1 100 0 3 2 110 3 4 2 130 4 5 1 100 0 6 1 100 0 7 1 100 0 8 1 100 0 9 1 100 0 10 1 100 0 also be OK?
|
|
12-07-2007
|
|||
|
|||
|
Re: "patch" join?
On 7 Dec, 13:40, Dan Braun <danbra...@gmail.com> wrote:
Here is what you asked for: SELECT COALESCE(s2.id,s1.id) id, COALESCE(s2.scenario,s1.scenario) scenario, COALESCE(s2.data,s1.data) data, COALESCE(s2.patchid,s1.patchid) patchid FROM scenarios s1 LEFT JOIN scenarios s2 ON s1.id = s2.patchid AND s2.scenario = 2 WHERE s1.scenario=1 ORDER BY s1.id and if you'd prefer my alternative SELECT COALESCE(s2.patchid,s1.id) id, COALESCE(s2.scenario,s1.scenario) scenario, COALESCE(s2.data,s1.data) data, COALESCE(s2.patchid,s1.patchid) patchid FROM scenarios s1 LEFT JOIN scenarios s2 ON s1.id = s2.patchid AND s2.scenario = 2 WHERE s1.scenario=1 ORDER BY s1.id
|
|
12-08-2007
|
|||
|
|||
|
Re: "patch" join?
On 7 Dec, 14:44, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 7 Dec, 13:40, Dan Braun <danbra...@gmail.com> wrote: > Here is what you asked for: > > SELECT > COALESCE(s2.id,s1.id) id, > COALESCE(s2.scenario,s1.scenario) scenario, > COALESCE(s2.data,s1.data) data, > COALESCE(s2.patchid,s1.patchid) patchid > FROM scenarios s1 > LEFT JOIN scenarios s2 ON s1.id = s2.patchid AND s2.scenario = 2 > WHERE s1.scenario=1 > ORDER BY s1.id > > and if you'd prefer my alternative > > SELECT > COALESCE(s2.patchid,s1.id) id, > COALESCE(s2.scenario,s1.scenario) scenario, > COALESCE(s2.data,s1.data) data, > COALESCE(s2.patchid,s1.patchid) patchid > FROM scenarios s1 > LEFT JOIN scenarios s2 ON s1.id = s2.patchid AND s2.scenario = 2 > WHERE s1.scenario=1 > ORDER BY s1.id fantastic! cheers, will try it out on monday Dan
|
|
12-10-2007
|
|||
|
|||
|
Re: "patch" join?
On 8 Dec, 20:29, Dan Braun <danbra...@gmail.com> wrote:
> On 7 Dec, 14:44, Captain Paralytic <paul_laut...@yahoo.com> wrote: > > > > > > > On 7 Dec, 13:40, Dan Braun <danbra...@gmail.com> wrote: > > Here is what you asked for: > > > SELECT > > COALESCE(s2.id,s1.id) id, > > COALESCE(s2.scenario,s1.scenario) scenario, > > COALESCE(s2.data,s1.data) data, > > COALESCE(s2.patchid,s1.patchid) patchid > > FROM scenarios s1 > > LEFT JOIN scenarios s2 ON s1.id = s2.patchid AND s2.scenario = 2 > > WHERE s1.scenario=1 > > ORDER BY s1.id > > > and if you'd prefer my alternative > > > SELECT > > COALESCE(s2.patchid,s1.id) id, > > COALESCE(s2.scenario,s1.scenario) scenario, > > COALESCE(s2.data,s1.data) data, > > COALESCE(s2.patchid,s1.patchid) patchid > > FROM scenarios s1 > > LEFT JOIN scenarios s2 ON s1.id = s2.patchid AND s2.scenario = 2 > > WHERE s1.scenario=1 > > ORDER BY s1.id > > fantastic! cheers, will try it out on monday > > Dan- Hide quoted text - > > - Show quoted text - that works perfectly, thanks very much! Dan
|
|
12-10-2007
|
|||
|
|||
|
Re: "patch" join?
On 10 Dec, 12:43, Dan Braun <danbra...@gmail.com> wrote:
> On 8 Dec, 20:29, Dan Braun <danbra...@gmail.com> wrote: > > > > > On 7 Dec, 14:44, Captain Paralytic <paul_laut...@yahoo.com> wrote: > > > > On 7 Dec, 13:40, Dan Braun <danbra...@gmail.com> wrote: > > > Here is what you asked for: > > > > SELECT > > > COALESCE(s2.id,s1.id) id, > > > COALESCE(s2.scenario,s1.scenario) scenario, > > > COALESCE(s2.data,s1.data) data, > > > COALESCE(s2.patchid,s1.patchid) patchid > > > FROM scenarios s1 > > > LEFT JOIN scenarios s2 ON s1.id = s2.patchid AND s2.scenario = 2 > > > WHERE s1.scenario=1 > > > ORDER BY s1.id > > > > and if you'd prefer my alternative > > > > SELECT > > > COALESCE(s2.patchid,s1.id) id, > > > COALESCE(s2.scenario,s1.scenario) scenario, > > > COALESCE(s2.data,s1.data) data, > > > COALESCE(s2.patchid,s1.patchid) patchid > > > FROM scenarios s1 > > > LEFT JOIN scenarios s2 ON s1.id = s2.patchid AND s2.scenario = 2 > > > WHERE s1.scenario=1 > > > ORDER BY s1.id > > > fantastic! cheers, will try it out on monday > > > Dan- Hide quoted text - > > > - Show quoted text - > > that works perfectly, thanks very much! > > Dan Which did you prefer, the first or the second one?
|
Linear Mode
