-1

I have table users. Each user has his own videos. It is one to many relationship. One user has many videos.

  1. How to write a query to select users.name where video.name is "Video1" or "Video2"?
  2. How to write a query to select users.name, video.name where video.name is "Video1" or "Video2"?

In the second question, we have to merge video.name, because it is one to many relationship.

miclofa
  • 49
  • 1
  • 10

1 Answers1

0

You have not specified if you're querying in SSMS or other platforms. In T-SQL a correct syntax is ( given that primary key is named "user_id" )

Select users.name
from users inner join video on users.user_id = video.user_id 
where video.name in ('Video1', 'Video2')

Select users.name, video.name
from users inner join video on users.user_id = video.user_id 
where video.name in ('Video1', 'Video2')

If instead you need one single row for every user, you should use XML PATH()

select users.name ,
 STUFF(
                 (SELECT ',' + name FROM video where video.user_id = users.user_id FOR XML PATH ('')), 1, 1, ''
               ) as Movies
               from users
group by users.name, users.user_id

How Stuff and FOR XML Path work

Bob Dubke
  • 76
  • 5
  • what if there is more than one video.name? How to select all video.name where video.user_id=users.user_id – miclofa Feb 02 '21 at 13:16
  • With the select i posted you are extracting all the videos for each user. If there are 2 users and eacj of them has 2 videos linked, the query will return 4 rows – Bob Dubke Feb 02 '21 at 13:23
  • How to return two rows - 2 users with 2 videos each? – miclofa Feb 02 '21 at 13:25