Limiting resultset to match column with only certain values

Question

Post reply

Limiting resultset to match column with only certain values

scfead1

Valued Member

Points: 71
More actions
May 22, 2010 at 7:45 pm

#143171

Hi,
I'm trying the following query in sql server:
There is a person table where each person has many vehicles.
I want to return only the persons that have these vehicle types (05,10,15).
If a person has 05 and 20 then I dont want that person in the resultset.
I tried using the IN clause but that works like an OR condition. So if there is a 20 and 05 , it still returns the person.
Can someone tell me if there is any alternative to get the resultset mentioned above?
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

scfead1 Valued Member Points: 71 More actions · Answer 1

I should have added this in my previous post:

I also want the resultset to contain persons with even one vehicle from that list.

The following are acceptable:

05,10,15

05

05,15

10,15

etc...

Lowell SSC Guru Points: 323518 More actions · Answer 2

WHERE vehicle_types IN (05,10,15)

AND vehicle_types NOT IN (20)?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

scfead1 Valued Member Points: 71 More actions · Answer 3

I tried that IN (05,10,15) but if the person has 05 and 20 , it returns that row as well.

Does IN work like this? choose rows where vehicle - 05 or vehicle = 10 or vehicle = 15

scfead1 Valued Member Points: 71 More actions · Answer 4

the problem is that vehicle_types not in (20) has a number of values that I don't know for the NOT condition. So I'm trying to check only with the vehicles that I know.

the exact query:

select * from persons, vehicles where persons.id = vehicles.id and vehicles.type IN (05,10,15)

not sure how to exclude the unacceptable (unknown) vehicle types from the above list

Lowell SSC Guru Points: 323518 More actions · Answer 5

how about selecting all the non-5.10.15 values so they can be eliminated?

WHERE vehicle_types IN (05,10,15)

AND vehicle_types NOT IN (SELECT vehicle_types FROM SOMETABLE WHERE vehicle_types NOT IN (05,10,15))?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

scfead1 Valued Member Points: 71 More actions · Answer 6

my data is like this (when you join Person and Vehicle):

Person Id VehicleType Transatcion seq

A1 20 1

A1 05 1

A1 20 2

A1 05 2

Query:

select * from persons

where vehtype in (05,10,15)

and vehtype not in (select * from vehicle, person where person.id = vehicle.id and vehtype not in (05,10,15))

and transaction_seq in (select max(transaction_seq) from person group by person.id)

Executing the above for more than 200,000 records made it v slow and it ran for almost an hour. I just cancelled the query so I dont know if it would have even returned any results.

Is there a more efficient way to do this?

goodguy SSCertifiable Points: 5456 More actions · Answer 7

For the life of me, I cannot write this down, since I have not touched T-SQL for over two years, although I have the concept in my head:

Try this:

1. Write a select query on Persons with a self-join where vehicle_type field is 05 and count of the subquery is 1.

2. Write separate select queries for the other individual values, 10 and 15.

3. Repeat for a combination pair where vehicle_type = 05 OR vehicle_type = 10 and subquery count =2.

4. Do NOT use the AND operator as your vehicle_types are stored singly in the table.

4. Ditto for combination pairs (05,15) and for (10,15).

5. Lastly, write one more query to test for all three values vehicle_type = 05 OR vehicle_type = 10 ORvehicle_type = 15 and subquery count = 3.

6. Append all results using UNION ALL.

This is the best I can come up with off the top of my head, although I am sure there are many experts on this site who can design much more elegant solutions to such problems.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

goodguy SSCertifiable Points: 5456 More actions · Answer 8