Combining data from two tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

Combining data from two tables

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2008-06-03 : 10:00:02

Hey all

I'm trying to get data from two tables that have the same fields. One is a log table and the other is the actual table. I have the following:

SELECT tbl_LCData.* FROM tbl_LogOfLCDataTable, tbl_LCData
WHERE (((SELECT DATENAME(MONTH, tbl_LCData.PrintDate)) = (SELECT DATENAME(Month,getdate())) AND (SELECT DATENAME(MONTH, tbl_LogOfLCDataTable.PrintDate)) = (SELECT DATENAME(Month,getdate()))))

I am trying to display records that were printed this month. I only get data from tbl_LCData. It doesn't display data from the log table. For example:

Contents of tbl_LCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
18, B, Whatever, 11/04/2007

Contents of tbl_LogOfLCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 01/06/2008

So, I want the query to display:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
17, A, Sample, 01/06/2008

The above shows records printed in the month of June!

Is there a join I need to use??

Many thanks,

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-03 : 10:07:23

You possibly need union here:

Select Id, Forename, Surname, PrintDate 
from tbl_LCData
Where PrintDate >= dateadd(month, datediff(month, 0, getdate()), 0)
and PrintDate <= dateadd(day, -1, dateadd(month, datediff(month, 0, getdate())+1, 0))
union all
Select Id, Forename, Surname, PrintDate 
from tbl_LogOfLCDataTable
Where PrintDate >= dateadd(month, datediff(month, 0, getdate()), 0)
and PrintDate <= dateadd(day, -1, dateadd(month, datediff(month, 0, getdate())+1, 0))

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 10:08:42

Well... Where do I start?

1) Your SELECT list only includes the records from the LCData table due to

SELECT		tbl_LCData.*

2) There is no binding information between the two tables so you will get a cartesian product (all possible combinations) between the two tables

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 10:12:18

Try this

DECLARE	@FromDate DATETIME,
	@ToDate DATETIME

SELECT	@FromDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101'),
	@ToDate = DATEADD(MONTH, 1, @FromDate)

SELECT	ID,
	Forename,
	Surname,
	PrintDate
FROM	tbl_LCData
WHERE	PrintDate >= @FromDate
	AND PrintDate < @ToDate

UNION ALL

SELECT	ID,
	Forename,
	Surname,
	PrintDate
FROM	tbl_LogOfLCData
WHERE	PrintDate >= @FromDate
	AND PrintDate < @ToDate

E 12°55'05.25"
N 56°04'39.16"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-06-03 : 10:14:29

Thx Harsh!! You come to my rescue again :-)

Much appreciated!

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-06-03 : 10:18:25

Thank you Peso!!!

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-03 : 10:20:20

Glad to be of help.

I hope you got the points which Peso raised. Are you from MS Access background? All those parenthesis are also not necessary.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-06-04 : 05:43:51

You can probably tell that I am a learner

Had another question...

I want to convert the date to: DD/MM/YYYY format..When I have the following:

Select Id, Forename, Surname, CONVERT([varchar](10), PrintDate, 103) AS PrintDate
from tbl_LCData
Where PrintDate >= dateadd(month, datediff(month, 0, getdate()), 0)
and PrintDate <= dateadd(day, -1, dateadd(month, datediff(month, 0, getdate())+1, 0))
union all
Select Id, Forename, Surname, PrintDate
from tbl_LogOfLCDataTable
Where PrintDate >= dateadd(month, datediff(month, 0, getdate()), 0)
and PrintDate <= dateadd(day, -1, dateadd(month, datediff(month, 0, getdate())+1, 0))

I get:

17, A, Sample, 2/6/2008
17, A, Sample, 6/1/2008

But as soon as I add the same conversion to the Select CONVERT([varchar](10), PrintDate, 103) AS PrintDate
from tbl_LogOfLCDataTable, I get the following format:

MM/DD/YYYY

for both the PrintDate. So, result for:

