?
Solved

Table Alias question

Posted on 2007-10-16
8
Medium Priority
?
1,522 Views
Last Modified: 2013-12-13
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?
0
Comment
Question by:lvollmer
  • 3
  • 3
7 Comments
 
LVL 48

Expert Comment

by:hernst42
ID: 20087027
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
0
 

Author Comment

by:lvollmer
ID: 20087123
I had to add two extra paramenters - I get this error


<b>Could not run query</b> 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%')

 <b>Here's the error message:</b><i> MySQL [1052]: Column 'VenueTypeName' in where clause is ambiguous</i>

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

Author Comment

by:lvollmer
ID: 20087307
when i use AND (vt1.VenueTypeName LIKE '%Barbecue%')


Column 'VenueTypeName' in ON clause is ambiguous

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:hernst42
ID: 20087329
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?
0
 
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 1000 total points
ID: 20087332
You are not using the aliases properly.  When you have identically named fields in different tables, you need to tell MySQL from which table it should use a particular field.  For example, say I have table1 and table2, and they both have a field called `id`:

SELECT id FROM table1,table2

That query will return an error because MySQL does not know which table's id field to pull.  You tell it like this:

SELECT table1.id FROM table1,table2

When using aliases:

SELECT a.id FROM table1 a,table2 b

Also examine your JOINs.  The second join looks incorrect (check the aliases) and you are lacking table identification in several sections.
0
 

Author Comment

by:lvollmer
ID: 20087350
>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
0
 
LVL 48

Accepted Solution

by:
hernst42 earned 1000 total points
ID: 20087367
> The VenueTypeName is in the table venuetype
As you use the table venuetype twice which must match or doesn't it matter as vt1.venuetype will always be the same as vt2.venuetype ? In this case pick vt1 or vt2 as prefix. Else choose the correct one.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses
Course of the Month16 days, 23 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question