Taking data from table depending on 2 other tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Taking data from table depending on 2 other tables

Author

Topic

iNko
Starting Member

19 Posts

Posted - 2012-12-11 : 13:23:27

Hello, for past few hours im trying to make this work but no success..

Table Messages:
Message_id|| Message_name
1 || one
2 || two
3 || three
4 || four

Table Users:
Message_id|| User_name
1 || User1
2 || user2

Table Membership:
Message_id || Group_name
3 || Group1
4 || Group2

I need a code that would display:

I dont know how to explain it, maybe these results of what i need will do the job:
IF my User_name = User1 - it would display Message_name = one.
IF my Group_name = Group2 - it would display Message_name = four.

IF my User_name = User1 OR Group_name = Group1 - it would display Message_name = one AND message_name = three.
IF my User_name = User1 OR Group_name = Group2 - it would display Message_name = one AND message_name = four.

and so on..

What im trying to say, User_name column and Group_name column are linked to Messages table by Message_id column.. and i need to display Message_name data depending on id..

Code from my previous thread that worked for me yesterday but for some reason no longer works..:
SELECT P.Message_name
FROM Messages P
LEFT JOIN Users T1 PSVZ
ON P.Message_id = T1.Message_id
LEFT JOIN Membership T2
ON T2.Message_id = P.Message_id
WHERE T1.User_name = 'User1'
OR T2.Group_name = 'Group1'

this only displays 1 Message_name, where i want it to display 2..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-11 : 13:30:34

If ID's are of the same data type, you could do something like the following

SELECT
	a.*
FROM
	MessagesTable a
	INNER JOIN
	(
		SELECT Message_id FROM UsersTable WHERE user_name = 'user1'
		UNION 
		SELECT Message_id FROM GroupsTable WHERE group_name = 'group2'
	)b ON a.Message_id = b.Message_id

iNko
Starting Member

19 Posts

Posted - 2012-12-11 : 13:43:59

It still displays 1 Message_name..

I want it to display all messages, even if user_name id would match group_name id..
Another example:
Message_id|| Message_name
1 || one

Table Users:
Message_id|| User_name
1 || User1

Table Membership:
Message_id || Group_name
1 || Group1

SELECT
a.Message_name
FROM
MessagesTable a
INNER JOIN
(
SELECT Message_id FROM UsersTable WHERE user_name = 'user1'
UNION
SELECT Message_id FROM #tmp2 WHERE group_name = 'group1'
)b ON a.Message_id = b.Message_id

This would display Message_name only 1 time, where i want it to display 2 times

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-11 : 13:54:57

It gives two rows when I try it - see the code below. If it still does not work for you, can you post the DDL for the tables and sample data like I have done here? That makes it easy for someone to copy your code and test and make changes.

CREATE TABLE #MessagesTable(Message_id INT, Message_name VARCHAR(32));
CREATE TABLE #UsersTable (Message_id INT, [User_name] VARCHAR(32));
CREATE TABLE #MembershipTable (Message_id INT, Group_name VARCHAR(32));

INSERT INTO #MessagesTable VALUES (1,'One'),(2,'Two'),(3,'Three'),(4,'Four');
INSERT INTO #UsersTable VALUES(1,'User1'),(2,'User2');
INSERT INTO #MembershipTable VALUES (3,'Group1'),(4,'Group2');


SELECT
	a.*
FROM
	#MessagesTable a
	INNER JOIN
	(
		SELECT Message_id FROM #UsersTable WHERE user_name = 'user1'
		UNION 
		SELECT Message_id FROM #MembershipTable WHERE group_name = 'group2'
	)b ON a.Message_id = b.Message_id

DROP TABLE #MessagesTable;
DROP TABLE #UsersTable;
DROP TABLE #MembershipTable

iNko
Starting Member

19 Posts

Posted - 2012-12-11 : 14:28:16

quote:
Originally posted by sunitabeck

Sor idk what DDL is, but i think this will help explain what i want:

INSERT INTO #MessagesTable VALUES (1,'One');
INSERT INTO #UsersTable VALUES(1,'User1');
INSERT INTO #MembershipTable VALUES (1,'Group1'),(1,'Group1');

If i insert these values, it would show only 1 data:
'One'
I want it to show 3 times:
'One'
'One'
'One'

I mean i want it to show duplicates

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-11 : 14:57:55

Change UNION to UNION ALL

iNko
Starting Member

19 Posts

Posted - 2012-12-11 : 17:22:58

Thank you it works :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-11 : 19:20:43

Great!

BTW, DDL = Data Definition Language. It refers to the scripts that you would use to create tables in the example that I posted earlier.