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.
| Author |
Topic |
|
ibanez270dx
Starting Member
3 Posts |
 Posted - 2008-10-25 : 19:31:01
|
| Hello, I'm switching my Ruby on Rails app from MySQL to SQL Server 2005. Everything so far is running smoothly except for one of my queries. In my app, there are two tables: actives and olds. When my app is run, the actives table overwrites the olds table and a new set of data for the actives table is imported. My query is supposed to result in giving me all the entries that match on displayname but do not match on one of the other fields. Basically, all the "changed" entries: SELECT actives.del_flag AS del_flag, actives.displayname AS displayname, actives.fname AS fname, actives.lname AS lname, actives.initials AS intials, actives.title AS title, actives.department AS department, actives.location AS location, actives.company AS company, actives.manager AS manager, actives.phone_office AS phone_office, actives.phone_mobile AS phone_mobile, actives.phone_fax AS phone_fax, actives.phone_home AS phone_home, actives.phone_pager AS phone_pager, actives.phone_ipphone AS phone_ipphone, actives.email AS email, actives.webpage AS webpage, actives.address_street AS address_street, actives.address_pobox AS address_pobox, actives.address_city AS address_city, actives.address_state AS address_state, actives.address_country AS address_country, actives.address_zip AS address_zip FROM actives JOIN olds ON actives.displayname = olds.displayname WHERE actives.fname <> olds.fname OR actives.lname <> olds.lname OR actives.initials <> olds.initials OR actives.title <> olds.title OR actives.department <> olds.department OR actives.location <> olds.location OR actives.company <> olds.company OR actives.manager <> olds.manager OR actives.phone_office <> olds.phone_office OR actives.phone_mobile <> olds.phone_mobile OR actives.phone_fax <> olds.phone_fax OR actives.phone_home <> olds.phone_home OR actives.phone_pager <> olds.phone_pager OR actives.phone_ipphone <> olds.phone_ipphone OR actives.email <> olds.email OR actives.webpage <> olds.webpage OR actives.address_street <> olds.address_street OR actives.address_pobox <> olds.address_pobox OR actives.address_city <> olds.address_city OR actives.address_state <> olds.address_state OR actives.address_country <> olds.address_country OR actives.address_zip <> olds.address_zipbut this ends up giving me a very odd result. In my DB, there are 21 erroneous entries in which the displayname='.' . The query yeilds 420 rows of those 21 entries over and over, although they are identical in both the actives table and olds table. This query works perfectly in MySQL, but I have to switch to SQL server 2005. Any and all help is greatly appreciated!Thanks, - Jeff Miller |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-26 : 01:13:38
|
| you're sure that actives table and olds table contain one record per each displayname? |
 |
|
|
|
|
|
|
|
switch from MySQL to MSSQL, join query