a subquery and tables with composite keys

Question

a subquery and tables with composite keys

jay.l.100

Old Hand

Points: 370
More actions
January 21, 2008 at 10:29 pm

#182213

I am trying to return all company's that are not holding interviews by, state. companyname, division are composite keys(Primary in the employer table and foreign in the interview table. SQL server does not like the composite keys. If I remove one key from each composition I am able to run the query. but I do not get the correct results. This query returns the results that I want if I run it in mysql. Any ideas?
SELECT employer.statecode, COUNT(employer.statecode) AS ct
FROM employer
WHERE employer.companyname, employer.division
NOT IN (SELECT interview.companyname, interview.division
FROM interview) GROUP BY employer.statecode ORDER BY employer.statecode
these are the error codes i get
Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near ','.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'GROUP'.
thanks in advance,
-jay lill

Viewing 7 posts - 1 through 6 (of 6 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

U cannot use 2 columns in the where condition and in the NOT IN caluse. Also cannot separate columns using commas in the where caluse.

One wayof doing this is

SELECT employer.statecode, COUNT(employer.statecode) AS ct

FROM employer

WHERE employer.companyname

NOT IN (SELECT interview.companyname

FROM interview)

AND employer.division NOT IN (SELECT interview.division FROM interview)

GROUP BY employer.statecode ORDER BY employer.statecode

"Keep Trying"

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

Take some time to read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Following the guidelines in the article will allow people to help you easier.

😎

jay.l.100 Old Hand Points: 370 More actions · Answer 3

Thanks, im closer now. I had to replace the AND with an OR, but I am still missing one record. Ill investigate more tomorrow. 6 am comes quick! I do appreciate the help.

-jay lill

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Try this:

[font="Courier New"]SELECT em.statecode, COUNT(em.statecode) AS ct

FROM employer em

LEFT JOIN interview iv

ON iv.companyname = em.companyname

AND iv.division = em.division

WHERE iv.companyname IS NULL

GROUP BY em.statecode

ORDER BY em.statecode[/font]

It assumes that em.companyname is never null.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Try this

SELECT employer.statecode, COUNT(employer.statecode) AS ct

FROM employer

WHERE NOT EXISTS (SELECT *

FROM interview

WHERE employer.companyname=interview.companyname

AND employer.division=interview.division)

GROUP BY employer.statecode

ORDER BY employer.statecode

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

jay.l.100 Old Hand Points: 370 More actions · Answer 6

jay.l.100

Old Hand

Points: 370

January 23, 2008 at 9:13 pm

#771142

Both of the last two queries worked.

Thanks!