left outer join problem - 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

left outer join problem

Author

Topic

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 05:24:33

here is my query :
insert into cristi.dbo.pontaj (card)
select distinct seriecard from cristi.dbo.temp
left outer join cristi.dbo.pontaj
on cristi.dbo.temp.marca=cristi.dbo.pontaj.marca

the problem is that MARCA column has 2000 entries and column SerieCard has only 500 ... but when i insert them ... it doesn't insert them according to MARCA ... it copies them below the 2000 rows... and my table becomes from a 2000 row table to a 2500 row...

Any ideas ?

10x !

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 05:29:53

use inner join to insert the seriecard where marca values equals in both tables (that values will be inserted)

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 05:46:17

quote:
Originally posted by bklr

use inner join to insert the seriecard where marca values equals in both tables (that values will be inserted)

no good... :( it copies them where there are no values matching in column MARCA ... :(

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 06:16:26

bump ? anyone ?

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 07:02:25

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 08:25:45

nobody ? :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 08:46:38

without any sample data its difficulkt to understand your exact problem. can you provide some data and illustrate waht your problem is?

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 08:59:54

so.... 2 columns in 2 tables
Column1.table1=ID Column2.table1 = name(2000rows)
Column1.table2=id Column2.table2=numbercard(500rows)

I need to copy into table1 ... Column2.table2 but only for those who have a numbercard... so from 2000 names only 500 have number cards... but witch ones ? so i need to match them according to the Id ... but when I run the query that i wrote in the upper section ... it copies those 500 entries for number cards all over the place... so instead of copying the only to the names that have entries ... it copies them all over the place ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:02:25

quote:
Originally posted by kote_alex

so.... 2 columns in 2 tables
Column1.table1=ID Column2.table1 = name(2000rows)
Column1.table2=id Column2.table2=numbercard(500rows)

I need to copy into table1 ... Column2.table2 but only for those who have a numbercard... so from 2000 names only 500 have number cards... but witch ones ? so i need to match them according to the Id ... but when I run the query that i wrote in the upper section ... it copies those 500 entries for number cards all over the place... so instead of copying the only to the names that have entries ... it copies them all over the place ...

are you trying to update the value of numbercard onto table1 or do you want to insert new rows itself with numbercard values into table1?

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 09:14:59

quote:
Originally posted by visakh16

quote:
Originally posted by kote_alex

so.... 2 columns in 2 tables
Column1.table1=ID Column2.table1 = name(2000rows)
Column1.table2=id Column2.table2=numbercard(500rows)

I need to copy into table1 ... Column2.table2 but only for those who have a numbercard... so from 2000 names only 500 have number cards... but witch ones ? so i need to match them according to the Id ... but when I run the query that i wrote in the upper section ... it copies those 500 entries for number cards all over the place... so instead of copying the only to the names that have entries ... it copies them all over the place ...

are you trying to update the value of numbercard onto table1 or do you want to insert new rows itself with numbercard values into table1?

yes ! i want to insert new rows with the numbercard values into table one from table 2 but only according with the ID column witch tells me what name has what numbercard ! :)

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-02-04 : 09:17:13

have you not tried something like this

insert into
cristi.dbo.pontaj (card)
SELECT
Seriecard
FROM
(
select distinct
seriecard
from
cristi.dbo.temp
left outer join
cristi.dbo.pontaj
on
cristi.dbo.temp.marca=cristi.dbo.pontaj.marca
) A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:18:41

then shouldnt this be enough?


insert into cristi.dbo.pontaj (marca,card)
select marca,seriecard from cristi.dbo.temp

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 09:34:04

quote:
Originally posted by visakh16

then shouldnt this be enough?

insert into cristi.dbo.pontaj (marca,card)
select marca,seriecard from cristi.dbo.temp

nop !:(( .... it won't work ... it inserts null in the names column to even if there's no value in the id column

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 09:35:49

quote:
Originally posted by NeilG

have you not tried something like this

insert into
cristi.dbo.pontaj (card)
SELECT
Seriecard
FROM
(
select distinct
seriecard
from
cristi.dbo.temp
left outer join
cristi.dbo.pontaj
on
cristi.dbo.temp.marca=cristi.dbo.pontaj.marca
) A

this still does not work because marca is the id column and it inserts the values from SERIECARD all over the place... it doesn't care that it has no corespondent value in the ID column from one table to another :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:38:58

quote:
Originally posted by kote_alex

quote:
Originally posted by visakh16

then shouldnt this be enough?

insert into cristi.dbo.pontaj (marca,card)
select marca,seriecard from cristi.dbo.temp

nop !:(( .... it won't work ... it inserts null in the names column to even if there's no value in the id column

id column? which id column? post your table structures then. i cant understand what id column you're talking about

try to post in this format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 10:00:26

here are the two tables ....
first : http://www.bihor.ro/Table1.jpg

second : http://www.bihor.ro/Table2.jpg

I need to fill seriecard with the data from table2 but in corelation to column 1 in both table witch is the ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 10:05:01

great! so you dont know differences b/w insert & update? I'd asked before whether you require update,then why did you tell you're looking for new insert?


UPDATE t1
SET t1.seriecard=t2.seriecard
FROM table1 t1
JOIN table2 t2
ON t2.marca=t1.marca

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-04 : 10:14:46

it gave me an error message : The multi-part identifier "temp_2.seriecard" could not be bound.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-04 : 11:42:15

It should have been a type somewhere. Can you Check your column names again for any errors or case sensitivity?. Whatever visakh has mentioned should work fine. Its a simple update.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-04 : 11:42:53

I meant typo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 11:54:45

quote:
Originally posted by kote_alex

it gave me an error message : The multi-part identifier "temp_2.seriecard" could not be bound.

is it SO difficult to copy and paste the given query and execute?
my posted query does not have temp_2 so either copy and execute what i've gievn or post your query

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources