TSQL something about join table and n - m relations

Question

0.00/5 (No votes)

See more:

Today I am "to be stuck"

I have three Tables, logical this are zwei tables Name and Room with a n to m relation.

-Name (n)

-A join table

-Room (n)

Now i want to get all Names-Rows unique, where ware "working" in room x,y and z

Please help me, today am am a big ideot ;)

User
UserID	Name	Adress	bla...
a	Max	
b	Peter
c	Ben


Join_User_Room
JoinID	UserID	RoomID
d	a	x
e	a	y
f	a	z
g	b	x
h	b	y
i	b	z


Room
RoomID	Name
x	Room1
y	Room2
z	Room3

What I have tried:

Left join and right joins and other crazy sql stuff

Posted 22-Aug-17 0:21am

BoySetsFire

Updated 22-Aug-17 4:06am

Add a Solution

Comments

OriginalGriff 22-Aug-17 6:45am

What exactly do you want to get from that data - it's not very clear from your description.
Perhaps an example of the output you are trying to get would help?

Suvendu Shekhar Giri 22-Aug-17 6:54am

I agree with @OriginalGriff. JOIN is not just for joining to 2 tables rather it's for joining 2 tables to get some meaningful data.

BoySetsFire 22-Aug-17 6:56am

select * from User

join Join_User_Room on UserID	= UserID	
join Room on RoomID = RoomID 

where  RoomID in( X, Y, Z)

now has the result set to many rows. from the join table... but, i want to see onley two rows, the row from user a and the row from user b...

Br,

Benny

OriginalGriff 22-Aug-17 9:06am

So show us an example of the output you are trying to get, and the output you get now.

Bryian Tan 22-Aug-17 10:52am

To continue with the example you provided, you can modify the query to something like below

select DISTINCT u.UserId, u.Name from [User] u
join Join_User_Room jur on u.UserID	= jur.UserID	
join Room r on jur.RoomID = r.RoomID 
where  jur.RoomID in( 'X', 'Y', 'Z')

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-08-22T04:05:00

Solution 1

Try this:

SQL

SELECT u.* FROM [User] u
JOIN (SELECT ju.UserID FROM Join_User_Room ju GROUP BY ju.UserID) jg ON jg.UserID = u.UserID

And when you post code fragments (such as your SQL) please try to ensure they work: that won't for loads of reasons!

Posted 22-Aug-17 4:05am

OriginalGriff