SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Distinct problem

    Hello,

    I have a little problem with this query:

    Code:
     SELECT DISTINCT a.evenementId, a.evenementNaam, a.evenementType, b.vrstZaal, b.vrstDatum FROM evenementen a, voorstelling b WHERE 1=1 AND a.evenementId=b.vrstEvenementId ORDER BY a.evenementNaam ASC
    I want to select unique evenementId's, but this query selects double evenementId's, it's just like the 'distinct' is ignored...

    Tables:
    Code:
     CREATE TABLE evenementen (
      evenementId int(7) NOT NULL auto_increment,
      evenementNaam varchar(80) NOT NULL default '',
      evenementType char(1) NOT NULL default '',
      PRIMARY KEY  (evenementId)
    ) TYPE=MyISAM;
    
    CREATE TABLE voorstelling (
      vrstId int(7) NOT NULL auto_increment,
      vrstEvenementId int(7) NOT NULL default '0',
      vrstZaal int(7) NOT NULL default '0',
      vrstDatum varchar(10) NOT NULL default '',
      vrstTijd varchar(5) NOT NULL default '',
      vrstPrijs int(5) NOT NULL default '0',
      PRIMARY KEY  (vrstId)
    ) TYPE=MyISAM;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, the distinct is not ignored

    distinct applies to the entire result row

    these rows are distinct --
    Code:
    Id Naam Type Zaal Datum
     1 tom  boy  foo  123
     1 tom  boy  foo  456
     1 tom  boy  foo  789
     1 tom  boy  bar  555
     1 tom  boy  bar  666
    if you wish only one result row for each evenementen row,
    you must either decide which voorstelling row you want with it
    (by specifying some condition on the voorstelling columns), or else
    use some aggregate on the voorstelling rows, e.g. count
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there a way to get a resultset without double evenementNaam's?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that depends on what you mean

    the result set of the join is

    aaa 101
    aaa 102
    aaa 103
    bbb 102
    bbb 104
    ccc 107

    if you are asking how to get it to come back like this --

    aaa 101
    --- 102
    --- 103
    bbb 102
    --- 104
    ccc 107

    then the answer is, don't do that with sql

    if you want this instead --

    aaa 101
    bbb 102
    ccc 107

    then you have chosen a specific row for each name with something to go along with it, in my example here the lowest number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    if you want this instead --

    aaa 101
    bbb 102
    ccc 107

    then you have chosen a specific row for each name with something to go along with it, in my example here the lowest number
    That's exactly what I want, but I'm not very familiar with 'count' in mysql... Is it possible to give an example of that? Thank you very much.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select evenementId
         , evenementNaam 
         , min(vrstZaal) as foo
      from evenementen 
    inner
      join voorstelling
        on evenementId 
         = vrstEvenementId
    group
        by evenementId
         , evenementNaam
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you!
    It works fine.


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
  •