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 ?
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%' 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
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:
Thank You For Your suggestion, I think I did something mistake.
@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 ambiguousso the where clause was the one with error, not the selected columns.)
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.
