Creating table in SQL -
could creating query please?
users book product video slots advertise 1 product video. booking table captures these bookings. booking spots allocated user not product or video. #1-ranked video user's #1-ranked product 1 advertised.
bookings table
id user_id position ---- ----- -------- 1 100 1 2 150 2 users table users can have 1-n bookings
id name ---- ---- 100 john smith 150 herby brown products table user can have 1-n products
id name user_id rank --- ---- ------- ---- 1 bike 100 1 2 stereo 100 2 3 computer 100 3 4 notebook 150 1 5 scooter 150 2 videos table product can have 1-n videos
id name product_id user_id rank --- ---- ----------- ------- ---- 1 bike video1 1 100 1 2 bike video2 1 100 2 3 computer video 3 100 3 4 notebook video 4 150 1 5 scooter video 5 150 2 so, query reads, in words: each booking record, #1 ranked video #1-ranked product booked user. order results booking position.
select v.id, v.name, v.rank, v.user_id, u.name uname videos v join users u on u.id = v.user_id join bookings b on u.id = b.user_id v.product_id = (select id products user_id = b.user_id order rank asc limit 1) order b.position asc, v.rank asc some simplified query:
select v.id, v.name, v.user_id, u.name uname, p.name pname videos v join users u on u.id = v.user_id join bookings b on u.id = b.user_id join products p on p.id = v.product_id v.product_id = (select id products user_id = b.user_id , rank = 1) , v.rank = 1 order b.position asc
Comments
Post a Comment