TurnerP
asked on
SQL syntax with Distinct and All
I am presently using the below code to return a recordset for a DAO 3.6/VB6 project. It truncates the Abstract field since it is a memo field and I have found that we can't use DISTINCT with a memo field. Could someone please tell me how to alter the code to use SELECT DISTINCT for all the other fields and include the Abstract field without DISTINCT?
strSQL = "SELECT DISTINCT AuthorPub.MedlineAuthorNam e, Pub.Journal, Pub.Abstract, Pub.Citation , Pub.PubYear, Pub.Title, Pub.MedlineID From Pub, AuthorPub " _
& "WHERE Pub.medlineid = AuthorPub.MedlineID " _
& "AND (AuthorPub.MedlineAuthorNa me = '" & strAuthor & "') " _
& "ORDER BY Pub.PubYear DESC"
strSQL = "SELECT DISTINCT AuthorPub.MedlineAuthorNam
& "WHERE Pub.medlineid = AuthorPub.MedlineID " _
& "AND (AuthorPub.MedlineAuthorNa
& "ORDER BY Pub.PubYear DESC"
ASKER
OK, I understand the logic here, but I am rather new at this and don't understand subqueries and the proper syntax. I'll give you the 200 if you would please rewrite my above code incorporating the subquery so it could work. (I have a deadline and we are moving our home on Wednesday. I have packed my SQL book!)I hope you'll do this. Thanks.
try something like (this is for sql server don't know what you have)
"SELECT DISTINCT AuthorPub.MedlineAuthorNam e, Pub.Journal, Pub.Citation , Pub.PubYear,
Pub.Title, Pub.MedlineID, " _
& "Abstract = (select top 1 P2.Abstract from pub p2 where p2.id = pub.id), " _
& " From Pub, AuthorPub " _
& "WHERE Pub.medlineid = AuthorPub.MedlineID " _
& "AND (AuthorPub.MedlineAuthorNa me = '" & strAuthor & "') " _
& "ORDER BY Pub.PubYear DESC"
"SELECT DISTINCT AuthorPub.MedlineAuthorNam
Pub.Title, Pub.MedlineID, " _
& "Abstract = (select top 1 P2.Abstract from pub p2 where p2.id = pub.id), " _
& " From Pub, AuthorPub " _
& "WHERE Pub.medlineid = AuthorPub.MedlineID " _
& "AND (AuthorPub.MedlineAuthorNa
& "ORDER BY Pub.PubYear DESC"
ASKER
Great! Thanks for your help. We're getting there...
It returns the following SQL statement and a runtime error 3342 (Invalid memo, OLE or Hyperlink Object in subquery 'P2.Abstract'
SELECT DISTINCT AuthorPub.MedlineAuthorNam e, Pub.Journal, Pub.Citation , Pub.PubYear, Pub.Title , Pub.MedlineID, Abstract = (select top 1 P2.Abstract from pub p2 where p2.id = pub.id) From Pub, AuthorPub WHERE Pub.medlineid = AuthorPub.MedlineID AND (AuthorPub.MedlineAuthorNa me = 'Amstey MS') ORDER BY Pub.PubYear DESC
It returns the following SQL statement and a runtime error 3342 (Invalid memo, OLE or Hyperlink Object in subquery 'P2.Abstract'
SELECT DISTINCT AuthorPub.MedlineAuthorNam
ASKER
According to the documentation, you can't use a memo field in a subquery. Is there a way around this?
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
- PAQ'd and pts refunded
Please leave any comments here within the
next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !
Nic;o)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could use a subquery to get back the data for the memo field and a group by for all the others.
SELECT AuthorPub.MedlineAuthorNam
Pub.Title, Pub.MedlineID ,
Abstract = (select P2.Abstract from pub p2 where p2.id = pub.id)