Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous

I run this query :

$phql = ’SELECT Artists.name, Songs.name FROM Artists JOIN Songs ON Artists.id=Songs.id_artists WHERE Artists.name LIKE '%Bill%' $result = $this->modelsManager->query($phql);

Error result : SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous

How To Fix It ?

edited Nov '14

It's confilict between Artists.name and Songs.name. you should alias at least one of them like below:

$phql = ’SELECT Artists.name as artist_name, Songs.name as song_name FROM Artists JOIN Songs ON Artists.id=Songs.id_artists WHERE Artists.name LIKE '%Bill%'


5.6k

Thank you @a6oozar for your replay, It Works You Solved My Problem.

One More Question :

$phql = ’SELECT Artists.name as artistname, Songs.name as songname FROM Artists JOIN Songs ON Artists.id=Songs.id_artists WHERE Artists.name LIKE '%Bill%'

If I change WHERE Artists.name LIKE '%Bill%' TO : HAVING artist_name LIKE '%Bill%'

It Becomes Error

Error Result Syntax error, unexpected token HAVING, near to artist_name LIKE '%Bill%' .......

Any suggestion ? Thank You



7.0k
Accepted
answer
edited Mar '14

Your welcome,

I can't understand why you should change WHERE with HAVING in this query? Did you know the correct usage case of HAVING in SQL?

The SQL HAVING Clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose the condition is TRUE and you have no GROUP BY so I think using HAVING in this query is a mistake and using WHERE is correct.

see these documentations:

http://www.w3schools.com/sql/sql%5having.asp

http://www.techonthenet.com/sql/having.php



5.6k

Thank You For Your suggestion, I think I did something mistake.

edited Mar '14

@a6oozar cemang is right about the HAVING clause, you can't use an alias in the where clause so i guess that's why he's using HAVING, the thing is he should have kept the WHERE Artists.name LIKE '%Bill%' because the query was fine.

(By the way, the error was

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in --> where <-- clause is ambiguous

so the where clause was the one with error, not the selected columns.)



176

I want to run the below code but it shows the error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous

Code:

$query = $obj->modelsManager->createBuilder() ->columns([ 'id' => 'Exams.id', 'name' => 'Exams.name', 'time' => 'Exams.time', 'course' => 'Courses.name', 'description' => 'Courses.description', 'information' => 'Courses.information', 'courseCode' => 'Courses.code', 'logo' => 'Vendors.logo', 'vendor' => 'Vendors.name' ]) ->from('Exams') ->join('Courses', 'Exams.courseid = Courses.id') ->join('Vendors', 'Exams.vendorid = Vendors.id') ->where('Vendors.active = :vactive: AND Courses.active = :cactive: AND Exams.active = :eactive: AND Vendors.name = :vendor: AND Courses.name like :course: AND Exams.name like :exam:', ['vactive' => 1, 'cactive' => 1, 'eactive' => 1, 'vendor' => 'aaa', 'course' => 'bbb', 'exam' => 'ccc']);

$query->getQuery()->execute();

As far I understand I am using table aliases for every name column in where clause. And also use active column in same way but it doesn't have any issue/error.