NESTED SQL QUESTION and aggregate function

10 Feb 2006 4:47 PM

kaczmar2

I have have three tables that I need to pull information from, and
return selected rows. Sorry for the psudeo-code for the tables, but it
should give you an idea of their (simplified) structure:

Inspections (
InspectionID int (PK),
LocationID int (FK),
InspectorID int (FK),
InspectionDate datetime
)

Inspectors (
InspectorID int (PK)
InspectorName varchar(50)
)

Location (
LocationID int (PK)
LocationName varchar(50)
)

Here's the data I need displayed as it would be shown in a "flat"
return:

SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate

FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101)
ORDER BY p.InspectorName, LastUpdate

would return results:

Joe Inspector    DEARBORN    2006-02-09 10:00:07
Joe Inspector    DEARBORN    2006-02-09 10:10:04
Joe Inspector    DEARBORN    2006-02-09 10:19:19
John Smith     ANN ARBOR    2006-02-09 14:20:35
John Smith    DEXTER                    2006-02-09 14:21:38
Jane Doe                    CLINTON                     2006-02-09 11:40:49
Jane Doe                    MOUNT CLEMENS    2006-02-09 11:54:07

Now, this is what I actually need: I need ONLY the first row (ie,
earliest time of inspection) for EACH inspector, regardless of
location. So my result set would look like:

Joe Inspector    DEARBORN    2006-02-09 10:00:07
John Smith     ANN ARBOR    2006-02-09 14:20:35
Jane Doe                    CLINTON                     2006-02-09 11:40:49
Another Guy              <NULL>                    <NULL>

Also, if the inspector has no inspections for that day, I would still
like to see the inspector name (note last row)

I'll be passing in the date (not the time) as a parameter.

I have tried using a nested SQL statement with an aggreagate MIN on the
InspectionTime, but I can't seem to get just the FIRST row for each
inspector to display - it will just give me every inspection for that
inspector.

Any help or insight will be greatly appreciated.

Christian

10 Feb 2006 5:11 PM

SQL-Star (Rajeev Shukla)

i think this will work...

SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate

FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
where inspectiondate = (select min(inspectiondate) from inspections
where CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101) group by inspectorid)
ORDER BY p.InspectorName, LastUpdate

post DDL and insert statement for more clear solutions

10 Feb 2006 6:43 PM

kaczmar2

Thank you for the reply. I mad to modify the query since your subquery
returns more than one value. "where inspectiondate = (.." was changed
to "where inspectiondate IN (.."

This looks to give me coreect results except for those inspectors that
did not work that day. I would like to show them in the results with
NULL data.

I have attached the DDL and INSERT statements as requested:

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

CREATE TABLE [dbo].[Inspections] (
    [InspectionID] [int] NOT NULL ,
    [LocationID] [int] NOT NULL ,
    [InspectorID] [int] NOT NULL ,
    [InspectionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Inspectors] (
    [InspectorID] [int] NOT NULL ,
    [InspectorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Location] (
    [LocationID] [int] NOT NULL ,
    [LocationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Inspections] ADD
    CONSTRAINT [PK_Inspections] PRIMARY KEY CLUSTERED
    (
        [InspectionID]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Inspectors] ADD
    CONSTRAINT [PK_Inspectors] PRIMARY KEY CLUSTERED
    (
        [InspectorID]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Location] ADD
    CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
    (
        [LocationID]
    ) ON [PRIMARY]
GO

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

INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(1,'Joe Inspector')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(2,'Jane Doe')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(3,'John Smith')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(4,'New Guy')

INSERT INTO Location(LocationID,LocationName)
VALUES(1,'Detroit')
INSERT INTO Location(LocationID,LocationName)
VALUES(2,'Ann Arbor')
INSERT INTO Location(LocationID,LocationName)
VALUES(3,'Royal Oak')
INSERT INTO Location(LocationID,LocationName)
VALUES(4,'Monroe')
INSERT INTO Location(LocationID,LocationName)
VALUES(5,'Dearborn')

INSERT INTO
Inspections(InspectionID,LocationID,InspectorID,InspectionDate)
VALUES(1,1,1,'2/9/2006 9:10 AM')
INSERT INTO
Inspections(InspectionID,LocationID,InspectorID,InspectionDate)
VALUES(2,1,1,'2/9/2006 10:15 AM')
INSERT INTO
Inspections(InspectionID,LocationID,InspectorID,InspectionDate)
VALUES(3,2,2,'2/9/2006 7:30 AM')
INSERT INTO
Inspections(InspectionID,LocationID,InspectorID,InspectionDate)
VALUES(4,3,2,'2/9/2006 11:00 AM')
INSERT INTO
Inspections(InspectionID,LocationID,InspectorID,InspectionDate)
VALUES(5,4,3,'2/9/2006 2:00 PM')
INSERT INTO
Inspections(InspectionID,LocationID,InspectorID,InspectionDate)
VALUES(6,5,3,'2/9/2006 1:00 PM')

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

Thank you very much for your help.

10 Feb 2006 5:15 PM

Mark Williams

SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Today')
FROM Inspectors P
LEFT JOIN
(
SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName, LocationID
) t1
ON p.InspectorId = t1.InspectorId
INNER JOIN Location l ON t1.LocationId = l.LocationID
ORDER BY p.InspectorName, t1.EaliestInspection

--

Show quote

"kaczm***@hotmail.com" wrote:

> I have have three tables that I need to pull information from, and
> return selected rows. Sorry for the psudeo-code for the tables, but it
> should give you an idea of their (simplified) structure:
>
> Inspections (
>   InspectionID int (PK),
>   LocationID int (FK),
>   InspectorID int (FK),
>   InspectionDate datetime
> )
>
> Inspectors (
>   InspectorID int (PK)
>   InspectorName varchar(50)
> )
>
> Location (
>   LocationID int (PK)
>   LocationName varchar(50)
> )
>
> Here's the data I need displayed as it would be shown in a "flat"
> return:
>
> SELECT
> p.InspectorName,
> l.LocationName,
> i.InspectionDate
>
> FROM
> Inspectors p
> LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
> LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
> WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
> '02/09/2006', 101)
> ORDER BY p.InspectorName, LastUpdate
>
> would return results:
>
> Joe Inspector    DEARBORN    2006-02-09 10:00:07
> Joe Inspector    DEARBORN    2006-02-09 10:10:04
> Joe Inspector    DEARBORN    2006-02-09 10:19:19
> John Smith     ANN ARBOR    2006-02-09 14:20:35
> John Smith    DEXTER                    2006-02-09 14:21:38
> Jane Doe                    CLINTON                     2006-02-09 11:40:49
> Jane Doe                    MOUNT CLEMENS    2006-02-09 11:54:07
>
> Now, this is what I actually need: I need ONLY the first row (ie,
> earliest time of inspection) for EACH inspector, regardless of
> location. So my result set would look like:
>
> Joe Inspector    DEARBORN    2006-02-09 10:00:07
> John Smith     ANN ARBOR    2006-02-09 14:20:35
> Jane Doe                    CLINTON                     2006-02-09 11:40:49
> Another Guy              <NULL>                    <NULL>
>
> Also, if the inspector has no inspections for that day, I would still
> like to see the inspector name (note last row)
>
> I'll be passing in the date (not the time) as a parameter.
>
> I have tried using a nested SQL statement with an aggreagate MIN on the
> InspectionTime, but I can't seem to get just the FIRST row for each
> inspector to display - it will just give me every inspection for that
> inspector.
>
> Any help or insight will be greatly appreciated.
>
> Christian
>
>

10 Feb 2006 5:38 PM

Mark Williams

Sorry, that last JOIN should be LEFT, not INNER.

--

Show quote

"Mark Williams" wrote:

> SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
> ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Today')
> FROM Inspectors P
> LEFT JOIN
> (
> SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection"
> FROM Inspections
> WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
> GROUP BY InspectorName, LocationID
> ) t1
> ON p.InspectorId = t1.InspectorId
> INNER JOIN Location l ON t1.LocationId = l.LocationID
> ORDER BY p.InspectorName, t1.EaliestInspection
>
> --
>
> "kaczm***@hotmail.com" wrote:
>
> > I have have three tables that I need to pull information from, and
> > return selected rows. Sorry for the psudeo-code for the tables, but it
> > should give you an idea of their (simplified) structure:
> >
> > Inspections (
> >   InspectionID int (PK),
> >   LocationID int (FK),
> >   InspectorID int (FK),
> >   InspectionDate datetime
> > )
> >
> > Inspectors (
> >   InspectorID int (PK)
> >   InspectorName varchar(50)
> > )
> >
> > Location (
> >   LocationID int (PK)
> >   LocationName varchar(50)
> > )
> >
> > Here's the data I need displayed as it would be shown in a "flat"
> > return:
> >
> > SELECT
> > p.InspectorName,
> > l.LocationName,
> > i.InspectionDate
> >
> > FROM
> > Inspectors p
> > LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
> > LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
> > WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
> > '02/09/2006', 101)
> > ORDER BY p.InspectorName, LastUpdate
> >
> > would return results:
> >
> > Joe Inspector    DEARBORN    2006-02-09 10:00:07
> > Joe Inspector    DEARBORN    2006-02-09 10:10:04
> > Joe Inspector    DEARBORN    2006-02-09 10:19:19
> > John Smith     ANN ARBOR    2006-02-09 14:20:35
> > John Smith    DEXTER                    2006-02-09 14:21:38
> > Jane Doe                    CLINTON                     2006-02-09 11:40:49
> > Jane Doe                    MOUNT CLEMENS    2006-02-09 11:54:07
> >
> > Now, this is what I actually need: I need ONLY the first row (ie,
> > earliest time of inspection) for EACH inspector, regardless of
> > location. So my result set would look like:
> >
> > Joe Inspector    DEARBORN    2006-02-09 10:00:07
> > John Smith     ANN ARBOR    2006-02-09 14:20:35
> > Jane Doe                    CLINTON                     2006-02-09 11:40:49
> > Another Guy              <NULL>                    <NULL>
> >
> > Also, if the inspector has no inspections for that day, I would still
> > like to see the inspector name (note last row)
> >
> > I'll be passing in the date (not the time) as a parameter.
> >
> > I have tried using a nested SQL statement with an aggreagate MIN on the
> > InspectionTime, but I can't seem to get just the FIRST row for each
> > inspector to display - it will just give me every inspection for that
> > inspector.
> >
> > Any help or insight will be greatly appreciated.
> >
> > Christian
> >
> >

10 Feb 2006 6:09 PM

kaczmar2

Thank you very much for your feedback. This gets me what I want except
for one thing: It shows the earliest time for each location. I want
to show the earliest time for each inspector regardless of location,
but I do want to see the lcoation in the result set. So I can't group
by location. This is your result set:

Tony Inspector    ANN ARBOR    16:13:40
Tony Inspector    YPSILANTI    17:19:08
Joe Schmoe    PLAINWELL    13:12:39
Jane Doe                     GRAND RAPIDS    11:42:27
Jane Doe                     GRANDVILLE    12:48:00

Any ideas on how to get the earliest time regardless of location?
Thank you for your continued help.

Mark Williams wrote:
Show quote

> Sorry, that last JOIN should be LEFT, not INNER.
> --
>
>
>
> "Mark Williams" wrote:
>
> > SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
> > ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Today')
> > FROM Inspectors P
> > LEFT JOIN
> > (
> > SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection"
> > FROM Inspections
> > WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
> > GROUP BY InspectorName, LocationID
> > ) t1
> > ON p.InspectorId = t1.InspectorId
> > INNER JOIN Location l ON t1.LocationId = l.LocationID
> > ORDER BY p.InspectorName, t1.EaliestInspection
> >
> > --
> >
> > "kaczm***@hotmail.com" wrote:
> >
> > > I have have three tables that I need to pull information from, and
> > > return selected rows. Sorry for the psudeo-code for the tables, but it
> > > should give you an idea of their (simplified) structure:
> > >
> > > Inspections (
> > >   InspectionID int (PK),
> > >   LocationID int (FK),
> > >   InspectorID int (FK),
> > >   InspectionDate datetime
> > > )
> > >
> > > Inspectors (
> > >   InspectorID int (PK)
> > >   InspectorName varchar(50)
> > > )
> > >
> > > Location (
> > >   LocationID int (PK)
> > >   LocationName varchar(50)
> > > )
> > >
> > > Here's the data I need displayed as it would be shown in a "flat"
> > > return:
> > >
> > > SELECT
> > > p.InspectorName,
> > > l.LocationName,
> > > i.InspectionDate
> > >
> > > FROM
> > > Inspectors p
> > > LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
> > > LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
> > > WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
> > > '02/09/2006', 101)
> > > ORDER BY p.InspectorName, LastUpdate
> > >
> > > would return results:
> > >
> > > Joe Inspector    DEARBORN    2006-02-09 10:00:07
> > > Joe Inspector    DEARBORN    2006-02-09 10:10:04
> > > Joe Inspector    DEARBORN    2006-02-09 10:19:19
> > > John Smith     ANN ARBOR    2006-02-09 14:20:35
> > > John Smith    DEXTER                    2006-02-09 14:21:38
> > > Jane Doe                    CLINTON                     2006-02-09 11:40:49
> > > Jane Doe                    MOUNT CLEMENS    2006-02-09 11:54:07
> > >
> > > Now, this is what I actually need: I need ONLY the first row (ie,
> > > earliest time of inspection) for EACH inspector, regardless of
> > > location. So my result set would look like:
> > >
> > > Joe Inspector    DEARBORN    2006-02-09 10:00:07
> > > John Smith     ANN ARBOR    2006-02-09 14:20:35
> > > Jane Doe                    CLINTON                     2006-02-09 11:40:49
> > > Another Guy              <NULL>                    <NULL>
> > >
> > > Also, if the inspector has no inspections for that day, I would still
> > > like to see the inspector name (note last row)
> > >
> > > I'll be passing in the date (not the time) as a parameter.
> > >
> > > I have tried using a nested SQL statement with an aggreagate MIN on the
> > > InspectionTime, but I can't seem to get just the FIRST row for each
> > > inspector to display - it will just give me every inspection for that
> > > inspector.
> > >
> > > Any help or insight will be greatly appreciated.
> > >
> > > Christian
> > >
> > >

