SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
Thread: Distinct problem
-
May 6, 2004, 04:04 #1
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
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;
-
May 6, 2004, 05:52 #2
- 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
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
-
May 6, 2004, 07:27 #3
Is there a way to get a resultset without double evenementNaam's?
-
May 6, 2004, 09:33 #4
- 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
-
May 7, 2004, 01:20 #5
Originally Posted by r937
-
May 7, 2004, 03:38 #6
- 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
-
May 7, 2004, 03:50 #7
Thank you!
It works fine.
Bookmarks