SQL Problem

Results 1 to 10 of 10

Thread: SQL Problem

  1. #1
    Join Date
    Dec 1969
    Posts
    36

    Default SQL Problem

    I have a database of paintings and I tried to query the following fields: Pt_Designer(artist name), pt_filename(the filename of the image)and YesNo. each Artist has a few diffrenet paintings in the database. <BR><BR>I am trying to build a page that will display all the names of the artists whose YesNo value is true and their names start with a specfic letter along with an image of their artwork(only one image of each) <BR><BR>My problem is that I am unable to create a query that will find only one of each - I tried using every option that I can imagine but I can&#039t find the answear, I even made this query work in access - went into SQL view, cut and pasted it into my asp page and it still didn&#039t work! <BR><BR>could you please give a direction?<BR><BR><BR>Thanks<BR>Ariel<BR><BR><BR>t he following is the code that works in access but not in the ASP Page <BR><BR>SELECT Picture.Pt_Designer, First(Picture.pt_filename) AS FirstOfpt_filename, Picture.YesNo <BR><BR>FROM Picture<BR><BR><BR>GROUP BY Picture.Pt_Designer, Picture.YesNo <BR><BR>HAVING (((Picture.Pt_Designer) Like "w%") AND ((Picture.YesNo)=True));<BR><BR><BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    511

    Default RE: SQL Problem

    The answer for this one kind of depends on how you have structured your tables and what data you have put where. I&#039ll give you the one I think you&#039ll need, but if not, it should at least give you a helping hand in the right direction.<BR><BR>SELECT DISTINCT Pt_Designer, Pt_Filename, YesNo<BR>FROM Pictures<BR>WHERE Pt_Designer LIKE "w%" AND YesNo = True<BR><BR>Hope that helps<BR><BR>Pete

  3. #3
    Join Date
    Dec 1969
    Posts
    36

    Default RE: SQL Problem

    It&#039s only one table but the problem is that each of the filenames is diffrenet and therefor it picks up all the records....

  4. #4
    Join Date
    Dec 1969
    Posts
    511

    Default RE: SQL Problem

    By using the DISTINCT keyword with Pt_Designer you remove all the repeated data. So if an artist has 10 paintings in the database only one record for that artist will be returned (I think its the first one) rather then all 10. So if you only have one artist they should only be one Pt_Filename with it! This is assuming you have set your table up something like this.<BR><BR>Pt_Designer, Pt_Filename, YesNo<BR><BR>Bob Smith - Egg on Face - Yes<BR>Bob Smith - Monalisa - Yes<BR>Bob Smith - Fish Head - Yes<BR>John Doe - Ham Salad - No<BR>etc...<BR><BR>Did you try my last query? That should work, but again it depends on how you have set up your data, assuming you hae done it something like I have above it will work.<BR><BR>Hope that helps<BR><BR>Pete


  5. #5
    Join Date
    Dec 1969
    Posts
    36

    Default RE: SQL Problem

    Hi Pete,<BR>that&#039s the way that my table is set up but the problem is that it&#039s picking up the distinct record and beacuse I&#039m telling it SELECT DISTINCT Pt_Designer, Pt_Filename, YesNo and there are diffrenet filename then each record is recognized as distinct and thats the problem!<BR>In access there is something called FIRSTOF which might have solved this problem if only if worked in the asp page...<BR><BR>

  6. #6
    Join Date
    Dec 1969
    Posts
    511

    Default RE: SQL Problem

    Ok then we&#039ll have to try a nested SQL statement.<BR><BR>SELECT *<BR>FROM Pictures<BR>WHERE Pt_Designer = <BR> (SELECT DISTINCT Pt_Designer<BR> FROM Pictures)<BR><BR>That will do it, I hope :)<BR><BR>Sorry for the delay BTW.<BR><BR>Pete

  7. #7
    Join Date
    Dec 1969
    Posts
    36

    Default RE: SQL Problem

    Hi Pete...<BR>Thanks for the help but I tried using this expression in the past but what I get is:<BR>Microsoft OLE DB Provider for ODBC Drivers error &#039 80004005&#039 <BR><BR>[Microsoft][ODBC Microsoft Access Driver] At most one record can be returned by this subquery. <BR><BR>also this expression is missing the "Like" element that I need<BR><BR>Ariel<BR><BR><BR><BR>

  8. #8
    Join Date
    Dec 1969
    Posts
    511

    Default RE: SQL Problem

    Bummer. Well I&#039m all out then, I&#039m sure that last one should have worked, you can add the LIKE statement on quite easily :<BR><BR>SELECT *<BR>FROM Pictures<BR>WHERE Pt_Designer =<BR> (SELECT DISTINCT Pt_Designer<BR> FROM Pictures<BR> WHERE Pt_Designer LIKE "W%")<BR><BR>I really do think it should work. Maybe there is something up with your data or table structure? I&#039d really like to beat this one. Can you email me a sample of your db? I&#039ll have a look at it for you, might have to wait til Monday now though :)<BR><BR>Thanks<BR><BR>Pete

  9. #9
    Join Date
    Dec 1969
    Posts
    36

    Default RE: SQL Problem

    I&#039ll be more then glad to send you the files - what&#039s yoour email address?<BR><BR>Ariel<BR>ariel@art-mine.com

  10. #10
    Join Date
    Dec 1969
    Posts
    511

    Default RE: SQL Problem

    Doh! Sorry, I&#039d forget my head yadda yadda yadda...<BR><BR>dunpa@hickson.co.uk<BR><BR>See you Monday.<BR><BR>Pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •