x
Solved

# The opposite of the IN operator?

Posted on 2006-06-29
Medium Priority
446 Views
Hi - instead of using an IN operator, that gets values that are in both fields specified  (adds them), what operator do you use to get values that are only in both fields specified (subtracts them)?

For ex, imagine this table:

name        type
-------       ------
ford           van
toyota        van
toyota        car
toyota        SUV
toyota        truck

I want to do a sql query that gives me what is common between a "ford" and a "toyota" -- which should result in the correct answer, "van". This would all be in one table.

Thank you!
0
Question by:jeremyduj

LVL 5

Accepted Solution

MageDribble earned 375 total points
ID: 17013197
Could you have multiple instances of one record?  Or is each line unique?

ex/

Ford    van
Toyota van
Ford   van

If each line is unique try:

SELECT type
FROM table
GROUP BY type
HAVING COUNT(*) > 1
0

LVL 12

Expert Comment

ID: 17013309
You can also do

SELECT T1.[NAME], T1.TYPE
FROM TAble AS T1
JOIN TABLE AS T2
ON T1.Type = T2.Type
AND T1.Name <> T2.Name

if you write an exact example of what you are trying to return that would be better... thanks
0

LVL 25

Expert Comment

ID: 17013411
select distinct type from table
WHERE type IN
(SELECT type from table WHERE name = 'ford')
AND type IN
(SELECT type from table WHERE name = 'toyota')

OR

SELECT a.type from table a
INNER JOIN (SELECT type from table WHERE name='toyota') B
ON a.type = b.type
WHERE a.name = 'ford'
0

LVL 12

Expert Comment

ID: 17013458
IN queries will perform slowly on large tables.... also sub queries should be avoided if possible...
0

LVL 28

Expert Comment

ID: 17025679
/*This will give you All the Vendors having Common type */

select * from YourTable A where exists

(Select count(B.Type) from YourTable B where A.Name = B.Name group by B.Type Having Count(B.Type) > 1)
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.