Combining Four 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

Combining Four Tables

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-06-03 : 06:36:49

Hi Friends

I had previously posted this question and but the results went wrong after testing so many times,

Here i had Four tables

Table1 which contains fields HAID,Name

Tables2 which contains fields Bid,HAId,FromDate,ToDate

Tables3 which contains fields Tid,HAId,FromDate,ToDate

Tables4 which contains fields Eid,HAId,FromDate,ToDate

Here when i enters two dates and Search the fromdate and todate of Table1,Table2,Table3 and if my entered dates exists inbetween the fromdate and Todate ,i need to get all the HAID from three tables .
and I need to compare the results with Table1 and I need to get other remaining HAId from Table1

Any one please help me regarding this query

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-03 : 06:40:51

sample data and expected result please.

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-03 : 06:42:35

[code]SELECT t1.haID,
t1.Name
FROM Table1 AS t1
INNER JOIN (
SELECT haID
FROM Table2
WHERE FromDate <= @ToDate
AND ToDate >= @FromDate

UNION

SELECT haID
FROM Table3
WHERE FromDate <= @ToDate
AND ToDate >= @FromDate

UNION

SELECT haID
FROM Table4
WHERE FromDate <= @ToDate
AND ToDate >= @FromDate
) AS d ON d.haID = t1.haID[/code]

E 12°55'05.63"
N 56°04'39.26"

Kotti
Posting Yak Master

129 Posts

Posted - 2009-06-03 : 07:28:07

Hi Peso

Thanks for your reply.

I am getting some HAId ,but i need other than that HAId.

Please help me regarding this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-03 : 07:29:55

Help with what? You need to define your criterias and explain them to us.
We can't guess.

E 12°55'05.63"
N 56°04'39.26"

Kotti
Posting Yak Master

129 Posts

Posted - 2009-06-03 : 07:40:38

Here i am Providing Some Sample Data and expected Result

Tabel1

HaId Name
1 MM
2 KK
3 SS
4 ND

Tabel2

BId HaId FromDate ToDate
1 2 01/6/2009 10/6/2009
2 1 11/6/2009 15/6/2009

Tabel3

TId HaId FromDate ToDate
1 2 15/6/2009 20/6/2009
2 1 16/6/2009 20/6/2009

Tabel4

EId HaId FromDate ToDate
1 2 25/6/2009 30/6/2009
2 1 21/6/2009 30/6/2009

Here when i search using the dates such as 11/6/2009 to 14/6/2009

HAid 2 is free on those days but HAiD 1 is Busy

So i need result as (Includes remaining HAID)

HaId Name
2 KK
3 SS
4 ND

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-03 : 08:40:08

[code]SET DATEFORMAT DMY

DECLARE @Table1 TABLE
(
haID INT,
Name VARCHAR(2)
)

INSERT @Table1
SELECT 1, 'MM' UNION ALL
SELECT 2, 'KK' UNION ALL
SELECT 3, 'SS' UNION ALL
SELECT 4, 'ND'

DECLARE @Table2 TABLE
(
bID INT,
haID INT,
FromDate DATETIME,
ToDate DATETIME
)

INSERT @Table2
SELECT 1, 2, '01/6/2009', '10/6/2009' UNION ALL
SELECT 2, 1, '11/6/2009', '15/6/2009'

DECLARE @Table3 TABLE
(
tID INT,
haID INT,
FromDate DATETIME,
ToDate DATETIME
)

INSERT @Table3
SELECT 1, 2, '15/6/2009', '20/6/2009' UNION ALL
SELECT 2, 1, '16/6/2009', '20/6/2009'

DECLARE @Table4 TABLE
(
eID INT,
haID INT,
FromDate DATETIME,
ToDate DATETIME
)

INSERT @Table4
SELECT 1, 2, '25/6/2009', '30/6/2009' UNION ALL
SELECT 2, 1, '21/6/2009', '30/6/2009'

DECLARE @FromDate DATETIME,
@ToDate DATETIME

SELECT @FromDate = '11/6/2009',
@ToDate = '14/6/2009'

SELECT t1.haID,
t1.Name
FROM @Table1 AS t1
LEFT JOIN (
SELECT haID
FROM @Table2
WHERE FromDate <= @ToDate
AND ToDate >= @FromDate

UNION

SELECT haID
FROM @Table3
WHERE FromDate <= @ToDate
AND ToDate >= @FromDate

UNION

SELECT haID
FROM @Table4
WHERE FromDate <= @ToDate
AND ToDate >= @FromDate
) AS d ON d.haID = t1.haID
WHERE d.haID IS NULL[/code]

E 12°55'05.63"
N 56°04'39.26"

Kotti
Posting Yak Master

129 Posts

Posted - 2009-06-03 : 09:08:06

Hi Peso ,

Thank you for your help.

Now i am getting the Answer.

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources