SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Taking data from table depending on 2 other tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/11/2012 :  13:23:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/11/2012 :  13:30:34  Show Profile  Reply with Quote
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

Edited by - sunitabeck on 12/11/2012 13:50:45
Go to Top of Page

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/11/2012 :  13:43:59  Show Profile  Reply with Quote
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

Edited by - iNko on 12/11/2012 13:50:18
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/11/2012 :  13:54:57  Show Profile  Reply with Quote
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
Go to Top of Page

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/11/2012 :  14:28:16  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/11/2012 :  14:57:55  Show Profile  Reply with Quote
Change UNION to UNION ALL
Go to Top of Page

iNko
Starting Member

Lithuania
19 Posts

Posted - 12/11/2012 :  17:22:58  Show Profile  Reply with Quote
Thank you it works :)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/11/2012 :  19:20:43  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000