10 Feb 2006 6:31 PM

Mark Williams

Terribly sorry,

SELECT p.InspectorName, ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection Today')
FROM Inspectors P
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT InspectorId, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName
) t1
INNER JOIN
(SELECT i.LocationId, l.LocationName FROM Inspections i
INNER JOIN Locations l ON i.LocationId = l.LocationId) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId

--

Show quote

"kaczm***@hotmail.com" wrote:

> Thank you very much for your feedback. This gets me what I want except
> for one thing: It shows the earliest time for each location. I want
> to show the earliest time for each inspector regardless of location,
> but I do want to see the lcoation in the result set. So I can't group
> by location. This is your result set:
>
> Tony Inspector    ANN ARBOR    16:13:40
> Tony Inspector    YPSILANTI    17:19:08
> Joe Schmoe    PLAINWELL    13:12:39
> Jane Doe                     GRAND RAPIDS    11:42:27
> Jane Doe                     GRANDVILLE    12:48:00
>
> Any ideas on how to get the earliest time regardless of location?
> Thank you for your continued help.
>
>
> Mark Williams wrote:
> > Sorry, that last JOIN should be LEFT, not INNER.
> > --
> >
> >
> >
> > "Mark Williams" wrote:
> >
> > > SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
> > > ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Today')
> > > FROM Inspectors P
> > > LEFT JOIN
> > > (
> > > SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection"
> > > FROM Inspections
> > > WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
> > > GROUP BY InspectorName, LocationID
> > > ) t1
> > > ON p.InspectorId = t1.InspectorId
> > > INNER JOIN Location l ON t1.LocationId = l.LocationID
> > > ORDER BY p.InspectorName, t1.EaliestInspection
> > >
> > > --
> > >
> > > "kaczm***@hotmail.com" wrote:
> > >
> > > > I have have three tables that I need to pull information from, and
> > > > return selected rows. Sorry for the psudeo-code for the tables, but it
> > > > should give you an idea of their (simplified) structure:
> > > >
> > > > Inspections (
> > > >   InspectionID int (PK),
> > > >   LocationID int (FK),
> > > >   InspectorID int (FK),
> > > >   InspectionDate datetime
> > > > )
> > > >
> > > > Inspectors (
> > > >   InspectorID int (PK)
> > > >   InspectorName varchar(50)
> > > > )
> > > >
> > > > Location (
> > > >   LocationID int (PK)
> > > >   LocationName varchar(50)
> > > > )
> > > >
> > > > Here's the data I need displayed as it would be shown in a "flat"
> > > > return:
> > > >
> > > > SELECT
> > > > p.InspectorName,
> > > > l.LocationName,
> > > > i.InspectionDate
> > > >
> > > > FROM
> > > > Inspectors p
> > > > LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
> > > > LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
> > > > WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
> > > > '02/09/2006', 101)
> > > > ORDER BY p.InspectorName, LastUpdate
> > > >
> > > > would return results:
> > > >
> > > > Joe Inspector    DEARBORN    2006-02-09 10:00:07
> > > > Joe Inspector    DEARBORN    2006-02-09 10:10:04
> > > > Joe Inspector    DEARBORN    2006-02-09 10:19:19
> > > > John Smith     ANN ARBOR    2006-02-09 14:20:35
> > > > John Smith    DEXTER                    2006-02-09 14:21:38
> > > > Jane Doe                    CLINTON                     2006-02-09 11:40:49
> > > > Jane Doe                    MOUNT CLEMENS    2006-02-09 11:54:07
> > > >
> > > > Now, this is what I actually need: I need ONLY the first row (ie,
> > > > earliest time of inspection) for EACH inspector, regardless of
> > > > location. So my result set would look like:
> > > >
> > > > Joe Inspector    DEARBORN    2006-02-09 10:00:07
> > > > John Smith     ANN ARBOR    2006-02-09 14:20:35
> > > > Jane Doe                    CLINTON                     2006-02-09 11:40:49
> > > > Another Guy              <NULL>                    <NULL>
> > > >
> > > > Also, if the inspector has no inspections for that day, I would still
> > > > like to see the inspector name (note last row)
> > > >
> > > > I'll be passing in the date (not the time) as a parameter.
> > > >
> > > > I have tried using a nested SQL statement with an aggreagate MIN on the
> > > > InspectionTime, but I can't seem to get just the FIRST row for each
> > > > inspector to display - it will just give me every inspection for that
> > > > inspector.
> > > >
> > > > Any help or insight will be greatly appreciated.
> > > >
> > > > Christian
> > > >
> > > >
>
>

10 Feb 2006 8:27 PM

kaczmar2

Mark-

Thank you very much, this worked! I just had to tweak one of the
joins. Here is the cleaned up, final query:

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

SELECT
p.InspectorName,
ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection
Today')

FROM Inspectors p
LEFT JOIN
(
    SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
    FROM
    (
        SELECT i.InspectorId, MIN(i.InspectionDate) AS "EarliestInspection"
        FROM Inspections i
        WHERE DATEDIFF(dd, i.InspectionDate, '20060209') = 0
        GROUP BY i.InspectorId /* name */
    ) t1

    INNER JOIN
    (
        SELECT i.LocationId, l.LocationName, i.InspectionDate, i.InspectorID
-- added
        FROM Inspections i
        INNER JOIN Location l ON i.LocationId = l.LocationId
    ) t2
    ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId

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

Thanks again for your help!!!

10 Feb 2006 7:44 PM

--CELKO--

If you have the proper constraints on your table, you will almost NEVER
use CONVERT()) in a query. Why keep mopping the floor and losing the
ability to use indexes when a simple CHECK() can trim off the time part
of a DATETIME? Fix the leak!

You might also want to learn about ISO-8601 and the SQL Standards for
temporal data, just in case you need to use ISO Standards some day :))
After all, you are one of the few posters lately who actually followed
the ISO-11179 naming conventions!

Your origianl query looks useful in itself, so you might put it in a
VIEW, but this will give you what you asked for

SELECT inspector_name, location_name, MIN(inspection_date)
   -- , MAX(inspection_date) could be useful, too!
FROM (SELECT P.inspector_name, L.location_name, I.inspection_date
         FROM Inspectors AS P
              LEFT OUTER JOIN
              Inspections AS I
              ON P.inspector_id = I.inspector_id
                 LEFT OUTER JOIN
                 Locations AS L
                 ON I.location_id = L.Location_id
        WHERE I.inspection_date '2006-02-09')
       AS X(inspector_name, location_name, inspection_date)
GROUP BY X.inspector_name, X.location_name;

10 Feb 2006 9:17 PM

kaczmar2

CELKO-

Thank you for your input. I probably did not explain my complete
intentions when you saw the CONVERT function used to strip of the time.
I do need the time information, but I need to group by the day for
this query, that is why I was using convert. And yes, I probably
should be aware of my naming conventions/syntax. Note that I posted
abriged tables/data/queries to simplify the example to the group.

12 Feb 2006 6:03 AM

waynehoobler

would this have worked?:

SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate

FROM
Inspectors p
left join

(inspections i join location l on i.locationid=l.locationid)

on p.inspectorid=i.inspectorid and
i.inspectiondate=(SELECT MIN(i1.inspectiondate) from
inspections i1
WHERE
i1.inspectorid=i.inspectorid and
year(i1.inspectiondate)=year(i.inspectiondate) and
month(i1.inspectiondate)=month(i.inspectiondate) and
day(i1.inspectiondate)=day(i.inspectiondate)
)

Note that I'd assume every inspection has a location, thus I wrote the
query this way. Also, I would think that some of the 'where' criteria
in some of the above queries would have negated the left joins going
on. The left joins will provide the NULLS but as soon as you follow up
with 'where inspection date = whatever', that would get rid of the
NULLS.