Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Invalid column name

Posted on 2007-03-27
8
Medium Priority
?
1,140 Views
Last Modified: 2008-01-09
think i have made a total mess of this query now, can anyone see what i am doing wrong?

Invalid column name 'manName'

            SELECT  m.manId, m.name as manName, p.manId, p.catId
            FROM products p, manufacturers m
            WHERE p.manId = m.manId
            AND p.catId='#URL.catId#'
            GROUP BY manName
0
Comment
Question by:pigmentarts
  • 4
  • 3
8 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 18798635
yes, the manName is field alias

It should be

            SELECT  m.manId, m.name as manName, p.manId, p.catId
            FROM products p, manufacturers m
            WHERE p.manId = m.manId
            AND p.catId='#URL.catId#'
            GROUP BY m.name
or

            SELECT * FROM (SELECT  m.manId, m.name as manName, p.manId, p.catId
            FROM products p, manufacturers m
            WHERE p.manId = m.manId
            AND p.catId='#URL.catId#') as T
            GROUP BY m.name

HuyBD;
0
 
LVL 17

Assisted Solution

by:HuyBD
HuyBD earned 800 total points
ID: 18798642
I have missing in 2nd query

            SELECT * FROM (SELECT  m.manId, m.name as manName, p.manId, p.catId
            FROM products p, manufacturers m
            WHERE p.manId = m.manId
            AND p.catId='#URL.catId#') as T
            GROUP BY manName
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 18798657
thanks for your reply, not sure if i am getting this, but i tried


          SELECT * FROM (SELECT  m.manId, m.name as manName, p.manId, p.catId
            FROM products p, manufacturers m
            WHERE p.manId = m.manId
            AND p.catId='#URL.catId#') as T
            GROUP BY manName

as posted and i get

[Macromedia][SQLServer JDBC Driver][SQLServer]Column 'm.manId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY


or this for the 2ed query


 [Macromedia][SQLServer JDBC Driver][SQLServer]The column 'manId' was specified multiple times for 'T'.

for

  SELECT * FROM (SELECT  m.manId, m.name as manName, p.manId, p.catId
            FROM products p, manufacturers m
            WHERE p.manId = m.manId
            AND p.catId='#URL.catId#') as T
            GROUP BY manName

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 17

Expert Comment

by:HuyBD
ID: 18798663
Please check table manufacturers for id field name!
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1200 total points
ID: 18798667
SELECT  m.manId, m.name as manName, p.manId, p.catId
            FROM products p
            Inner Join manufacturers m
                on  p.manId = m.manId
            Where p.catId='#URL.catId#'
            Order by m.name
 
you can't group by just one of the selected columns...

what are you actually trying to achieve?
which database system are you using?
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 18798671
manufacturers  table...

manId
imageUrl
description
name
contactName
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 18798682
you can't group by just one of the selected columns...
 so i would have to list them all out, would this not make it slow?


what are you actually trying to achieve?

to show a list of manufacturers based on the products displayed. so if they are ten products on screen they maybe linked to a manufacture if so display them.

which database system are you using?
MS SQL
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 18798691
i see what this is doing now,


            Inner Join manufacturers m
                on  p.manId = m.manId


so really there was no need for me to use a group by anyhow, cus if i get what the above is saying you are joining the two table up based on a matching manid? which is what i want.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question