How to Show Data from two different tables in SQL?

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

I have two different tables shown below , first table is to store working days and second table is to store holidays , both tables are having common field branchID(int).
==================================================

VB

SELECT [Sno]
      ,[CompanyID]
      ,[BranchID]
      ,[Date]
      ,[Day]
      ,[WorkingDay]

  FROM [HRMS].[dbo].[tblWorkingDayList]

=================================================

VB

SELECT  [Sno]
      ,[Occasion]
      ,[Day]
      ,[Date]
      ,[CompanyID]
      ,[BranchID]

  FROM [HRMS].[dbo].[tblHolidayList]

==================================================

From the Above two tables i want to combine them and generate like below
----------------------------------------------------------
BranchID | Date | workingDay | Occasion|
----------------------------------------------------------
1 16/09/2014 yes --
1 17/09/2014 no Holiday
1 18/09/2014 yes --

Please help me
Thanks Advance

Posted 16-Sep-14 1:15am

DevilsCod

Add a Solution

Comments

Neetin_1809 16-Sep-14 7:36am

Can you Tell me Date Type of WorkingDay in [HRMS].[dbo].[tblWorkingDayList]. Its Better You can give the create table script of both the table.?

DevilsCod 16-Sep-14 7:40am

bit

4 solutions

Solution 2

Have a look at "Using Inner Joins" at TechNet[^].

Posted 16-Sep-14 1:22am

CPallini

Comments

DevilsCod 16-Sep-14 7:27am

Select wl.[Date],
wl.WorkingDay,
hl.Date,
hl.Occasion
from
tblWorkingDayList wl
INNER JOIN tblHolidayList hl on hl.BranchID = wl.BranchID
group by wl.Date , wl.WorkingDay, hl.Date ,hl.Occasion

i tried this but data is repeating ??? out of 61 rows , 610 rows are coming .. ?

Solution 4

Hello ,
I am assuming that
1)when there is a working Day you are storing it as 1 in column [WorkingDay] in table [HRMS].[dbo].[tblWorkingDayList].
2)When itis Occassion , you are storing it as zero in in column [WorkingDay] in table [HRMS].[dbo].[tblWorkingDayList].
3)If it is weekly off Day then Entry exists in both table tblWorkingDayList and tblHolidayList

SQL

SELECT  t.Working,
        t.BranchId,
        t.Working,
        t.Occasion
FROM    ( SELECT    wl.[Date],
                    'YES' [Working],
                    BranchId,
                    '' Occasion
          FROM      tblWorkingDayList wl
          WHERE     wl.Date NOT IN ( SELECT DATE
                                     FROM   tblHolidayList
                                     WHERE  wl.BranchId = BranchId )
          UNION ALL
          SELECT    h1.DATE,
                    'NO' [Working],
                    BranchID,
                    Occasion
          FROM      tblHolidayList h1
        ) t

Hope it helps you

Posted 16-Sep-14 3:00am

Neetin_1809

Updated 16-Sep-14 23:34pm

v3

Solution 1

check SQL joins[^]

Posted 16-Sep-14 1:20am

kedar001

Comments

DevilsCod 16-Sep-14 7:23am

Select wl.[Date],
wl.WorkingDay,
hl.Date,
hl.Occasion
from
tblWorkingDayList wl
LEFT OUTER JOIN tblHolidayList hl on hl.BranchID = wl.BranchID

I tried this but data is repeating ... ??

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manikandan MAC · Accepted Answer · 2014-09-16T01:37:00

Solution 3

Hi, Please try this,

SQL

SELECT [Sno],[CompanyID],[BranchID],[Date],[Day],'YES' as [WorkingDay], '--' as [Occasion] FROM tblWorkingDayList
Union all
Select [Sno],[CompanyID],[BranchID],[Date],[Day],'NO' as [WorkingDay], [Occasion] FROM tblHolidayList
Order by date

and update -- Good luck

Posted 16-Sep-14 1:37am

Manikandan MAC

Comments

DevilsCod 16-Sep-14 7:58am

wDate Day WorkingDay Occasion
2014-10-01 00:00:00.000 Wednesday YES --
2014-10-02 00:00:00.000 Thursday YES --
2014-10-03 00:00:00.000 Friday NO Dasara
2014-10-03 00:00:00.000 Friday YES --
2014-10-04 00:00:00.000 Saturday YES --
2014-10-05 00:00:00.000 Sunday YES --
2014-10-06 00:00:00.000 Monday YES --
2014-10-07 00:00:00.000 Tuesday YES --
2014-10-08 00:00:00.000 Wednesday YES --
2014-10-09 00:00:00.000 Thursday YES --
2014-10-10 00:00:00.000 Friday YES --
2014-10-11 00:00:00.000 Saturday YES --
2014-10-12 00:00:00.000 Sunday YES --
2014-10-13 00:00:00.000 Monday YES --
2014-10-14 00:00:00.000 Tuesday YES --
2014-10-15 00:00:00.000 Wednesday YES --
2014-10-16 00:00:00.000 Thursday YES --
2014-10-17 00:00:00.000 Friday YES --
2014-10-18 00:00:00.000 Saturday YES --
2014-10-19 00:00:00.000 Sunday YES --
2014-10-20 00:00:00.000 Monday YES --
2014-10-21 00:00:00.000 Tuesday YES --
2014-10-22 00:00:00.000 Wednesday YES --
2014-10-23 00:00:00.000 Thursday YES --
2014-10-24 00:00:00.000 Friday YES --
2014-10-25 00:00:00.000 Saturday YES --
2014-10-26 00:00:00.000 Sunday YES --
2014-10-27 00:00:00.000 Monday NO Diwali
2014-10-27 00:00:00.000 Monday YES --
2014-10-28 00:00:00.000 Tuesday YES --
2014-10-29 00:00:00.000 Wednesday YES --
2014-10-30 00:00:00.000 Thursday YES --
2014-10-31 00:00:00.000 Friday YES --

holiday rows are repeating at (2014-10-03 and 2014-10-27 ),, how to solve this..?

Manikandan MAC 16-Sep-14 8:04am

I hope record exists in both tables for 2014-10-03, and 2014-10-27.
Please check in those tables. I hope it should be alternated.
Same date will not be appear in both table.

I hope Diwali (2014-10-03) and Dasara (2014-10-27) should not be in tblWorkingDayList.

Please check,