Joining Of 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

Joining Of Tables

Author

Topic

shafi_sunshine
Starting Member

3 Posts

Posted - 2012-09-04 : 13:38:01

Hi Experts

I have three table s1,s2,s3
in each tables i have 3 fields as id,fc,rc,date
here all the three tables are linked with id
I have to join the tables and retrieve that
if fc,rc exist in anyone of the table then i have to retrive fc,rc

In Where condition i have to pass the paramater as date

Finally I have to retrieve if fc,rc exist in any one of the table with in the given date

Any Help would be greatly appreciated

Thanks
Shafi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-04 : 13:47:52

If fc and rc are present in more than one table for the same date, which of the rc and fc's do you want to get?

Also, do all three tables have values for id even if they don't have data for fc and rc?

One of the two queries below:

-- 1
SELECT
	s1.id,
	COALESCE(s1.fc,s2.fc,s3.fc) AS fc,
	COALESCE(s1.rc,s2.rc,s3.rc) AS rc
FROM
	s1
	INNER JOIN s2 ON s1.id = s2.id
	INNER JOIN s3 ON s1.id = s3.id
WHERE
	s1.date = @dateparam;
	
-- 2	
SELECT
	COALESCE(s1.id,s2.id,s3.id) AS id,
	COALESCE(s1.fc,s2.fc,s3.fc) AS fc,
	COALESCE(s1.rc,s2.rc,s3.rc) AS rc
FROM
	s1
	FULL JOIN s2 ON s1.id = s2.id
	FULL JOIN s3 ON s1.id = s3.id
WHERE
	COALESCE(s1.date,s2.date,s3.date) = @dateparam;

shafi_sunshine
Starting Member

3 Posts

Posted - 2012-09-04 : 21:07:56

Hi Sunitabeck

Thanks For ur Reply

If fc and rc are present in more than one table for the same date, i want rc and fc's of all the three tables

No, all three tables will not have values for id if they dont have data for fc and rc

Thanks
Shafi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources