Select question

[Nimh]

Hello everyone,

This is my first post as I just don't know how to google this.

So for example we have table like this:

Item    Paramater
trololo 1
trololo 2
trololo 3
trololo 4
trololo 5
trololo 6
moo     1
moo     3
moo     4
dsl     1
dsl     2
dsl     3

For example I need a list of items which have 1, 2 and 3. I'm looking for a SELECT that will return me the list of such items:

trololo
dsl

I would appreciate any suggestions or how to google this. Thank you in advance.

 

[pr2-eugin]

Hello Nimh, Welcome to AWF.

What you need is a SELECT statement with a WHERE condition. Learn how to use SELECT data from the Microsoft tutorial.

 

[plog]

Actually, this is requires something a little more complex than a simple SELECT query. You're going to need a sub-query.

First you need a query to limit your data to just those distinct Item/Paramater permutations with 1, 2 or 3 in their Paramater field. This is that SQL:

SELECT Item, Paramater 
FROM YourTableNameHere 
WHERE Paramater>=1 AND Paramater<=3
GROUP BY Item, Paramater

Replace 'YourTableNameHere' with your actual table's name. Then, name that query 'subQ'. Now, from that data source you need to limit it to Item values that appear exactly 3 times (Paramater=1, Paramater=2 and Paramater=3). This SQL will do that:

SELECT Item FROM subQ WHERE COUNT(Item)=3 GROUP BY Item

That query will give you the results you want.

 

[Nimh]

Paul, plog, thank you for the responses.

There I thought that simplifying my requirements will help me out with the actual task. But I've spent 3 more hours on this and couldn't come to a solution.

So I have more something like this actually:

tblItems

item  parameter
item1 wheels
item1 seats
item1 engine
item1 small
item2 seats
item2 engine
item2 wings
item2 big
item3 engine
item3 wheels
item3 seats
item3 big

tblTypes

type    parameter
vehicle wheels
vehicle seats
vehicle engine
car     wheels
car     seats
car     engine
car     small
bus     wheels
bus     seats
bus     engine
bus     big
plane   seats
plane   engine
plane   wings
plane   big

And I'm looking for a query which would result in:

vehicle item1
vehicle item3
car     item1
bus     item3
plane   item2

Hopefully that's clear enough. Is it even possible?
Any help is appreciated, thank you!