SQL Join Problem

[Thomas Brown]

I'm having a bit of a SQL problem and I can't figure it out. I have a
working solution, but it could be better.

I've got two tables, tblInspections and tblViolations. Each inspection
record has an associated establishment id. Each inspection record can have
multiple violation records associated with it. Each violation can be either
of type 1 (critical) or 0 (noncritical).

What I want is to get a list of inspections by establishmentid with a column
for the count of noncritical violations, and a count of critical violations.

EG.

Date NonCriticalViolations CriticalViolations

5/30/2003 6 2

Here is my SQL and what it returns.

----------------------------------------------------------------------------
----------------------------------------------------------------

select

tblInspections.ReceivedByDate,

tblViolations.Type,

count(tblViolations.Type) as ViolationCount

from

tblinspections

join tblViolations on tblInspections.ID = tblviolations.InspID

where tblInspections.iestabid = 100

group by

tblViolations.Type,

tblInspections.ReceivedByDate

----------------------------------------------------------------------------
----------------------------------------------------------------

and it returns:
+++++++++++++++++++++++

Date: Type Count

2003-05-20 0 2

2003-05-20 1 2

+++++++++++++++++++++++

I can work with this (because it is correct), but would prefer it all on one
record.

Here's what I've tried:

----------------------------------------------------------------------------
----------------------------------------------------------------

select

tblInspections.ReceivedByDate,

count(NCritVios.Type) as NCritViolationCount,

count(CritVios.Type) as CritViolationCount

from

tblInspections

join tblViolations as NCritVios on NCritVios.Type = 0 and
tblInspections.ID = NCritVios.InspID

join tblViolations as CritVios on CritVios.Type = 1 and
tblInspections.ID = CritVios.InspID

where

tblInspections.iestabid = 100

group by

tblInspections.ReceivedByDate, NCritVios.Type

----------------------------------------------------------------------------
----------------------------------------------------------------

and it retuns:

+++++++++++++++++++++++

Date: NCrit Crit

2003-05-20 4 4

+++++++++++++++++++++++

which is not correct.

Note that without the second count in the select and without the second
join, it works (counts ncrits correctly). It's that second join.

Any ideas?

Thanks.

Thom

[Thomas Brown]

Here's the solution I found, if anybody cares.

select
tblInspections.ReceivedByDate,
(select count(tblViolations.id) from tblViolations
where tblViolations.Type = 0 and tblViolations.inspID =
tblInspections.ID) as NonCriticals,
(select count(tblViolations.id) from tblViolations
where tblViolations.type = 1 and tblViolations.inspID =
tblInspections.ID) as Criticals
from tblInspections


"Thomas Brown" <th**@semo.net> wrote in message
news:10*************@corp.supernews.com...

I'm having a bit of a SQL problem and I can't figure it out. I have a
working solution, but it could be better.

I've got two tables, tblInspections and tblViolations. Each inspection
record has an associated establishment id. Each inspection record can have multiple violation records associated with it. Each violation can be either of type 1 (critical) or 0 (noncritical).

What I want is to get a list of inspections by establishmentid with a column for the count of noncritical violations, and a count of critical violations.
EG.

Date NonCriticalViolations CriticalViolations

5/30/2003 6 2

Here is my SQL and what it returns.

-------------------------------------------------------------------------- -- ----------------------------------------------------------------

select

tblInspections.ReceivedByDate,

tblViolations.Type,

count(tblViolations.Type) as ViolationCount

from

tblinspections

join tblViolations on tblInspections.ID = tblviolations.InspID

where tblInspections.iestabid = 100

group by

tblViolations.Type,

tblInspections.ReceivedByDate

-------------------------------------------------------------------------- -- ----------------------------------------------------------------

and it returns:
+++++++++++++++++++++++

Date: Type Count

2003-05-20 0 2

2003-05-20 1 2

+++++++++++++++++++++++

I can work with this (because it is correct), but would prefer it all on one record.

Here's what I've tried:

-------------------------------------------------------------------------- -- ----------------------------------------------------------------

select

tblInspections.ReceivedByDate,

count(NCritVios.Type) as NCritViolationCount,

count(CritVios.Type) as CritViolationCount

from

tblInspections

join tblViolations as NCritVios on NCritVios.Type = 0 and
tblInspections.ID = NCritVios.InspID

join tblViolations as CritVios on CritVios.Type = 1 and
tblInspections.ID = CritVios.InspID

where

tblInspections.iestabid = 100

group by

tblInspections.ReceivedByDate, NCritVios.Type

-------------------------------------------------------------------------- -- ----------------------------------------------------------------

and it retuns:

+++++++++++++++++++++++

Date: NCrit Crit

2003-05-20 4 4

+++++++++++++++++++++++

which is not correct.

Note that without the second count in the select and without the second
join, it works (counts ncrits correctly). It's that second join.

Any ideas?

Thanks.

Thom

[Joe Celko]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Does your boss often ask you to program without this
information?

Plese read ISO-11179 so you'll know why not ot put that silly "tbl-"
prefix on table names. That is, unless this database deals furniture
inspections, which is what you are saying in ISO terms.

I've got two tables, inspections and violations. Each inspection record [sic] has an associated establishment id. Each inspection record
[sic] can have multiple violation records [sic] associated with it. Each
violation can be either of type 1 (critical) OR 0 (non-critical). <<

You have all the classic newbie design flaws and misconceptions. Rows
are not records; fields are not columns; tables are not files. A data
element does not change its name because of the table it appears in.
There is no "Magical, Global, one-size-fits-all id" -- to be is to be
something in particular and to be a general, vague nothing in particular
is to be nothing. DATE is a reserved word in Standard SQL as well as
too vague to a data element name. And I am almost willing to bet that
you are using IDENTITY as the key to tables, completing the list of
Newbie design errors.

Of course your inspection tickets do have a check digit or other
built-in validation. You did include DRI and DRI actions (a major
reason talbes are not files). To my mind, a violation is the situation
that appears on a citation; a citation is issued for a violation found
in an inspection at a particular establishment. Is this what you meant
to post?

CREATE TABLE Inspections
(insp_id INTEGER NOT NULL PRIMARY KEY
CHECK (<< validation rule >>),
estab_id INTEGER NOT NULL
REFERENCES Establishments (estab_id),
receivedbydate DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
..);

CREATE TABLE Citations
(insp_id INTEGER NOT NULL
REFERENCES Inspections(insp_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
violation_code INTEGER NOT NULL
REFERENCES Violations (violation_code)
ON DELETE CASCADE
ON UPDATE CASCADE,
violation_type INTEGER NOT NULL
CHECK (violation_type IN (0, 1)),
..);
What I want is to get a list of inspections by establishment_id with

a column for the count of non-critical violations, and a count of
critical violations. <<

You ask for it by establishment, then show it by date! Kinda like the
joke about "line up alphabetically by height!" -- I'll do the written
version of the spec:

SELECT I1.estab_id,
SUM (CASE WHEN C1.violation_type = 1 THEN 1 ELSE 0 END)
AS critical,
SUM (CASE WHEN C1.violation_type = 0 THEN 1 ELSE 0 END)
AS non_critical
FROM Citations AS C1, Inspections AS I1
WHERE C1.insp_id = I1.insp_id
GROUP BY I1.I1.estab_id;

I am not sure if a violation type ought to be in the violations, or if
the same violation can be deemed to be critical/non-critical on each
citation (example: violation 1010 = "uncovered food container"; critical
when food is 3 day old oysters, non-critical when food is cheese). I
took the latter assumption and let each inspecotr make a judgement call.

--CELKO--
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

[posted and mailed, please reply in news]

Thomas Brown (th**@semo.net) writes:

select tblInspections.ReceivedByDate,
tblViolations.Type,
count(tblViolations.Type) as ViolationCount
from tblinspections
join tblViolations on tblInspections.ID = tblviolations.InspID
where tblInspections.iestabid = 100
group by tblViolations.Type,
tblInspections.ReceivedByDate

and it returns:

Date: Type Count
2003-05-20 0 2
2003-05-20 1 2

I can work with this (because it is correct), but would prefer it all on
one record.

The same solution as Joe Celko, just not buried among his standard
diatribe:

select i.ReceivedByDate,
SUM (CASE i.Type WHEN 0 THEN 1 ELSE 0 END) AS NonCritCnt
SUM (CASE i.Type WHEN 1 THEN 1 ELSE 0 END) AS CritCnt
from tblinspections i
join tblViolations v on i.ID = v.InspID
where i.iestabid = 100
group by i.ReceivedByDate
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Joe Celko]

>> The same solution as Joe Celko, just not buried among his standard
diatribe: <<

That was a hand-made, custom diatribe!

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!