comparing tables

Hi,

I have two tables containing (ahem) lists of mp3 tunes. One is the "master" list (table name "mp3_master") - everything I've got on my home pc. The other (table name "mp3") details everything I've got on my work pc.

Both tables have an id field, an "artist" field and a "title" field.

I'd like to simply compare the two tables and return a list of any artist/title combinations that are in the mp3 table (ie: that I have at work) but not in the mp3_master table (ie: that I haven't yet taken home).standard sql solution:

select id, artist, title from mp3
except
select id, artist, title from mp3_master

microsoft sql/server syntax has minus instead of except, i believe

if that don't work, do it the old-fashioned way:

select id, artist, title from mp3
where id not in (select id from mp3_master)

rudy
http://rudy.ca/|||Except is a function used in Analysis Services - MDX

The query

select id, artist, title from mp3
where id not in (select id from mp3_master)

will work fine, however it is a slow way, performance wise. Once a match is found the main query will not stop scanning the sub-query. A faster query is to use the NOT EXISTS

select id, artist, title from mp3
where not exists (select * from mp3_master mp3.id = mp3_master.id)

Once a match is found the search stops|||Hmm - maybe I should have added that the id numbers in these tables don't match up.

select id, artist, title from mp3
where title not in (select title from mp3_master)

I used that. As long as I've not got the same tune by different artists, I should be ok.

select id, artist, title from mp3
where not exists (select * from mp3_master mp3.id = mp3_master.id)

I get a "syntax error near "." on that - is that some sort of shorthand for a join?

Cheers anyway :)|||i don't have sql/server to test on, so i'm guessing, but like i said, i think the operator is MINUS

select artist, title from mp3
minus
select artist, title from mp3_master

if the id numbers don't match up, you don't want to match on id number in the subselect, neither a's way nor mine

rudy|||Hi,

The 'minus' clause certainly won't work in Query Analyzer. There is no syntax even simlar in SQL Server.

The 'not in' or 'where not exists' are the correct syntax.

You had some typos in the SQL that failed with the syntax error. It should be:

select id, artist, title from mp3
where not exists (select * from mp3_master where mp3_master.mp3.id = mp3_master.id)

Hope this helps.

- Andy Abel|||thanks andy, it wouldn't be the first time sql/server didn't support standard sql ;)

spudhead, since you can't match on ids, try this --

select id, artist, title from mp3
where not exists
( select 1 from mp3_master
where artist = mp3.artist
and title = mp3.title )

rudy|||I love reading these forums, to help people and also to read SQLServer bashing. I'm a big fan of SQLServer, so when I read a jab or a bash I have to find out if what is said is true.

it wouldn't be the first time sql/server didn't support standard sql

I tried to find out the ANSI standards for SQL and the only thing I could find on MINUS is:
The MINUS keyword is not ANSI-compliant, the implementation of the MINUS operator is implemented in Oracle.
So bully for Oracle|||yeah, i love these syntax discussions too

actually, the ansi standard operator is EXCEPT

oracle's support of MINUS is non-standard

:cool:

rudy

Company with Address Question

I have a database that allows a client to have multiple addresses, could be a home, business, vacation, etc... I'm trying to deside if I should put the Company Name and Title fileds in the tblClientAddresses table or keep it with the tblClient table. We do mailings to clients and if the mail is going to a home address the company and title should be excluded. If I put them in the tblClients table I really have no way of knowing if we are mailing to a home or a company. If i put the company and title with the address it makes sence that this would solve the issue.

Looking for any thoughts or suggestions?

Thank you,Standard normalization would say if there are multiple addresses per client, put it in a separate table. The only time I would recommend against this is for performance reasons, but I'm guessing you don't have any performance problems yet.|||The Company Name and Title fields belong in tblClient.

You could do something like this to make them blank if the address is a Home address:

SELECT
 CASE
 WHEN A.type = 'Home' THEN ''
 ELSE C.Name
 END AS Name,
 CASE
 WHEN A.type = 'Home' THEN ''
 ELSE C.Title
 END AS Title,
 A.Address1,
 A.Address2,
 A.City,
 A.State,
 A.ZIP
FROM
 tblClient C
INNER JOIN
 tblClientAddress A ON C.ClientID = A.ClientID

As the OP said, though, sometimes you give up correct normalization for performance -- especially if the data is static and the tables are extremely large.

Terri