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
 Help required in joining
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lkumar
Starting Member

1 Posts

Posted - 10/07/2012 :  09:08:25  Show Profile  Reply with Quote
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 - 10/07/2012 :  10:42:09  Show Profile  Reply with Quote
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

Edited by - flamblaster on 10/07/2012 12:55:17
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
264 Posts

Posted - 10/07/2012 :  15:12:49  Show Profile  Reply with Quote
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 - 10/08/2012 :  00:08:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 10/08/2012 :  01:09:50  Show Profile  Reply with Quote
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
  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.06 seconds. Powered By: Snitz Forums 2000