Hi first post in a new group so if I have chosen the wrong one then
please point me in the right direction.

I am completely new to mysql and am looking to migrate a current
access system to mysql to allow for future expansion to a web based
solution.

The problem I am having is that alot of the tables have duplicated
column names from one table to another so ie master id field in table
1 has the same name as its child id in table 2.

In Access this dosn't cause any issues and everything works lovely.
However if i try to select all records from tables 1 and 2 I get some
screewy results due to the fields not all being unique in mysql.

I have looked through the files and think aliases is the way to
go however it also says that you can not use aliases with a where
clause which is what I think I need in mosdt cases. As on a whole I
will be wanting all records from table 1 but only those records from
table 2 where the matching child ID match a user value, or all records
from both where the ID's match user inputs etc.

I hope that makes sense and someone can a complete novice trying
to learn this stuff.

Thanks
Glyn

On 21 Dec, 09:48, g...@amethystmailing.co.uk wrote:
> Hi first post in a new group so if I have chosen the wrong one then
> please point me in the right direction.
>
> I am completely new to mysql and am looking to migrate a current
> access system to mysql to allow for future expansion to a web based
> solution.
>
> The problem I am having is that alot of the tables have duplicated
> column names from one table to another so ie master id field in table
> 1 has the same name as its child id in table 2.
>
> In Access this dosn't cause any issues and everything works lovely.
> However if i try to select all records from tables 1 and 2 I get some
> screewy results due to the fields not all being unique in mysql.
>
> I have looked through the files and think aliases is the way to
> go however it also says that you can not use aliases with a where
> clause which is what I think I need in mosdt cases. As on a whole I
> will be wanting all records from table 1 but only those records from
> table 2 where the matching child ID match a user value, or all records
> from both where the ID's match user inputs etc.
>
> I hope that makes sense and someone can a complete novice trying
> to learn this stuff.
>
> Thanks
> Glyn

Can you point me to where in the manual it: "says that you can not
use aliases with a where clause"?

I took it from here http://dev.mysql.com/doc/refman/5.0/...ith-alias.html

However reading it again I think it maybe that you cant reference the
alias in the where statement rather than use them at all, but I think
that I would need to do this as the duplicated ID fields would be the
fileds that the condition runs on.

If I were to use aliases in the select and define the standard column
names in the where would this work if both ID fileds were in the where
statement?

Thanks
Glyn

On 21 Dec, 10:36, g...@amethystmailing.co.uk wrote:
> I took it from herehttp://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html
>
> However reading it again I think it maybe that you cant reference the
> alias in the where statement rather than use them at all, but I think
> that I would need to do this as the duplicated ID fields would be the
> fileds that the condition runs on.
>
> If I were to use aliases in the select and define the standard column
> names in the where would this work if both ID fileds were in the where
> statement?
>
> Thanks
> Glyn

You can use table aliases or table qualiiers to do what you want.
For example, suppose you have

table1:
id,
name.

table2
id,
name,
size.

You could say:
SELECT
table1.id id1,
table1.name name1,
table2.id id2,
table2.size,
table2.name name2
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE table2.name = 'Fred'

or with table aliases

SELECT
t1.id id1,
t1.name name1,
t2.id id2,
t2.size,
t2.name name2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE t2.name = 'Fred'

The latter case is how one joins a table to itself.

Cool thanks for that should get me started.

Glyn