Solved

The opposite of the IN operator?

Posted on 2006-06-29
5
433 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 125 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 28
Oracle DB monitor SW 21 48
insert wont work in SQL 14 21
transaction in asp.net, sql server 6 32
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

776 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