| Author |
 Topic  |
|
|
lkumar
Starting Member
1 Posts |
 Posted - 10/07/2012 : 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
USA
355 Posts |
Posted - 10/07/2012 : 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 |
Edited by - flamblaster on 10/07/2012 12:55:17 |
 |
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 10/07/2012 : 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 |
|
|
|
flamblaster
Constraint Violating Yak Guru
USA
355 Posts |
Posted - 10/08/2012 : 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.] |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 10/08/2012 : 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 |
|
|
| |
Topic |
|
|
|
Help required in joining
