• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

The opposite of the IN operator?

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
jeremyduj
Asked:
jeremyduj
1 Solution
 
MageDribbleCommented:
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
 
Einstine98Commented:
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
 
dstanley9Commented:
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
 
Einstine98Commented:
IN queries will perform slowly on large tables.... also sub queries should be avoided if possible...
0
 
imran_fastCommented:
/*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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now