CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Codex73
Codex73

Reputation: 5766

SQL Query Problem

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

Upvotes: 1

Views: 60

Answers (4)

bobs
bobs

Reputation: 22184

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

Upvotes: 1

twip
twip

Reputation: 648

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.)

Upvotes: 1

Ryan Gross
Ryan Gross

Reputation: 6515

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

Upvotes: 0

asthasr
asthasr

Reputation: 9397

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.

Upvotes: 1

Related Questions