[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Invalid column name

Posted on 2007-03-27
8
Medium Priority
?
1,134 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

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.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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