asked on

Table Alias question

I am having a problem with this query -

SELECT SQL_CALC_FOUND_ROWS *
FROM venue
LEFT JOIN venuetype ON venuetype.VenueTypeName = VenueTypeName
LEFT JOIN venuetype ON venuetype.VenueTypeID = VenueType
LEFT JOIN vensubtype ON VenueID = VenSubTypeVenueID
LEFT JOIN venuetype AS subvenuetype ON VenSubTypeVenueTypeID = subvenuetype.VenueTypeID
WHERE VenueActive = 1

I get this error

MySQL [1066]: Not unique table/alias: 'venuetype'

I get this error when I add the line

LEFT JOIN venuetype ON venuetype.VenueTypeName = VenueTypeName

I need to get the value of VenueTypeName from the venuetype table. Can anyone tell me what I am doing wrong?

hernst42

try something like:

SELECT SQL_CALC_FOUND_ROWS *
FROM venue
LEFT JOIN venuetype vt1 ON vt1.VenueTypeName = VenueTypeName
LEFT JOIN venuetype vt2 ON vt1.VenueTypeID = vt1.VenueType
LEFT JOIN vensubtype ON VenueID = VenSubTypeVenueID
LEFT JOIN venuetype AS subvenuetype ON VenSubTypeVenueTypeID = subvenuetype.VenueTypeID
WHERE VenueActive = 1

As you use the same table twice mysql don't know which table to qualify by venuetype So giv each table an uniue alias for the statement and everything should work fine

Larry Vollmer

ASKER

I had to add two extra paramenters - I get this error

Could not run query SELECT SQL_CALC_FOUND_ROWS *
FROM venue
LEFT JOIN venuetype vt1 ON vt1.VenueTypeName = VenueTypeName
LEFT JOIN venuetype vt2 ON vt1.VenueTypeID = vt1.VenueType
LEFT JOIN vensubtype ON VenueID = VenSubTypeVenueID
LEFT JOIN venuetype AS subvenuetype ON VenSubTypeVenueTypeID = subvenuetype.VenueTypeID
WHERE VenueActive = 1

10AND (VenueCity = 'Baldwin Place')

AND (VenueTypeName LIKE '%Barbecue%')

Here's the error message: MySQL [1052]: Column 'VenueTypeName' in where clause is ambiguous

when i do venuetype.VenueTypeName , that does not work either

Larry Vollmer

ASKER

when i use AND (vt1.VenueTypeName LIKE '%Barbecue%')

Column 'VenueTypeName' in ON clause is ambiguous

hernst42

To which column VenueTypeName do you referr the one of vt1 or vt2 ?

Best is to give each table an alias and specify the alias in front of all column, so you don't get ambious columns errors?

SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Vollmer

ASKER

>To which column VenueTypeName do you referr the one of vt1 or vt2 ?

>Best is to give each table an alias and specify the alias in front of all column, so you don't get ambious columns errors?

The VenueTypeName is in the table venuetype

ASKER CERTIFIED SOLUTION

hernst42

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial