March 8, 2013 at 7:22 am
I have two tables with following data
Table1
Month dramount
------ ---------
April 1709.75
March 5000
Table2
Month cramount
------ ---------
March 6295
I want to join two tables and want results as following
Month dramount cramount
------ --------- ----------
April 1709.75 NULL
March 5000 6295
How can i achieve this?
March 8, 2013 at 7:24 am
Is there a pk -fk link between table
March 8, 2013 at 7:25 am
No pk-fk links
March 8, 2013 at 7:32 am
You're looking at using a LEFT OUTER JOIN read up on themhere http://msdn.microsoft.com/en-gb/library/ms187518(v=sql.105).aspx
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 8, 2013 at 7:33 am
Can you provide code for this?
I tried left outer join but with unexpected result
March 8, 2013 at 7:39 am
Post the code that you've written and I (or other members) will point out the error.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 8, 2013 at 7:41 am
select month,dramount,cramount from table1
left outer join table2
on table1.month=table2.month
March 8, 2013 at 7:47 am
Ok, I take it you got an Error being reported about month?
select
[month]
,dramount
,cramount
from table1
left outer join table2
on table1.[month]=table2.[month]
This should now work, because month is an SQL function you need to encapsulate it with in square brackets ([]), as this tells the compiler its a column not a function.
You should be careful when naming columns that are reserved words to prevent needing to do this, maybe a better name for the columns is Period, or PeriodMonth, as this looks like accounting data, and so accountants deal in periods.
Hope this helps.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 8, 2013 at 7:49 am
It's helpful if you get an "unexpected result", that you post what the result is. I expect what you actually got was an error about the ambigious column "month", so you will have to fully qualify that column like so:
SELECT t1.Month, t1.dramount, t2.cramount
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 ON t1.Month = t2.Month;
It may make sense to fully qualify all field names in advance, so that if you add additional columns to the tables later, it won't break your existing queries.
March 8, 2013 at 7:55 am
Deque (3/8/2013)
It's helpful if you get an "unexpected result", that you post what the result is. I expect what you actually got was an error about the ambigious column "month", so you will have to fully qualify that column like so:
SELECT t1.Month, t1.dramount, t2.cramountFROM Table1 t1
LEFT OUTER JOIN Table2 t2 ON t1.Month = t2.Month;
It may make sense to fully qualify all field names in advance, so that if you add additional columns to the tables later, it won't break your existing queries.
Well spotted I didn't notice the Alias missing. Doh!!!.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 8, 2013 at 8:06 pm
It wont work as one table is having data about single month and other is having data for two months.
March 8, 2013 at 8:07 pm
There is not any problem with column name.
The problem is when one table is having data for month which is not present in other table.
March 8, 2013 at 8:08 pm
This will display all months from t1 but not from t2
March 8, 2013 at 8:10 pm
Jason-299789 (3/8/2013)
Ok, I take it you got an Error being reported about month?
select
[month]
,dramount
,cramount
from table1
left outer join table2
on table1.[month]=table2.[month]
This should now work, because month is an SQL function you need to encapsulate it with in square brackets ([]), as this tells the compiler its a column not a function.
You should be careful when naming columns that are reserved words to prevent needing to do this, maybe a better name for the columns is Period, or PeriodMonth, as this looks like accounting data, and so accountants deal in periods.
Hope this helps.
There is not any problem with column name.
The problem is when one table is having data for month which is not present in other table.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply