Subquery Help... - 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

Subquery Help...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-01 : 10:44:03

I have two tables called CountyCodes and AddressDB

CountyCodes
StCode char(2)
CountyCd char (3)
CountyName varchar(50)
StNameAbr Char(2)
StNameLong varchar(30)

AddressDB
Zip char(5)
Doc char(3)
Scc char(5)

I want to join the two tables but they didn't have a field in common so to do so I did this:

SELECT CONCAT(StCode, CountyCd) AS SSC
FROM dbo.CountyCodes

Now how do I get this query to work?

SELECT { fn CONCAT(c.StCode, c.CountyCd) } AS c.Scc, c.CountyName, c.StNameAbr, c.StNameLong
FROM dbo.CountyCodes AS c INNER JOIN
dbo.AddressDB AS d ON c.scc = d.Scc
WHERE (d.Doc = '275')

This query is giving me this error:

CONCAT is not a recognized built-in function name I'm using SQL 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 10:47:11

SELECT c.StCode,+','+ c.CountyCd AS c.Scc, c.CountyName, c.StNameAbr, c.StNameLong
FROM dbo.CountyCodes AS c INNER JOIN
dbo.AddressDB AS d ON c.scc = d.Scc
WHERE (d.Doc = '275')

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-01 : 10:48:35

[code]SELECT a.StCode + a.CountyCd as SSC
,...
FROM CountyCodes a
INNER JOIN AddressDB b on b.Scc = a.StCode + a.CountyCd[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-01 : 10:48:55

SELECT c.StCode + c.CountyCd AS Scc, c.CountyName, c.StNameAbr, c.StNameLong
FROM dbo.CountyCodes AS c INNER JOIN
dbo.AddressDB AS d ON c.StCode + c.CountyCd = d.Scc
WHERE (d.Doc = '275')

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-01 : 10:49:45

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-01 : 10:52:03

Thanks so much you guys crack me up. I love it how you race! They all work!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources