Cheers to all,

I use mysql version 4.0.16

I have a table with these data

service_id-----------entity_id
---5---------|--------1------
---6---------|--------1------
---5---------|--------2------
---6---------|--------3------


What I need to select:
-----------------------

All entity_id's with service_id's equal to 5 and 6.
(or all entities that have both service 5 and service 6)
In this case this is entity_id=1 (but not 2 and 3).

Is there a way to do this?
And how would I do it?

I've tried to search and read up on it, but frankly I have not found anything.

I have a strong feeling that this cannot be done, unless you go into code and have logic there.
I'd also gladly do it with temp table, but that too didnt inspire a solution from me.

The only way I thought of would be with GROUP_CONCAT, but that is cheesy and my mysql version doesent support it.

Please Help,
Thanks a lot
Damjan

PS if for some reason you need sql for table and data:
INTRO:
-------
I have table mapper:

CREATE TABLE mapper (
service_id integer NOT NULL,
entity_id integer NOT NULL
)
GO

with data:

INSERT INTO mapper (service_id, entity_id) VALUES (5, 1);
go
INSERT INTO mapper (service_id, entity_id) VALUES (6, 1);
go
INSERT INTO mapper (service_id, entity_id) VALUES (5, 2);
go
INSERT INTO mapper (service_id, entity_id) VALUES (6, 3);
go

to view data:

SELECT
m.service_id,
m.entity_id
FROM mapper AS m
ORDER BY m.entity_id

What has the instructor covered in class so far? Have you covered JOIN clauses yet?

-PatP

Hm, I do not know why the cynicism,
but I do know how to use joins and if nothing else I have ONE table here.
And unless you think I should use self join (which I dont think you are, but if you do please make a concrete suggestion) it makes no sense. Anyway self join dont make no sense either.

Maybe you should read my question again,

Thanks for reply anyway
Damjan

It still looks the same.

The self-join is a good idea. You are trying to check values in two different rows, so it makes sense to treat them as two different tables.

I don't do homework, but I'll usually give a couple of good clues.

-PatP

i have to agree, this is not a "beginner sql" question

it's more of a "sql 201" question, because the answer is not forthcoming with a simple SELECT

no matter how the beginner tries to write it, with ANDs and/or ORs, a simple query won't solve the problem

that's because we are actually looking for the presence of more than one (certain type of) row

damjan, you were so close, yes, you can do it with a self-join:
but this quickly becomes tedious as you want more values

the other way to do it is to select all the different qualifying rows, one by one, and then later, in the grouping, count how many different ones you got:
which is a lot easier to extend to 3, 4, 5 values...

f.y.i. damjan, "go" is not valid in mysql

your series of inserts should read like this --

INSERT INTO mapper (service_id, entity_id)
VALUES (5, 1), (6, 1), (5, 2), (6, 3)
;

Thanks for time and answer,
the second query is what I really needed, and what I couldnt come up with
by myself.

(as for go I know, I use winsql wich uses it, sorry about it.)