Inner join? - 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

Inner join?

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-21 : 10:17:49

Ok,

I want to compare data in two tables, based on a single value in one that equals a substring of a value in the other, and take the results and drop them into a new table.

douglas.dbo.persondata: I want the data from this table dropped into the new table WHEN the Formid value for a row matches the substring of the offenseno(10,15) in the offense.dbo.off_per table. My assumed approach i thought would be:

Select * into Douglasconversion.dbo.lastfixforoffper
FROM Douglas.dbo.persondata m
Inner JOIN Offense.dbo.off_per a
ON
m.FormID = SUBSTRING(a.Offenseno,10,15)

Of course, that aint working.

Seems pretty simple and straightforward. Suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 10:22:17

by substring of the offenseno(10,15) do you mean characters from 10th to 15th position in the string
ie
ststri
from
thisisateststring

or

15 characters starting from 10th position?

ie ngstringexample
from

thisisalongstringexampletoillustratesubstring

?

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-21 : 10:38:48

correct. starting at position 10 and including up to the 15th character. (including the 15th) - so it's grabbing the last six characters in Offenseno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 10:50:56

then it should be


Select * into Douglasconversion.dbo.lastfixforoffper
FROM Douglas.dbo.persondata m
Inner JOIN Offense.dbo.off_per a
ON 
m.FormID = SUBSTRING(a.Offenseno,10,6)

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-21 : 10:57:58

Then why am i getting this error?:

Msg 2705, Level 16, State 3, Line 2
Column names in each table must be unique. Column name 'OFFENSENO' in table 'lastfixforoffper' is specified more than once.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-21 : 10:59:43

wait a minute. I think i know why.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-21 : 11:01:27

my suspicion was incorrect. The error still makes no sense to me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 11:02:44

quote:
Originally posted by WJHamel

wait a minute. I think i know why.

reason is you're using * while dumping into table and you've same column coming from multiple tables in FROM so it is unable to create duplicate columns with same name in the table. So use explicit column list and alias it with different columnname if you want both columns with same name in your final table

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-21 : 11:14:56

I had forgotten that there are columns in both tables with the same name. So let me try to approach this from a different angle.

I will dump ALL of the douglas.dbo.persondata table into the new temp table (Douglasconversion.dbo.lastfixforoffper). Once i've done that, i want to DELETE FROM that new table, any rows where the FormID value DOES NOT EXIST as a substring of the last six digits in the offenseno field in off_per.

So, getting the persondata data into the new table is easy. However, how would the delete from statement look?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 11:18:23

quote:
Originally posted by WJHamel

I had forgotten that there are columns in both tables with the same name. So let me try to approach this from a different angle.

I will dump ALL of the douglas.dbo.persondata table into the new temp table (Douglasconversion.dbo.lastfixforoffper). Once i've done that, i want to DELETE FROM that new table, any rows where the FormID value DOES NOT EXIST as a substring of the last six digits in the offenseno field in off_per.

So, getting the persondata data into the new table is easy. However, how would the delete from statement look?

for that there's no need of changing approach

if you're interested only in columns of Douglas.dbo.persondata to be dumped you can tweak the statement like below


Select m.* into Douglasconversion.dbo.lastfixforoffper
FROM Douglas.dbo.persondata m
Inner JOIN Offense.dbo.off_per a
ON 
m.FormID = SUBSTRING(a.Offenseno,10,15)

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-21 : 11:28:50

duh. thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 12:31:03

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources