Select Query ( Table Comparison )

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

SQL Server 2000 Forums

SQL Server Development (2000)

Select Query ( Table Comparison )

Author

Topic

Genis
Starting Member

5 Posts

Posted - 2005-08-11 : 11:27:53

I basically am trying to only select data from 1 table if there is matching data in a different table.

For example:

tblSourceContact

tblDataImports

tblContactUpdate

I want to create a select statement that takes all of the data in tblSourceContact and copies it over to tblContactUpdate. However I only want rows that have matching sysID fields in both tblSourceContact and tblDataImports to copy over to tblContactUpdate.

If any one could help me out with this selection statement that would be greatly appreciated. The overall idea is this:

SELECT tblSourceDataImport.*
FROM tblSourceDataImport
WHERE tblSourceContact.sysID = tblDataImports.sysID

This doesn't work, but I think it helps get what I want accross.

As of right now I am doing

select * from tblSourceDataImport tDI
inner join tblSourceContact tSC on tDI.sysID = tSC.sysID

This works, but due to the join it also returns a lot fo extra data. I was wondering if anyone could explain to me how to get this working with a WHERE statement, or something of that sort.

Any help would be greatly appreciated. Thanks a lot

~Matthew

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-11 : 12:42:44

how about just explicitly list the fields you want. * should not be used in production.

====================================================
Regards,
Sean Roussy

Thank you, drive through

Genis
Starting Member

5 Posts

Posted - 2005-08-11 : 12:50:51

yea, that is fine. I can make those changes and explicitly list the fileds. But my real problem is only copying data if two tables have corresponding fields.

Thanks for your response though.

~Genis

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-11 : 13:02:58

Like so

CREATE TABLE TABLE1
(
col1 int,
col2 int
)

CREATE TABLE TABLE2
(
col1 int,
col2 int
)

INSERT INTO TABLE1(col1,col2)
SELECT 1,2
UNION
SELECT 2,1

INSERT INTO TABLE2(col1,col2)
SELECT 1,2
UNION
SELECT 3,1

INSERT INTO TABLE1(col1,col2)
SELECT TABLE2.col1,TABLE2.col2
FROM TABLE1
JOIN TABLE2
ON TABLE1.col1 = TABLE2.col1

SELECT * FROM TABLE1

DROP TABLE TABLE1
DROP TABLE TABLE2

====================================================
Regards,
Sean Roussy

Thank you, drive through

Genis
Starting Member

5 Posts

Posted - 2005-08-11 : 13:08:15

thanks again for you fast reply I will play around with it. However it looks similar to the method I used in my original post(in regards to using join)

Thanks again, I will let you know how it goes

~Genis

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-11 : 13:09:56

you could use a where clause. old school joins, new school joins, the database engine does'nt care.

====================================================
Regards,
Sean Roussy

Thank you, drive through

Genis
Starting Member

5 Posts

Posted - 2005-08-11 : 13:27:44

would it be better to use a where though, because with the join I basically get two full tables and all i really want is 1. But I need to check the table with the sysID of the other table before I send(thats why I used the join) but if I could do it in a way that would just give me the data i need without the extra joined data that would be great. Problem is tht I don't know how to do it in a different way. I can't get a where statement to work like my above join statement.

~Genis

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-11 : 13:43:34

Did you run my code. It only pulled the one record over where the 2 columns matched. Isnt that what you wanted?

If not, you need to post your query, your DDL, sample data and expected results.

====================================================
Regards,
Sean Roussy

Thank you, drive through

Genis
Starting Member

5 Posts

Posted - 2005-08-11 : 13:47:28

no yours does what I want, thankyou

I was just curious about the where for it makes more sense to use to me. But im sure that you know what your doing way more than i do. So if thats how you would do it that is great, I will use that.

Thanks a lot

~Genis

nadejda18
Starting Member

7 Posts

Posted - 2005-08-11 : 17:26:40

Also, you can run:
SELECT tblSourceDataImport.*
FROM tblSourceDataImport, tblDataImports
WHERE tblSourceDataImport.sysID = tblDataImports.sysID

or:

SELECT *
FROM tblSourceDataImport
WHERE tblSourceContact.sysID in
(select sysID from tblDataImports)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources