Solved

The opposite of the IN operator?

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now