Solved

Invalid column name

Posted on 2007-03-27
8
1,117 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 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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