Select Id, Forename, Surname, CONVERT([varchar](10), PrintDate, 103) AS PrintDate
from tbl_LCData
Where PrintDate >= dateadd(month, datediff(month, 0, getdate()), 0)
and PrintDate <= dateadd(day, -1, dateadd(month, datediff(month, 0, getdate())+1, 0))
union all
Select Id, Forename, Surname, CONVERT([varchar](10), PrintDate, 103) AS PrintDate
from tbl_LogOfLCDataTable
Where PrintDate >= dateadd(month, datediff(month, 0, getdate()), 0)
and PrintDate <= dateadd(day, -1, dateadd(month, datediff(month, 0, getdate())+1, 0))

is:

17, A, Sample, 6/2/2008
17, A, Sample, 6/1/2008

Any ideas on this?? I am using derived column to add it to excel destination. The conversion type on derived column is: date

Many thanks,

Rupa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 05:53:34

What datatype is printdate column?

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-04 : 06:02:33

Does both tables have same data type i.e. datetime?

-- sample data
declare @t1 table
(
	a int identity(1,1),
	b datetime
)

declare @t2 table
(
	a int identity(1,1),
	b datetime
)

insert @t1(b)
select getdate() union all
select getdate()+1 union all
select dateadd(month, 1, getdate())

insert @t1(b)
select getdate() union all
select getdate()+5 union all
select dateadd(month, -1, getdate())

-- original data
select * from @t1
union all
select * from @t2

-- converted data
select a, convert(varchar(10), b, 103) from @t1
union all
select a, convert(varchar(10), b, 103) from @t2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-06-04 : 06:06:34

PrintDate is datetime.

Thanks,

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-06-04 : 06:22:06

Thanks Harsh..but I'm a little confused with this..should I select the fields as normal and then select them again using conversion??

If so, I tried that and it doesn't work!

Thanks,

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-04 : 06:28:57

No. Those were just two sample queries to compare the output. You need only CONVERT query.

If both table's PRINTDATE column is datetime column and you are using 103 as conversion format, you should get correct result.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-04 : 06:33:34

Alternatively, you can try this:

Select Id, Forename, Surname, CONVERT(varchar(10), PrintDate, 103) AS PrintDate
From
(
Select Id, Forename, Surname, PrintDate
from tbl_LCData
Where PrintDate >= dateadd(month, datediff(month, 0, getdate()), 0)
and PrintDate <= dateadd(day, -1, dateadd(month, datediff(month, 0, getdate())+1, 0))
union all
Select Id, Forename, Surname, PrintDate
from tbl_LogOfLCDataTable
Where PrintDate >= dateadd(month, datediff(month, 0, getdate()), 0)
and PrintDate <= dateadd(day, -1, dateadd(month, datediff(month, 0, getdate())+1, 0))
) t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-06-04 : 06:42:19

Still gives me:

...,...,...,6/1/08
...,...,...,6/2/08

:-(

Thanks Harsh!!

Rupa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 12:28:46

It's inpossible for Style 103 to deliver a two-year digit. Only if you use Style 3 it will.
Why don't you actually TRY the suggestions we make?

E 12°55'05.25"
N 56°04'39.16"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-06-05 : 06:36:41

Peso,

Thanks for all your suggestions..I have been trying all possible solutions that have been suggested. I want this thing to work so I'm willing to try anything.

I have tried style 3, it still gives me the following:

6/1/2008
6/2/2008

As opposed to:

1/6/2008
2/6/2008

Any suggestions???

Thanks,

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-05 : 06:53:29

Rupa,

Without any conversion, what date value you see from tbl_LogOfLCDataTable table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-06-05 : 07:20:18

Harsh,

Thanks for your response, I have managed to make it work...I used the conversion and then set the datatype as 'Unicode String' in the Derived Column component.

To answer your question, it shows:

01/06/2008
02/06/2008

Many thanks for all your help Harsh..Much appreciated, as always

Rupa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources