CODEWITHSUNDEEP
sqlt-sqlschema
Donut
Donut

Reputation: 157

Help construct a query (schema provided)

Here is the schema for the database: https://i.sstatic.net/omX60.png

Which company has the least entitlements?

Please tell me how wrong my answer is.

select top 1 companyName
serialNumber_tbl as sn, Entitlement_tbl as ent, Company_tbl as c
where sn.serialNumberId = ent.serialNumberId
and c.companyId = sn.companyId
order by count(distinct entitlementId) asc
group by companyName

Thank you.

Upvotes: 1

Views: 67

Answers (3)

Andriy M
Andriy M

Reputation: 77657

The other answers have correctly pointed out the missing from keyword and the wrong order of the group by and order by clauses. There should be no problem about following the advice you got there.

One other thing is the syntax you are using to join the tables. Although technically not a mistake, it is considered outdated. The newer standard offers dedicated keywords for joining, which lets you separate your joining logic from your filtering-by-values logic, which previously, as per the older standard, had to be mixed in one clause, the WHERE clause.

Let me show this by rewriting the FROM and WHERE clauses for you, so you can see the difference. First, here's your current join logic implementation:

FROM serialNumber_tbl AS sn,
     Entitlement_tbl AS ent,
     Company_tbl AS c
WHERE sn.serialNumberId = ent.serialNumberId
  AND c.companyId = sn.companyId

Both conditions in the WHERE clause are join conditions. Here's how the same fragment of code changes when done with the newer syntax:

FROM serialNumber_tbl AS sn
  INNER JOIN Entitlement_tbl AS ent ON sn.serialNumberId = ent.serialNumberId
  INNER JOIN Company_tbl AS c ON c.companyId = sn.companyId

In this case the WHERE clause is gone entirely, of course, but you shouldn't have the wrong impression that it is always like that. Generally the WHERE clause remains valid and useful. If it contained some other conditions that were not part of the joining logic, they would retain there places in the WHERE clause. I'm talking about conditions like ent.Date < '19900101' AND c.CompanyName NOT IN ('IBM', 'Microsoft').

Like I said earlier, with the older syntax, which you are using presently, that condition would be in one clause with the join conditions. In simpler cases, like yours, it might be overlooked without problems, but in more complex queries it could become an issue, if not a nightmare, in terms of maintainability. So the point is to get used to the newer syntax before you start writing such complex queries.

Upvotes: 1

squawknull
squawknull

Reputation: 5191

select top 1 companyName
**from** serialNumber_tbl as sn, Entitlement_tbl as ent, Company_tbl as c
where sn.serialNumberId = ent.serialNumberId
and c.companyId = sn.companyId
group by companyName
order by count(distinct entitlementId) asc

Upvotes: 1

Tommi
Tommi

Reputation: 8608

Almost right, I suppose. Missing the FROM keyword, and GROUP BY must come before ORDER BY.

select top 1 companyName
from serialNumber_tbl as sn, Entitlement_tbl as ent, Company_tbl as c
where sn.serialNumberId = ent.serialNumberId
and c.companyId = sn.companyId
group by companyName
order by count(distinct entitlementId) asc

Upvotes: 1

Related Questions