Distinct problem

[iBram007]

Hello,

I have a little problem with this query:

 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:

 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;

[r937]

actually, the distinct is not ignored

distinct applies to the entire result row

these rows are distinct --

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

[iBram007]

Is there a way to get a resultset without double evenementNaam's?

[r937]

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

[iBram007]

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.

[r937]

select evenementId
     , evenementNaam 
     , min(vrstZaal) as foo
  from evenementen 
inner
  join voorstelling
    on evenementId 
     = vrstEvenementId
group
    by evenementId
     , evenementNaam

[iBram007]

Thank you!
It works fine.