Professional Web Applications Themes

SQL Join Statement help - ASP Database

I am working on a page that will display a list of manufacturers that we have available. I want to check against the products table to verify if a manufacturer has products in the database or not. I do not want any information from the products table, only to return a count or boolean if products exist. Is there a better way to do this? Please see the SQL statement and related error it is returning. select manufacturers.id, manufacturers.company, manufacturers.[url], products.count(*) FROM manufacturers LEFT JOIN products ON manufacturers.company=products.manfname ORDER BY manufacturers.company Microsoft OLE DB Provider for SQL Server error '80040e14' ...

  1. #1

    Default SQL Join Statement help

    I am working on a page that will display a list of manufacturers that we have
    available. I want to check against the products table to verify if a
    manufacturer has products in the database or not. I do not want any information
    from the products table, only to return a count or boolean if products exist.

    Is there a better way to do this?

    Please see the SQL statement and related error it is returning.

    select manufacturers.id, manufacturers.company, manufacturers.[url],
    products.count(*) FROM manufacturers LEFT JOIN products ON
    manufacturers.company=products.manfname ORDER BY manufacturers.company

    Microsoft OLE DB Provider for SQL Server error '80040e14'
    Line 1: Incorrect syntax near '*'.
    /manufacturer_listing.inc, line 43

    Cary Guest

  2. #2

    Default Re: SQL Join Statement help

    Cary wrote: 

    This is because you cannot use a prefix (the table name) for an aggregate
    function. It should be:
    count(*)
    or
    count(product.columnname)

    However, once you fix this, there will be a different error:

    aggregate function found with no GROUP BY clause

    or something to that effect.

    You can't use count(*) in a select clause that contains other column names,
    without putting those column names in a GROUP BY clause:
    Like this (I prefer to use table aliases for readability):

    select m.id, m.company, m.[url],
    count(p.manfname)
    FROM manufacturers m LEFT JOIN products p ON
    m.company=p.manfname
    GROUP BY m.id, m.company, m.[url]
    ORDER BY m.company

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  3. #3

    Default Re: SQL Join Statement help

    Thank you Bob, that works well. I thought that I needed a group by clause, but
    was not sure how.

    Do you have any suggestions on good books or Web resources for more information
    on SQL queries. All I have been able to find shows only very basic SQL information.

    Thank you!



    Bob Barrows [MVP] wrote:
     
    >
    >
    > This is because you cannot use a prefix (the table name) for an aggregate
    > function. It should be:
    > count(*)
    > or
    > count(product.columnname)
    >
    > However, once you fix this, there will be a different error:
    >
    > aggregate function found with no GROUP BY clause
    >
    > or something to that effect.
    >
    > You can't use count(*) in a select clause that contains other column names,
    > without putting those column names in a GROUP BY clause:
    > Like this (I prefer to use table aliases for readability):
    >
    > select m.id, m.company, m.[url],
    > count(p.manfname)
    > FROM manufacturers m LEFT JOIN products p ON
    > m.company=p.manfname
    > GROUP BY m.id, m.company, m.[url]
    > ORDER BY m.company
    >
    > Bob Barrows[/ref]
    Cary Guest

  4. #4

    Default Re: SQL Join Statement help

    I would just go to Amazon and look for books about Transact-SQL. I've heard
    good things about Ken Henderson's books.

    Bob Barrows
    Cary wrote: 

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. JOIN vs no JOIN
    By Paul Lautman in forum MySQL
    Replies: 2
    Last Post: May 19th, 01:15 PM
  3. Comma Seperation List and Join Statement
    By Flashm@n in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 13th, 04:19 PM
  4. Need help with SQL statement for JOIN-type stuff
    By Mike in forum ASP Database
    Replies: 13
    Last Post: August 16th, 10:36 AM
  5. DB2 join
    By Prakash in forum IBM DB2
    Replies: 1
    Last Post: September 10th, 05:24 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139