Join two columns from one table

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

Join two columns from one table

Author

Topic

JPG
Starting Member

10 Posts

Posted - 2011-12-23 : 01:03:05

hello..

how can i join the two columns from one table..?

jpgervacio

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-23 : 01:06:40

Do you mean a self join?

select t1.c1, t2.c3
from t1
join t1 t2
on t1.c1 = t2.c2

Or maybe you mean joining on more than one column?

select t1.c1, t2.c2, t1.zzzz, t2.aaaa
from t1
join t2
on t1.c1 = t2.c1 and t1.c2 = t2.c2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JPG
Starting Member

10 Posts

Posted - 2011-12-23 : 01:16:58

This is my table..
UserFirstName|UserLastName
Alex |Rivera
Ana |San Sebastian

and the output that I want to show is something like this..
UserName
Alex Rivera
Ana San Sebastian

jpgervacio

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 01:19:41

its not joining but its called concatenation


SELECT COALESCE(UserFirstName + ' ','') + COALESCE(UserLastName,'') AS UserName
FROM table

Add the COALESCE only if columns are nullable

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

JPG
Starting Member

10 Posts

Posted - 2011-12-23 : 01:26:55

there's an error in my program after I put the syntax..

*Error converting data type nvarchar to numeric.

jpgervacio

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-23 : 01:33:28

Why are you using numeric in your program?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JPG
Starting Member

10 Posts

Posted - 2011-12-23 : 01:33:53

@visakh16
sorry I didn't know that it is called concatenation, thank you for the correction :)

jpgervacio

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 01:35:13

quote:
Originally posted by JPG

there's an error in my program after I put the syntax..

*Error converting data type nvarchar to numeric.

jpgervacio

hmm
how come your username field is numeric? Didnt understand the reason for that. please use appropriate datatype for your columns

it should be varchar

if not, you've to do explicit casting


SELECT COALESCE(CAST(UserFirstName AS varchar(100))+ ' ','') + COALESCE(CAST(UserLastName AS varchar(100)),'') AS UserName
FROM table

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

JPG
Starting Member

10 Posts

Posted - 2011-12-23 : 01:36:46

@tkizer

I'm not using numeric I don't know why it is the error :|

jpgervacio

JPG
Starting Member

10 Posts

Posted - 2011-12-23 : 01:40:51

Actually this is my sample prog..

SELECT RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName, OrdersHistory.OrderNo, OrdersHistory.OrderSourceText AS OrderSource, OrdersHistory.PaymentTypeText AS PaymentType,
SUM(OrdersHistory.NetTotal) AS NetPrice,
SUM(OrdersHistory.GrossTotal) AS GrossPrice, ReasonMaster.ReasonDescription AS Reason,
COALESCE(CAST(UserFirstName AS varchar(100))+ ' ','') + COALESCE(CAST(UserLastName AS varchar(100)),'') AS UserName

FROM OrdersHistory
JOIN ReasonMaster
ON OrdersHistory.ReasonFKID = ReasonMaster.PKID
JOIN RestaurantMaster
ON RestaurantMaster.PKID = OrdersHistory.RestaurantID
JOIN UserMaster
ON OrdersHistory.MessageUserId = UserMaster.UserID

WHERE
OrdersHistory.OrderDate BETWEEN '2011-11-01' AND '2011-12-01'
AND OrdersHistory.StatusFKID = 3

GROUP BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName, OrdersHistory.OrderNo, OrdersHistory.OrderSourceText, OrdersHistory.PaymentTypeText, ReasonMaster.ReasonDescription, UserMaster.UserFirstName, UserMaster.UserLastName;

and I don't know why the error is..
*Error converting data type nvarchar to numeric. :'(

jpgervacio

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 01:52:55

can you check data types for UserFirstName ,UserLastName ,NetTotal,GrossTotal
I doubt whether error is caused by concatenation. it can even be from those SUM() above if NetTotal,GrossTotal etc are not numeric

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

JPG
Starting Member

10 Posts

Posted - 2011-12-23 : 01:59:56

i remove the sum and it's already, but my problem now is there's a lot of duplication, all in need is one store per column..

014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Ahren Villalobos
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Ahwie sacrez
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Aldreen Wiena Amatorio
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Alen Arienda
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Alex Meraña Jr.
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Angelik Beltran
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Angelita Luga
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Angelyn Aban
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Ann May Andrino
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Anthony Pe
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Beverly Tandoy
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Bless Elgera
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Blessie Elgera
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Cezar Santos
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Chano Pitogo
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Chico Carag
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Christian Pitogo
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Dana dela Rosa
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) dhalia jarical
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Don Ricardo
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Dondie Ricardo
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Dulce Mia Besinio
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Elmer Mirador
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Erwin Miñano
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Evangeline Baniel
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Gay Montante
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Grace Maldonado
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Harold Ferrer
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Hazel Jimenez
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Ice Santos
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) irish jurada
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Ivy Cortez
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Jenny Kaye Mata
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Jersey Gerez
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Jesus Inaudito
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Jhapz Ebe
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) Joan Bajao
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) John Jordaen Bataclan
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) John Michael Maleniza
014 ALABANG 014-12/11/2011-120408 Phone Cash 164.00 204.00 connectivity issue(phone/psi/msgtool/remote) John Paul Mejia

Jeneca :)

JPG
Starting Member

10 Posts

Posted - 2011-12-23 : 02:00:51

one row per store rather..

Jeneca :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 03:15:42

dont remove the sum instead check data types and if non numeric you need to exclude them before applying SUM()

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources