| Author |
 Topic  |
|
|
TStewartFan
Junior Member
190 Posts |
 Posted - 13 March 2005 : 10:32:59
|
I am having a slight problem with dynamically populating a pulldown list with values form my Access database. I am trying to display results without the duplicate entires but when my sql executes all fields found in the db are pulled and not just the SELECT DISTINCt. Can someone help with the following sql statement:
strSql = "SELECT DISTINCT " & strTablePrefix & "MUSIC.M_ID"
strSql = strSql & ", " & strTablePrefix & "MUSIC.M_ARTIST"
strSql = strSql & ", " & strTablePrefix & "MUSIC.M_MEMBER_ID"
strSql = strSql & " FROM " & strTablePrefix & "MUSIC "
strSql = strSql & " WHERE " & strTablePrefix & "MUSIC.M_MEMBER_ID = 0 "
strSql = strSql & " ORDER BY " & strTablePrefix & "MUSIC.M_ARTIST ASC;"
set rsMusic = Server.CreateObject("ADODB.Recordset")
rsMusic.open strSql, my_Conn, 3
artistRows = rsMusic.getRows
rsMusic.Close
Set rsMusic = nothing |
Edited by - ruirib on 13 March 2005 16:31:43 |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 13 March 2005 : 11:59:03
|
So you've got:
SELECT DISTINCT [tblprefix]MUSIC.M_ID,
[tblprefix]MUSIC.M_ARTIST,
[tblprefix]MUSIC.M_MEMBER_ID,
FROM [tblprefix]MUSIC
WHERE [tblprefix]MUSIC.M_MEMBER_ID = 0
ORDER BY [tblprefix]MUSIC.M_ARTIST ASC;
Looks like you're asking it to select all the distinct ID numbers (MUSIC.M_ID). If the ID field is unique, then all the records will be returned.
Also, aince all your fields come from the same table, you shouldn't need to specify the table alias in your select fields or order by, or where clauses:
SELECT DISTINCT M_ID,
M_ARTIST,
M_MEMBER_ID,
FROM [tblprefix]MUSIC
WHERE M_MEMBER_ID = 0
ORDER BY M_ARTIST ASC; |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
 |
|
|
TStewartFan
Junior Member
190 Posts |
Posted - 13 March 2005 : 12:50:09
|
Thanks. I have actually fixed issue number one just to bring to surface issue number 2. My select distinct and group by function now works but my when selected function fails because it is being grouped. However it is no longer an sql error so its back to web surfin I go...heh By the way the new sql statement looks like this:
strSql = "SELECT DISTINCT M_ARTIST, SUM(M_ID)"
strSql = strSql & " FROM " & strTablePrefix & "MUSIC "
strSql = strSql & " GROUP BY M_ARTIST"
strSql = strSql & " ORDER BY M_ARTIST ASC;" |
Edited by - TStewartFan on 13 March 2005 12:55:02 |
|
|
| |
Topic |
|
|
|
Topic Locked
