results from 2 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

SQL Server 2005 Forums

Transact-SQL (2005)

results from 2 tables

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2010-05-05 : 04:04:43

hy guys, this may be simple but im having some trouble finding the solution.

table 1 :

1-------2

a -- aa
b -- bb
c -- cc
d -- dd
e -- ee
f -- ff

table 2

3-------4

a -- 100
b -- 200
c -- 300
d -- 400

select 1 [column 2], 4 [column 4] from table_1, table_2 where 1=3

the result will be :

aa -- 100
bb -- 200
cc -- 300
dd -- 400

what i would like is for the non existent records from table 2 to have a 'N/A' for example . just like this:

aa -- 100
bb -- 200
cc -- 300
dd -- 400
ee -- n/a
ff -- n/a

thanks in advance guys.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-05 : 04:19:13

Use left join and isnull():
select t1.column2, isnull(t2.column4,'n/a') as column4
from table1 as t1
left join table2 as t2
on t1.column1 = t2.column3

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 04:47:48

a different approach


SELECT col1,
MAX(Col2) AS Col2,
COALESCE(MAX(Col3),'N/A' AS Col3
FROM
(
SELECT 1 AS Col1,2 AS Col2, NULL AS Col3
FROM Table1
UNION ALL
SELECT 3 ,NULL,CAST(4 AS varchar(10))
FROM table2
)t
GROUP BY col1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-05 : 05:43:22

quote:
Originally posted by visakh16

a different approach

SELECT col1,
MAX(Col2) AS Col2,
COALESCE(MAX(Col3),'N/A' AS Col3
FROM
(
SELECT 1 AS Col1,2 AS Col2, NULL AS Col3
FROM Table1
UNION ALL
SELECT 3 ,NULL,CAST(4 AS varchar(10))
FROM table2
)t
GROUP BY col1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think this is:
- not understandable
- wrong
because "select 1 as col1 from table" will go and repeat the value 1 as often as there are records in the table.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 07:27:39

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

a different approach

SELECT col1,
MAX(Col2) AS Col2,
COALESCE(MAX(Col3),'N/A' AS Col3
FROM
(
SELECT [1] AS Col1,[2] AS Col2, NULL AS Col3
FROM Table1
UNION ALL
SELECT [3] ,NULL,CAST([4] AS varchar(10))
FROM table2
)t
GROUP BY col1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think this is:
- not understandable
- wrong
because "select 1 as col1 from table" will go and repeat the value 1 as often as there are records in the table.

No, you're never too old to Yak'n'Roll if you're too young to die.

i meant columns in table (1,2,3,4 ,...) not actual numbers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-05 : 10:00:11

[code]
SELECT Col2, ISNULL(CAST(D.col4 AS VARCHAR(10)), 'N/A')
FROM table_1 t1
OUTER APPLY (SELECT col4
FROM table_2 t2
WHERE t1.col1 = t2.col3)D[/code]

tempus
Starting Member

47 Posts

Posted - 2010-05-07 : 01:31:36

Hey guys,

im really sorry for the late reply. i got to say the the first solution is ok. i havent got the time now to check the other ones but i promise i will.

a simple isnull (column, 'text') done it for me :) .

one question thou: why does everyone uses sometext.columnname instead of the columnname itself? any tricks or something?

Thank you verry much for the help. I really apreciate it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-07 : 02:26:32

quote:
Originally posted by tempus

Hey guys,

im really sorry for the late reply. i got to say the the first solution is ok. i havent got the time now to check the other ones but i promise i will.

a simple isnull (column, 'text') done it for me :) .

one question thou: why does everyone uses sometext.columnname instead of the columnname itself? any tricks or something?

Thank you verry much for the help. I really apreciate it.

When joining tables and having same column names in different tables then you have to use tablename.columnname and for a short and more readable form you can use aliases for the tablenames.
In my example the aliases are t1 and t2.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources