Combining three Tables - SQL Server Forums

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

Combining three Tables

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-30 : 09:57:37

Hi Friends,

I had Three Tables,

1.Groups Contains Fields
GroupId,Userid
2.GroupUsers Contain Fields
GroupId,Userid
3.Member Contain Fields
UserId,Firstname,businessname,city,state

I will pass the value for Firstname and Groupid

From the firstname, i need to get the userid for that firstname
Now i want to check whether the groupid and userid exists in the Groups table and groupusers table

I tried this query but i am getting wrong answer.Will you help me to modify this query to get the result.

select m.firstname ,m.businessname,m.city,m.state ,m.userid
from member m,groups g ,groupusers u where m.userid=g.ownerid
or u.userid=m.userid and g.groupid=5 and u.groupid=5 and m.firstname like 'k%'

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-10-30 : 10:21:19

[code]
select m.firstname ,m.businessname,m.city,m.state ,m.userid
from member m
INNER JOIN groups g
on g.userid = m.userid
INNER JOIN groupusers u
ON u.userid = m.userid
--where m.userid=g.ownerid <-- What is this field? You do not mention it in your table structure
--and g.groupid=5 and u.groupid=5 and m.firstname like 'k%'
[/code]

This will give you all records that exist in all 3 tables.

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-30 : 10:58:48

Sorry i wrongly mentioned that ,it is userid.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 11:12:09

Post same data and desired output please.

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-30 : 12:11:24

groups Table
GroupId UserId
1 150
2 151
3 150
4 152

groupusers table
Groupid Userid
4 150
3 152
3 151

member Table
UserId FirstName businessname city state
150 kotti Shop chennai TN
151 Sam Engg Bangalore KA
152 Karthick RE Calcutta WB

I am passing value for groupid=4 and firstname like 'K%'

It searches for firstname like 'k%' in member table i will get kotti and karthick
and corressponding userid for kotti and karthick is 150 and 152

i will take that 150 and 152 along with groupid =4 i will check in groups and

groupusers table as (userid=150 and groupid =4 exists in groups table or

groupusers table ) and (userid=152 and groupid =4 exists in groups table or

groupusers table)

If exists i will display the details of userid

Query Result Must be :
kotti Shop chennai TN
Karthick RE Calcutta WB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 12:37:35

[code]SELECT m.FirstName,
m.businessname,
m.city,
m.state
FROM member m
inner join groups g
on g.Userid=m.Userid
inner join groupusers gu
on gu.Userid=m.Userid
where m.FirstName like 'k%'[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 12:40:05

select distinct m.* from
@member m, groups g, groupusers u
where
m.userid = g.userid and g.groupid = 4
or
m.userid = u.userid and u.groupid = 4

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-30 : 12:42:10

Visakh i am getting wrong answer

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-30 : 12:42:19

quote:

Originally posted by visakh16

SELECT DISTINCT m.FirstName,
m.businessname,
m.city,
m.state
FROM member m
inner join groups g
on g.Userid=m.Userid
inner join groupusers gu
on gu.Userid=m.Userid
where m.FirstName like 'k%'
AND (g.GroupID = 4 OR gu.GroupID = 4)

Small edit to include the GroupID (Not sure if teh DISTINCT is needed or not).

ALternativly:

SELECT DISTINCT
	m.FirstName,
	m.businessname,
	m.city,
	m.[state]
FROM 
	@Member AS m
inner join 
	@groups g
	on g.Userid=m.Userid
	AND g.GroupID = 4
inner join 
	@groupusers gu
	on gu.Userid=m.Userid
	AND gu.GroupID = 4
where m.FirstName like 'k%'

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-30 : 12:49:21

select distinct m.* from
member m, groups g, groupusers u
where
m.userid = g.Userid and g.groupid = 4
or
m.userid = u.userid and u.groupid = 4
and firstname like 'p%'

I am getting error when i modified like this

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-10-30 : 12:51:23

But you are not using what has been suggested. Are you actually using SQL Server?!? If so, then use the code suggested and use proper joins.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-30 : 12:51:42

Try this

declare @groups Table (
GroupId int, UserId int )
insert into @groups
select 1, 150 union all
select 2, 151 union all
select 3, 150 union all
select 4, 152

declare @groupusers table (
Groupid int,Userid int )
insert into @groupusers
select 4, 150 union all
select 3, 152 union all
select 3, 151

declare @member Table
( UserId int, FirstName varchar(64), businessname varchar(128), city varchar(128),state varchar(16))
insert into @member
select 150, 'kotti', 'Shop', 'chennai', 'TN' union all
select 151, 'Sam' ,'Engg', 'Bangalore', 'KA' union all
select 152, 'Karthick','RE', 'Calcutta', 'WB'

select distinct m.firstname , m.businessname, m.city, m.state
from @member m
inner join @groupusers gu on gu.userid = m.userid
inner join @groups g on g.userid = m.userid
where m.firstname like 'k%'

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-30 : 12:52:48

SELECT DISTINCT
m.FirstName,
m.businessname,
m.city,
m.[state]
FROM
@Member AS m
inner join
@groups g
on g.Userid=m.Userid
AND g.GroupID = 4
inner join
@groupusers gu
on gu.Userid=m.Userid
AND gu.GroupID = 4
where m.FirstName like 'k%'

When i use this i am getting error

Must declare the table variable "@Member".

Kotti
Posting Yak Master

129 Posts

Posted - 2008-10-30 : 12:57:17

RickD
I need the firstname that starts with letter p ,That is why i modified the query

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 13:03:49

[code]select distinct m.* from
member m, groups g, groupusers u
where
(m.userid = g.Userid and g.groupid = 4
or
m.userid = u.userid and u.groupid = 4)
and firstname like 'p%'[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 13:04:52

quote:
Originally posted by Kotti

SELECT DISTINCT
m.FirstName,
m.businessname,
m.city,
m.[state]
FROM
@Member AS m
inner join
@groups g
on g.Userid=m.Userid
AND g.GroupID = 4
inner join
@groupusers gu
on gu.Userid=m.Userid
AND gu.GroupID = 4
where m.FirstName like 'k%'

When i use this i am getting error

Must declare the table variable "@Member".

Change all the var tables to your table name. so remove all the "@"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources