Post reply

Limiting resultset to match column with only certain values

  • 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!

  • May 22, 2010 at 7:49 pm

    #1171260

    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...

  • May 22, 2010 at 8:43 pm

    #1171264

    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!

  • May 22, 2010 at 8:45 pm

    #1171266

    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

  • May 22, 2010 at 8:47 pm

    #1171267

    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

  • May 22, 2010 at 8:51 pm

    #1171268

    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!

  • May 22, 2010 at 9:13 pm

    #1171269

    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?

  • May 23, 2010 at 3:10 am

    #1171279

    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

  • May 23, 2010 at 3:13 am

    #1171280

    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

  • May 24, 2010 at 10:54 am

    #1171675

    select * from person where exists

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

    and not exists

    (select * from vehicle where vehicle.personid = person.personid and vehicle = 50)

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

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