SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Estonia
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Don't know, how to select data

    I have 2 tables
    Code:
    create table sem_opjaaine(
      opID int unsigned not null,
      aineID varchar(255) not null
    );
    
    create table sem_ained(
      kood varchar(255) not null,
      nimi varchar(255),
      teade varchar(255),
      PRIMARY KEY(kood)
    );
    and data in tables
    Code:
    mysql> select * from sem_opjaaine;
    +------+-------------+
    | opID | aineID      |
    +------+-------------+
    |    5 | MTAT.01.001 |
    |    1 | AABB.32.045 |
    |    1 | MTAT.01.001 |
    |    1 | AKAK.01.001 |
    +------+-------------+
    
    mysql> select * from sem_ained;
    +-------------+------------------+-------+
    | kood        | nimi             | teade |
    +-------------+------------------+-------+
    | MTAT.01.001 | Ati aine         | NULL  |
    | AKAK.01.001 | Mingisugune aine | NULL  |
    | AABB.32.045 | Kolmas Proov     | NULL  |
    +-------------+------------------+-------+
    For example, let opID be 1.
    I can't figure out select sentence to select rows where opID != 1 and aineID = kood and only get those aineID's, whose opID != 1.
    So when opID is 1, i should get nothing, when opID is 5, is should get this:
    Code:
    +------+-------------+-------------+
    | opID | aineID      | kood        |
    +------+-------------+-------------+
    |    1 | AKAK.01.001 | AKAK.01.001 |
    |    1 | AABB.32.045 | AABB.32.045 |
    +------+-------------+-------------+
    This is as clear as i can explane it
    Thanks,
    Sander

  2. #2
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well you almost said it yourself
    Code:
    SELECT a.opID, a.aineID, b.kood FROM sem_opjaaine AS a, sem_ained AS b WHERE a.aineID = b.kood AND a.opID != 1
    this will return lines where from both tables where aineID = kood and opID is not 1.

    Hope that helps!
    - website

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Estonia
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by website
    well you almost said it yourself
    Code:
    SELECT a.opID, a.aineID, b.kood FROM sem_opjaaine AS a, sem_ained AS b WHERE a.aineID = b.kood AND a.opID != 1
    this will return lines where from both tables where aineID = kood and opID is not 1.

    Hope that helps!
    Thanks for answering, but thats not what i need, this line you gave shows one row, but it should show nothing.

    It's like: first I select aineID and kood, if there is 2 similar rows, I throw away both and from result I filter out rows where opID 1.

  4. #4
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    always when I'm about to realise what you want it floats out into the deep part of my brain. This is at least how I now think it is.

    Eg, if I choose opID as 5 then it should return opID and aineID WHERE aineID in any row that has opID = 5 does NOT match any aineID in the other opID's ?
    If I under stand this correctly I do not have a clue how to select this, but I am going to pm one of the SQL masters of the forum (r937) to take a look at this.

    Anyway, this is all I can do and I really do hope this gets solved!
    - website

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Estonia
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by website
    Eg, if I choose opID as 5 then it should return opID and aineID WHERE aineID in any row that has opID = 5 does NOT match any aineID in the other opID's ?
    Yes, I think that is what I want. I tried RIGHT JOIN, but no luck.

  6. #6
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Estonia
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I got answer from IRC and it is:
    Code:
    SELECT a.opID, a.aineID FROM sem_opjaaine as a LEFT JOIN sem_opjaaine AS table2 ON a.aineID=table2.aineID
    AND a.opID != table2.opID WHERE a.opID=1 AND table2.aineID IS NULL;
    Thanks for Shustrik
    So the problem is solved(I hope)


Bookmarks

Posting Permissions

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