CODEWITHSUNDEEP
sqlselectcategories
ahlkristen
ahlkristen

Reputation: 11

Selecting multiple rows from specific categories in SQL

I want to be able to run a report that will give me a list of all the items in the User Computer, Server, and Virtual Machine categories.

I want to exclude all other categories from this report.

I'm not quite sure what to write in order to achieve this, or where to put it.

This is what I'm working with currently. I get an Error Code 1054: Unknown column for each of the categories though...

SELECT DISTINCT
ASSET.NAME AS ASSET_NAME,
ASSET_TYPE.NAME AS ASSET_TYPE,
ASSET_TYPE.ASSET_CATEGORY AS CATEGORY 
FROM ASSET, ASSET_TYPE
WHERE ASSET_CATEGORY IN (SERVERS, USER_COMPUTER, VIRTUAL_MACHINE)
ORDER BY ASSET.NAME;

EDIT: I made the suggested changes (from Codo), but now I'm getting 0 rows returned. Any further suggestions?

Upvotes: 1

Views: 1272

Answers (2)

Kaf
Kaf

Reputation: 33809

As @Codo said, your categories should be strings (varchar or nvarchar) and should be like

WHERE ASSET_CATEGORY IN ('SERVERS', 'USER_COMPUTER', 'VIRTUAL_MACHINE')

Also have not shown any relationship of two tables. Assuming the relation is;

ASSET.AssetTypeId = ASSET_TYPE.AssetTypeId

And avoiding WHERE ASSET, ASSET_TYPE type old style table joining, your query can be done with table aliases as;

SELECT a.NAME ASSET_NAME, at.NAME ASSET_TYPE, at.ASSET_CATEGORY CATEGORY 
FROM ASSET a JOIN ASSET_TYPE at ON a.AssetTypeId = at.AssetTypeId 
WHERE at.ASSET_CATEGORY IN ('SERVERS', 'USER_COMPUTER', 'VIRTUAL_MACHINE')
ORDER BY a.NAME;

Upvotes: 1

Codo
Codo

Reputation: 78835

Your categories most likely are string and thus need quotes around them:

SELECT DISTINCT
ASSET.NAME AS ASSET_NAME,
ASSET_TYPE.NAME AS ASSET_TYPE,
ASSET_TYPE.ASSET_CATEGORY AS CATEGORY 
FROM ASSET, ASSET_TYPE
WHERE ASSET_CATEGORY IN ('SERVERS', 'USER_COMPUTER', 'VIRTUAL_MACHINE')
ORDER BY ASSET.NAME;

Without quotes, the database thinks these words are column names and gets confused.

Furthermore, you query two tables but don't specify how they connect with either other, i.e. there is a join condition missing.

Most likely you need something like:

FROM ASSET, ASSET_TYPE
WHERE ASSET_CATEGORY IN ('SERVERS', 'USER_COMPUTER', 'VIRTUAL_MACHINE')
AND ASSET_TYPE.ID  =  ASSET.ASSET_TYPE_ID

Upvotes: 2

Related Questions