Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

The opposite of the IN operator?

Posted on 2006-06-29
5
Medium Priority
?
442 Views
Last Modified: 2012-08-13
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
Comment
Question by:jeremyduj
5 Comments
 
LVL 5

Accepted Solution

by:
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

by:Einstine98
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

by:dstanley9
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

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

Expert Comment

by:imran_fast
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question