node.js - Mysql triple join error:Not unique table/alias: 'cushbu_mark_user_favorites' -
i have 3 tables in db
1)cushbu_users (id,first_name,last_name)
2)cushbu_art (id,user_id(fk cushbu_user),title,base_price etc...)
-for store user arts
3)cushbu_mark_user_favorites (id,user_id(fk cushbu_user),art_id(fk cushbu_art))
-for marking favourite items
i want fetch the art items of particular user favourited
count of favourites each art (stored in cushbu_mark_usier_favorites
table )
here query
select cushbu_art.id art_id, cushbu_art.title, cushbu_art.base_price, cushbu_art.image_name, concat( cushbu_users.first_name, ' ', cushbu_users.last_name ) artist_name,count(cushbu_mark_user_favorites.art_id) cushbu_art join cushbu_mark_user_favorites on cushbu_mark_user_favorites.art_id = cushbu_art.id join cushbu_users on cushbu_users.id = cushbu_art.artist_id left join cushbu_mark_user_favorites on cushbu_art.id=cushbu_mark_user_favorites.art_id cushbu_mark_user_favorites.user_id = 68
but got not unique table/alias: 'cushbu_mark_user_favorites'
join
statement
left join cushbu_mark_user_favorites on cushbu_art.id=cushbu_mark_user_favorites.art_id
update
select a.id art_id, a.title, a.base_price, a.image_name, concat( c.first_name, ' ', c.last_name ) artist_name,count(b.art_id) cushbu_art join cushbu_mark_user_favorites b on b.art_id = a.id join cushbu_users c on c.id = a.artist_id left join b on a.id=b.art_id b.user_id = 68
try below query.
select cushbu_art.id art_id, cushbu_art.title, cushbu_art.image_name, concat( cushbu_users.first_name, ' ', cushbu_users.last_name ) artist_name , b.favorites_count total_fav cushbu_mark_user_favorites left join cushbu_art on cushbu_art.id=cushbu_mark_user_favorites.art_id left join cushbu_users on cushbu_users.id = cushbu_art.artist_id left join (select art_id,count(*) favorites_count cushbu_mark_user_favorites group art_id) b on b.art_id=cushbu_art.id cushbu_mark_user_favorites.user_id=1 group cushbu_art.id
hope helpful you.
Comments
Post a Comment