force table alias in complex select

[elvira]

Dear Friends,

I have three tables where a field called Registro exists on two of them.

This select Works fine in Access:

"SELECT * FROM PRESTAMOS, LIBROS, LECTORES WHERE PRESTAMOS.REGISTRO = LIBROS.REGISTRO AND PRESTAMOS.REG_USUARI = LECTORES.REG_USUARI" )

Access converts first REIGSTRO from PRESTAMOS Table as PRESTAMOS.REGISTRO.

The same with REGISTRO at LIBROS Table, converted as LIBROS.REGISTRO.


But MySQL does not do such conversión. The resulting Recordset has a field REGISTRO twice.

How can I forcé MySQL to work the same as Access please?:

Thank you very much.

[Enrico Maria Giordano]

Elvira,

Dear Friends,

I have three tables where a field called Registro exists on two of them.

This select Works fine in Access:

"SELECT * FROM PRESTAMOS, LIBROS, LECTORES WHERE PRESTAMOS.REGISTRO = LIBROS.REGISTRO AND PRESTAMOS.REG_USUARI = LECTORES.REG_USUARI" )

Access converts first REIGSTRO from PRESTAMOS Table as PRESTAMOS.REGISTRO.

The same with REGISTRO at LIBROS Table, converted as LIBROS.REGISTRO.


But MySQL does not do such conversión. The resulting Recordset has a field REGISTRO twice.

How can I forcé MySQL to work the same as Access please?:

Thank you very much.

This works at least on Access, MSSQL and MySQL:

Code: Select all  Expand view

"SELECT * FROM PRESTAMOS AS T1, LIBROS AS T2, LECTORES AS T3 WHERE T1.REGISTRO = T2.REGISTRO  AND  T1.REG_USUARI = T3.REG_USUARI"

EMG

[elvira]

Mr. Enrico,

It Works in Access but it still does not work with MySQL.

If I do an xBrowse, I see the same fields with no tx, even on registro field.

Thaks.

[Enrico Maria Giordano]

Elvira,

Mr. Enrico,

It Works in Access but it still does not work with MySQL.

If I do an xBrowse, I see the same fields with no tx, even on registro field.

Thaks.

All I can say is that I'm using that syntax with MySQL without problems. Do you get any error messages?

EMG

[nageswaragunupudi]

The issue raised by Mr Elvira is different.
You will get duplicate column names when you use SELECT * , unless you specify every column and provide unique alias column name for the duplicated column names, in all DBMS expect MSACCESS.

It is highly desirable to avoid duplicate field names by laboriously specifying all field names in the select clause. Sad no DBMS, except MSACESS provides this automatic feature.

Any way, provided you do not make it a habit, you can tolerate the duplicate field names in the recordset and it does not hurt you much if you take some precautions like refer to fields by number and not by name. Eg: oRs:Fields(0):Value and not oRs:Fields( "name" ):Value

Each field contains in its properties collection the real field name in the table and the basetable from which the field is read. Recordset does not get confused.

That brings me to the point that xbrowse does get confused. XBrowse reads and writes fields by names ( eg. oRs:Fields( "name" ):Value := uNewVal )
It is high time I make some suitable changes.

[Enrico Maria Giordano]

NageswaraRao,

The issue raised by Mr Elvira is different.
You will get duplicate column names when you use SELECT * , unless you specify every column and provide unique alias column name for the duplicated column names, in all DBMS expect MSACCESS.

Ok, I didn't understand. You can use

Code: Select all  Expand view

SELECT *, PRESTAMOS.REGISTRO AS PREREG, etc. FROM ...

It is highly desirable to avoid duplicate field names

I agree.

EMG