I'm trying to select from two tables using join and then grouping by type. How do I escape 'active'?
It's throwing this error on Query window (management studio):
Error
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'active'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'zone'.
Query
select
vendor.name,type_zone.type as zone
from dbo.vendor join dbo.type_zone
on fkType_zoneID = pkType_ZoneID
where active = 1
GROUP BY zone
Your errors mean that the "active" column name exists in both tables. You need to specify it as type_zone.active or vendor.active in your tests, to identify which table should have its column of that name tested.
Your GROUP BY clause is flawed because you have no aggregate function in your SELECT clause.
ORDER BY is what you're looking for. GROUP BY is used for giving sums or averages per date, or counts of records by geographical area, etc.Qualify the active column with a tablename:
vendor.active or type_zone.active
(I'm not sure which of those tables the active column refers to. That's why the server gives you the error; it can't tell either.)
The column active appears in both tables. You need to identify which active column you want to use. For example
WHERE vendor.active = 1
You can't use the column alias in the GROUP BY clause. Change zone to type_zone.type
It depend s on which table active is in. If it is in vendor, then the following should work:
select
vendor.name,type_zone.type as zone
from dbo.vendor inner join dbo.type_zone
on fkType_zoneID = pkType_ZoneID
where dbo.vendor.active = 1
GROUP BY type_zone.type
It is easier to deal with this if you alias your tables, as in the following:
select
v.name,t.type as zone
from dbo.vendor v inner join dbo.type_zone t
on v.fkType_zoneID = t.pkType_ZoneID
where v.active = 1
GROUP BY t.type
