Solved

Invalid column name

Posted on 2007-03-27
8
1,126 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
LVL 17

Expert Comment

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

Accepted Solution

by:
Lowfatspread earned 300 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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