asked on

Invalid column name

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

HuyBD

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;

SOLUTION

HuyBD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pigmentarts

ASKER

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

HuyBD

Please check table manufacturers for id field name!

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pigmentarts

ASKER

manufacturers table...

manId
imageUrl
description
name
contactName

pigmentarts

ASKER

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

pigmentarts

ASKER

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.