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
 Help required in joining

Author  Topic 

lkumar
Starting Member

1 Post
Posted - 2012-10-07 : 09:08:25
Hello friends,

I have searched a lot and tested various approaches but not able to get what I require.

My requirement is:

I have 3 tables
Table 1 (Person table)
S.No. | Food name
1 | John
2 | Henry
3 | Ronaldo


Table 2 (food table)
S.No. | Food name
1 | Burger
2 | Pizza
3 | Sandwich
4 | Kobab

Table 3 (this table contains id and of food as well as person who consumed it, a person can consume multiple foods)
S.No. Person id Food id
1 | 1 | 1
2 | 1 | 3

I have to list all the foods on one side and id of the person on another side, even if a person havent eat that food that food will be listed but in front of that it will be null whereas if a person has ate that food it will be person id.

At once I will request food table only for one person
I want result something like this if fetch for person having id 1 i.e. John:

S.No. | Food name | Person id
1 | Burger | 1
2 | Pizza | NULL
3 | Sandwich | 1
4 | Kobab | NULL

flamblaster
Constraint Violating Yak Guru

384 Posts
Posted - 2012-10-07 : 10:42:09
select T2.[S.No.], T2.[Food Name], T1.[S.No.] as PersonId
from [Table 2] T2
left outer join [Table 3] T3 on T2.[S.No.]=T3.[S.No.]
left outer join [Table 1] T1 on T1.[S.No.]=T3.[S.No.]

(edited, changed order of tables and added left outer join to Table 3
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts
Posted - 2012-10-07 : 15:12:49
Try something like this:

SELECT T2.s_no, T2.food_name,
       CASE WHEN EXISTS(SELECT * FROM Table3 AS T3 WHERE T3.food_id = T2.s_no AND T3.person_id = 1) THEN 1 END AS person_id
  FROM Table2 AS T2;




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts
Posted - 2012-10-08 : 00:08:01
Adjustment to mine in order to get the PersonId from T3:

select T2.[S.No.], T2.[Food Name], T3.[Person Id] as PersonId
from @Food T2
left join @FP T3 on T2.[S.No.]=T3.[S.No.]
left outer join @Person T1 on T1.[S.No.]=T3.[S.No.]
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2012-10-08 : 01:09:50
DECLARE @Person table (SNo int, [Person name] varchar(10))
INSERT INTO @Person
SELECT 1, 'John' UNION ALL
SELECT 2, 'Henry' UNION ALL
SELECT 3, 'Ronaldo'


DECLARE @food table (SNo int, [Food name] varchar(10))
INSERT INTO @food
SELECT 1, 'Burger' UNION ALL
SELECT 2, 'Pizza' UNION ALL
SELECT 3, 'Sandwich' UNION ALL
SELECT 4, 'Kobab'

--Table 3 (this table contains id and of food as well as person who consumed it, a person can consume multiple foods)
DECLARE @Table3 Table (SNo int, [Person id] int, [Food id] int)
INSERT INTO @Table3 VALUES(1, 1, 1),(2, 1, 3)


SELECT f.sno, [food name], t3.[person id]
FROM @Table3 t3
JOIN @Person p ON t3.[Person id] = p.SNo
RIGHT JOIN @food f ON f.SNo = t3.[Food id];

SELECT f.sno, [food name], t3.[person id]
FROM @Table3 t3
RIGHT JOIN @food f ON f.SNo = t3.[Food id];



--
Chandu
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site