SEO

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

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?

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

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?

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

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?

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

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

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

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?