Trouble with join

Question

Post reply

Trouble with join

igngua

SSCrazy

Points: 2510
More actions
March 22, 2010 at 3:35 pm

#386557

Scenario:
Two identical tables from two diferent databases. Table 'A' and table 'B'
I need to get every data not present in table 'A' from table 'B'
This is what i´ve got.
select t2.cotnum, t2.codaux,
t1.cotnum
from softlandsoporte2.softland.nwcotiza t2
left join hermes.lab0708.softland.nwcotiza t1 on t2.cotnum = t1.cotnum
where t1.cotnum is null

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

March 22, 2010 at 3:40 pm

#1137894

So what's the problem??

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Looks fine to me. Is it not producing the desired results?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

igngua SSCrazy Points: 2510 More actions · Answer 3

CirquedeSQLeil (3/22/2010)
Looks fine to me. Is it not producing the desired results?

HI!

Im not getting the results i want.

t2 has less data than t1 and i´m getting no data.

select t2.cotnum, t2.codaux,

t1.cotnum

from softlandsoporte2.softland.nwcotiza t2

left join hermes.lab0708.softland.nwcotiza t1 on t2.cotnum = t1.cotnum

where t1.cotnum is null

thanks in advance.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

igngua (3/23/2010)
CirquedeSQLeil (3/22/2010)
Looks fine to me. Is it not producing the desired results?
HI!
Im not getting the results i want.
t2 has less data than t1 and i´m getting no data.
select t2.cotnum, t2.codaux,
t1.cotnum
from softlandsoporte2.softland.nwcotiza t2
left join hermes.lab0708.softland.nwcotiza t1 on t2.cotnum = t1.cotnum
where t1.cotnum is null
thanks in advance.

Please think about what you just said, there is less data in t2 than t1. The query you are running is asking for data in t2 that is not in t1. The fact you are getting no results indicate to me that you are getting the correct results. Change your query to ask for data in t1 that is not in t2 and see what you get.

igngua SSCrazy Points: 2510 More actions · Answer 5

thanks!!

it worked!

select t1.cotnum, t1.codaux,

t2.cotnum, t2.codaux

from hermes.lab0708.softland.nwcotiza t1

left join softlandsoporte2.softland.nwcotiza t2 on t1.cotnum = t2.cotnum

where t2.cotnum is null

Now i´ve to update T2 with all the columns from 1 not present in t2

this is what i´ve got.

insert into softlandsoporte2.softland.nwcotiza(t2.CotNum,t2.CodAux,t2.NomCon,t2.CodMon)

select(t1.CotNum,t1.CodAux,t1.NomCon,t1.CodMon)

from hermes.lab0708.softland.nwcotiza t1

left join softlandsoporte2.softland.nwcotiza t2

on t2.cotnum = t1.cotnum

where t2.cotnum is null

I get a sintax error on the blackened line; Incorrect syntax near ','

thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

igngua (3/23/2010)
thanks!!
it worked!
select t1.cotnum, t1.codaux,
t2.cotnum, t2.codaux
from hermes.lab0708.softland.nwcotiza t1
left join softlandsoporte2.softland.nwcotiza t2 on t1.cotnum = t2.cotnum
where t2.cotnum is null
Now i´ve to update T2 with all the columns from 1 not present in t2
this is what i´ve got.
insert into softlandsoporte2.softland.nwcotiza(t2.CotNum,t2.CodAux,t2.NomCon,t2.CodMon)
select(t1.CotNum,t1.CodAux,t1.NomCon,t1.CodMon)
from hermes.lab0708.softland.nwcotiza t1
left join softlandsoporte2.softland.nwcotiza t2
on t2.cotnum = t1.cotnum
where t2.cotnum is null
I get a sintax error on the blackened line; Incorrect syntax near ','
thanks.

Take out the parens in the select, not needed.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

igngua (3/23/2010)
thanks!!
it worked!
select t1.cotnum, t1.codaux,
t2.cotnum, t2.codaux
from hermes.lab0708.softland.nwcotiza t1
left join softlandsoporte2.softland.nwcotiza t2 on t1.cotnum = t2.cotnum
where t2.cotnum is null
Now i´ve to update T2 with all the columns from 1 not present in t2
this is what i´ve got.
insert into softlandsoporte2.softland.nwcotiza(t2.CotNum,t2.CodAux,t2.NomCon,t2.CodMon)
select(t1.CotNum,t1.CodAux,t1.NomCon,t1.CodMon)
from hermes.lab0708.softland.nwcotiza t1
left join softlandsoporte2.softland.nwcotiza t2
on t2.cotnum = t1.cotnum
where t2.cotnum is null
I get a sintax error on the blackened line; Incorrect syntax near ','
thanks.

Congrats.

And Lynn has the answer about the error.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

You may also need to take out the t2. from this line:

insert into softlandsoporte2.softland.nwcotiza(t2.CotNum,t2.CodAux,t2.NomCon,t2.CodMon)

igngua SSCrazy Points: 2510 More actions · Answer 9

igngua

SSCrazy

Points: 2510

March 23, 2010 at 12:55 pm

#1138492

Thanks you all!

it worked!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

igngua (3/23/2010)
Thanks you all!
it worked!

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

igngua (3/23/2010)
Thanks you all!
it worked!

Thank you for the feedback, and you are welcome.