I seem to be sinking deeper and deeper into php when I am sure there
is a clever query solution to my problem.

How can I create a query that will replace the contents of a field in
one table if the fieldname matches the content of a field in another
table?

For example, how could I compare the two tables below and output
'Smith, Jones'?

Table 1 Table 2
-----------------------------------
-------------------------------------
| id period1 period2 | | id period teacher
|
-----------------------------------
-------------------------------------
| 0 Smith Free | | 0 period2 Jones
|
-----------------------------------
-------------------------------------

Many thanks,

Chris

On Sun, 28 Oct 2007 21:51:32 +0100, Chris <matchett123@googlemail.com>
wrote:

> I seem to be sinking deeper and deeper into php when I am sure there
> is a clever query solution to my problem.
>
> How can I create a query that will replace the contents of a field in
> one table if the fieldname matches the content of a field in another
> table?
>
> For example, how could I compare the two tables below and output
> 'Smith, Jones'?
>
> Table 1 Table 2
> -----------------------------------
> -------------------------------------
> | id period1 period2 | | id period teacher
> |
> -----------------------------------
> -------------------------------------
> | 0 Smith Free | | 0 period2 Jones
> |
> -----------------------------------
> -------------------------------------

No simple query solution, unless we write something for every possible
statement. Let's assume we can join in the 'id' column or it will be even
harder still. Direct advice is not the use 'Free' as a column value.
Mister Free won't like it he is assumed to be non-existent. Use a NULL
value.


SELECT
IFNULL(table1.period1,t2_1.teacher) as 'period1',
IFNULL(table1.period1,t2_2.teacher) as 'period2',
IFNULL(table1.period1,t2_3.teacher) as 'period3'
FROM table1
LEFT JOIN table2 t2_1
ON table1.id = t2_1.id
AND t2_1 = 'period1'
LEFT JOIN table2 t2_2
ON table1.id = t2_2.id
AND t2_2 = 'period2'
LEFT JOIN table2 t2_3
ON table1.id = t2_3.id
AND t2_3 = 'period3'

....so for every period there has to be made an extra join.

There's a more simple database normalisation solution though. Why is the
data scattered in two tables? And to keep things flexible, 'periods'
should not be columns, unless you're absolutely a 100% sure. What is the
exact data it represents, and what do you want to be able to do with it?
--
Rik Wasmus

On 28 Oct, 21:33, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Sun, 28 Oct 2007 21:51:32 +0100, Chris <matchett...@googlemail.com>
> wrote:
>
>
>
>
>
> > I seem to be sinking deeper and deeper into php when I am sure there
> > is a clever query solution to my problem.
>
> > How can I create a query that will replace the contents of a field in
> > one table if the fieldname matches the content of a field in another
> > table?
>
> > For example, how could I compare the two tables below and output
> > 'Smith, Jones'?
>
> > Table 1 Table 2
> > -----------------------------------
> > -------------------------------------
> > | id period1 period2 | | id period teacher
> > |
> > -----------------------------------
> > -------------------------------------
> > | 0 Smith Free | | 0 period2 Jones
> > |
> > -----------------------------------
> > -------------------------------------
>
> No simple query solution, unless we write something for every possible
> statement. Let's assume we can join in the 'id' column or it will be even
> harder still. Direct advice is not the use 'Free' as a column value.
> Mister Free won't like it he is assumed to be non-existent. Use a NULL
> value.
>
> SELECT
> IFNULL(table1.period1,t2_1.teacher) as 'period1',
> IFNULL(table1.period1,t2_2.teacher) as 'period2',
> IFNULL(table1.period1,t2_3.teacher) as 'period3'
> FROM table1
> LEFT JOIN table2 t2_1
> ON table1.id = t2_1.id
> AND t2_1 = 'period1'
> LEFT JOIN table2 t2_2
> ON table1.id = t2_2.id
> AND t2_2 = 'period2'
> LEFT JOIN table2 t2_3
> ON table1.id = t2_3.id
> AND t2_3 = 'period3'
>
> ...so for every period there has to be made an extra join.
>
> There's a more simple database normalisation solution though. Why is the
> data scattered in two tables? And to keep things flexible, 'periods'
> should not be columns, unless you're absolutely a 100% sure. What is the
> exact data it represents, and what do you want to be able to do with it?
> --
> Rik Wasmus- Hide quoted text -
>
> - Show quoted text -

Rik,

Thanks for your .

I set up a room booking system for the college I work in several years
ago and it is still going strong...my plan is to develop it.

At the moment all the data for one room is in it's own table with the
fields weekno (key), weekbegin and p1, p2, p3 -> p40 for each of the
40 periods. I display the week's timetable (based on the weekno)
showing the blanks as free periods.

My problem is that it is not very scalable. I have to manually upload
a csv file to fill the corresponding table with it's timetable for the
52/53 weeks of the year at the beginning of each academic year. I was
experimenting with using another table to insert new bookings so I
could have one table with a row for each timetable and then test the
content of the other 'bookings' table to see if there are any bookings
for periods of that week.

I'm sure this is clear as mud I appreciate your comments and look
forward to any more advice you might have as I'm sure you can tell I
am a MySQL hack.

Chris

On 29 Oct, 09:06, Chris <matchett...@googlemail.com> wrote:
> On 28 Oct, 21:33, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>
>
>
>
>
> > On Sun, 28 Oct 2007 21:51:32 +0100, Chris <matchett...@googlemail.com>
> > wrote:
>
> > > I seem to be sinking deeper and deeper into php when I am sure there
> > > is a clever query solution to my problem.
>
> > > How can I create a query that will replace the contents of a field in
> > > one table if the fieldname matches the content of a field in another
> > > table?
>
> > > For example, how could I compare the two tables below and output
> > > 'Smith, Jones'?
>
> > > Table 1 Table 2
> > > -----------------------------------
> > > -------------------------------------
> > > | id period1 period2 | | id period teacher
> > > |
> > > -----------------------------------
> > > -------------------------------------
> > > | 0 Smith Free | | 0 period2 Jones
> > > |
> > > -----------------------------------
> > > -------------------------------------
>
> > No simple query solution, unless we write something for every possible
> > statement. Let's assume we can join in the 'id' column or it will be even
> > harder still. Direct advice is not the use 'Free' as a column value.
> > Mister Free won't like it he is assumed to be non-existent. Use a NULL
> > value.
>
> > SELECT
> > IFNULL(table1.period1,t2_1.teacher) as 'period1',
> > IFNULL(table1.period1,t2_2.teacher) as 'period2',
> > IFNULL(table1.period1,t2_3.teacher) as 'period3'
> > FROM table1
> > LEFT JOIN table2 t2_1
> > ON table1.id = t2_1.id
> > AND t2_1 = 'period1'
> > LEFT JOIN table2 t2_2
> > ON table1.id = t2_2.id
> > AND t2_2 = 'period2'
> > LEFT JOIN table2 t2_3
> > ON table1.id = t2_3.id
> > AND t2_3 = 'period3'
>
> > ...so for every period there has to be made an extra join.
>
> > There's a more simple database normalisation solution though. Why is the
> > data scattered in two tables? And to keep things flexible, 'periods'
> > should not be columns, unless you're absolutely a 100% sure. What is the
> > exact data it represents, and what do you want to be able to do with it?
> > --
> > Rik Wasmus- Hide quoted text -
>
> > - Show quoted text -
>
> Rik,
>
> Thanks for your .
>
> I set up a room booking system for the college I work in several years
> ago and it is still going strong...my plan is to develop it.
>
> At the moment all the data for one room is in it's own table with the
> fields weekno (key), weekbegin and p1, p2, p3 -> p40 for each of the
> 40 periods. I display the week's timetable (based on the weekno)
> showing the blanks as free periods.
>
> My problem is that it is not very scalable. I have to manually upload
> a csv file to fill the corresponding table with it's timetable for the
> 52/53 weeks of the year at the beginning of each academic year. I was
> experimenting with using another table to insert new bookings so I
> could have one table with a row for each timetable and then test the
> content of the other 'bookings' table to see if there are any bookings
> for periods of that week.
>
> I'm sure this is clear as mud I appreciate your comments and look
> forward to any more advice you might have as I'm sure you can tell I
> am a MySQL hack.
>
> Chris- Hide quoted text -
>
> - Show quoted text -

You don't teach grammar at college do you?
http://groups.google.co.uk/group/alt....no.apostrophe