SELECT Question? - 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

SELECT Question?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-12-02 : 02:56:16

Hi,

I have two tables that i want to join.Table A has fields like Num,Date,SName and FormType. Table B has FormTypeID and Name. I want to join those two tables with FormType and FormTypeID. But the problem is, in FormType values are varchar like "1,4,6," and in FormTypeID values are integer.

Is there a way to join those two tables?

thanks in advance.
Best Regards

chadmat
The Chadinator

1974 Posts

Posted - 2010-12-02 : 03:01:32

Looks like you need to normalize TableA

-Chad

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 03:07:03

That is correct that you should normalize the table
but anyway if you cant change database design then you can use the below query -


SELECT * FROM TableA A INNER JOIN TableB B ON A.FormType LIKE '%'+CONVERT(VARCHAR(10), FormTypeID)+'%'

Vaibhav T

If I cant go back, I want to go fast...

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-12-02 : 03:14:29

thanks so much Vaibhav T

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-12-02 : 03:28:39

Is there a way to combine results into one field?
Select returns more than one rows and i would like to combine Name fields as one.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-02 : 03:30:21

quote:
Originally posted by raysefo

Is there a way to combine results into one field?
Select returns more than one rows and i would like to combine Name fields as one.

What version of sql you are using ?

Edit:
Have a look at :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-12-02 : 03:35:39

SQL Server 2008 R2

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 03:41:04

Please post some sample data and required output.

Vaibhav T

If I cant go back, I want to go fast...

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-12-02 : 03:42:52

name EformName EformName_eng CreationDate EformType IsApproved CreatedBy
jack Form1 Form1 01.12.2010 11:14:00 10 0 Jack
jack Form2 Form2 01.12.2010 11:14:00 10 0 Jack

Here is a sample output from select query.Except EformName and EformName_eng all fields are same. Is there a way to output like below?
name EformName EformName_eng CreationDate EformType IsApproved CreatedBy
jack Form1,Form2 Form1,Form2 01.12.2010 11:14:00 10 0 Jack

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-12-02 : 04:28:52

Any help please?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 04:45:34

Check PK_Bohra's Link suggested. You will get a clue.

Vaibhav T

If I cant go back, I want to go fast...

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-12-02 : 04:48:57

I already checked the link but i m not that good at SQL.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 05:21:11

Try this -


DECLARE @Sample AS TABLE ( Name VARCHAR(20), EformName VARCHAR(20), EformName_eng VARCHAR(20), CreationDate DATETIME, EformType INT, IsApproved BIT, CreatedBy VARCHAR(20) )

INSERT INTO @Sample 
SELECT 'jack', 'Form1', 'Form1', '01/12/2010 11:14:00', 10, 0, 'Jack' UNION ALL
SELECT 'jack', 'Form2', 'Form2', '01/12/2010 11:14:00', 10, 0, 'Jack'

SELECT * FROM @Sample

/*
name EformName EformName_eng CreationDate EformType IsApproved CreatedBy
jack Form1,Form2 Form1,Form2 01.12.2010 11:14:00 10 0 Jack
*/


;WITH C AS 
(
SELECT * FROM @Sample
)

SELECT DISTINCT Name, 
STUFF((SELECT ',' + EformName FROM @Sample WHERE Name = C.Name 
AND CreationDate = C.CreationDate
AND EformType = C.EformType 
AND IsApproved = C.IsApproved
AND CreatedBy = C.CreatedBy
FOR XML PATH('')),1,1,'') AS EformName
,
STUFF((SELECT ',' + EformName_eng FROM @Sample WHERE Name = C.Name 
AND CreationDate = C.CreationDate
AND EformType = C.EformType 
AND IsApproved = C.IsApproved
AND CreatedBy = C.CreatedBy 
FOR XML PATH('')),1,1,'') AS EformName_eng
, CreationDate, EformType, IsApproved, CreatedBy
FROM C

Vaibhav T

If I cant go back, I want to go fast...

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-12-03 : 06:34:35

Hi,
I tried to convert it to my structure But getting this error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Users.name" could not be bound.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Name'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'Name'.

;WITH C AS
(
SELECT * FROM Eform_Vekalet
)
select distinct Users.name,
stuff((select','+EformType.EformName from Eform_Vekalet INNER JOIN Tasks ON Eform_Vekalet.EFormID = Tasks.EformID INNER JOIN Users ON Eform_Vekalet.CreatedBy = Users.SAMAccountName INNER JOIN EformType ON Eform_Vekalet.EFormType_Vekalet LIKE '%'+CONVERT(VARCHAR(10), EformType.EformType)+'%'
where Users.name = C.Name
and CONVERT(CHAR(10),Eform_Vekalet.CreationDate,104) + SUBSTRING(CONVERT(varchar,Eform_Vekalet.CreationDate,113),12,9) = C.CreationDate
and Eform_Vekalet.EformID = C.EformID
For XML Path('')),1,1,'') As EformName
,
Stuff((select','+EformType.EformName_eng from Eform_Vekalet INNER JOIN Tasks ON Eform_Vekalet.EFormID = Tasks.EformID INNER JOIN Users ON Eform_Vekalet.CreatedBy = Users.SAMAccountName INNER JOIN EformType ON Eform_Vekalet.EFormType_Vekalet LIKE '%'+CONVERT(VARCHAR(10), EformType.EformType)+'%'
where Users.name = C.Name
and CONVERT(CHAR(10),Eform_Vekalet.CreationDate,104) + SUBSTRING(CONVERT(varchar,Eform_Vekalet.CreationDate,113),12,9) = C.CreationDate
and Eform_Vekalet.EformID = C.EformID
For XML Path('')),1,1,'') As EformName_eng,CreationDate,EformID
From C

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-03 : 08:10:42

Try this -


;WITH C AS 
(
select * 
from Eform_Vekalet INNER JOIN Tasks ON Eform_Vekalet.EFormID = Tasks.EformID 
INNER JOIN Users ON Eform_Vekalet.CreatedBy = Users.SAMAccountName 
INNER JOIN EformType ON Eform_Vekalet.EFormType_Vekalet LIKE '%'+CONVERT(VARCHAR(10), EformType.EformType)+'%' 
)

select distinct Users.name,
stuff((select','+EformType.EformName from Eform_Vekalet INNER JOIN Tasks ON Eform_Vekalet.EFormID = Tasks.EformID 
INNER JOIN Users ON Eform_Vekalet.CreatedBy = Users.SAMAccountName 
INNER JOIN EformType ON Eform_Vekalet.EFormType_Vekalet LIKE '%'+CONVERT(VARCHAR(10), EformType.EformType)+'%' 
where Users.name = C.Name
and CONVERT(CHAR(10),Eform_Vekalet.CreationDate,104) + SUBSTRING(CONVERT(varchar,Eform_Vekalet.CreationDate,113),12,9) = C.CreationDate 
and Eform_Vekalet.EformID = C.EformID 
For XML Path('')),1,1,'') As EformName
,
Stuff((select','+EformType.EformName_eng from Eform_Vekalet INNER JOIN Tasks ON Eform_Vekalet.EFormID = Tasks.EformID INNER JOIN Users ON Eform_Vekalet.CreatedBy = Users.SAMAccountName INNER JOIN EformType ON Eform_Vekalet.EFormType_Vekalet LIKE '%'+CONVERT(VARCHAR(10), EformType.EformType)+'%'
where Users.name = C.Name
and CONVERT(CHAR(10),Eform_Vekalet.CreationDate,104) + SUBSTRING(CONVERT(varchar,Eform_Vekalet.CreationDate,113),12,9) = C.CreationDate 
and Eform_Vekalet.EformID = C.EformID 
For XML Path('')),1,1,'') As EformName_eng,CreationDate,EformID 
From C

Vaibhav T

If I cant go back, I want to go fast...

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-12-03 : 08:19:15

Msg 8156, Level 16, State 1, Line 1
The column 'EformID' was specified multiple times for 'C'.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-03 : 08:40:33

quote:
Originally posted by raysefo

Msg 8156, Level 16, State 1, Line 1
The column 'EformID' was specified multiple times for 'C'.

List out the columns from all tables instead of "*"
I dont know all the columns thats why I put the "*"

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources