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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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