Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: select query question?

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What has the instructor covered in class so far? Have you covered JOIN clauses yet?

    -PatP

  3. #3
    Join Date
    Aug 2004
    Posts
    3
    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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by OutterSpace
    Maybe you should read my question again
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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:
    Code:
    select t1.entity_id
      from mapper t1
    inner
      join mapper t2
        on t1.entity_id
         = t2.entity_id
     where t1.service_id = 5
       and t2.service_id = 6
    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:
    Code:
    select entity_id
      from mapper
     where service_id in (5,6)
    group
        by entity_id
    having count(distinct service_id) = 2
    which is a lot easier to extend to 3, 4, 5 values...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Posts
    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.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •