We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

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%' 


8.0k

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

One More Question :

$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%'

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



10.5k
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:

https://www.w3schools.com/sql/sql_having.asp

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



8.0k

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.)



665

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.course_id = Courses.id') ->join('Vendors', 'Exams.vendor_id = 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.