query help appreciated. max()/joins/subquery 
Author Message
 query help appreciated. max()/joins/subquery

thanks for looking this. i can make simple queries but this is first
attempt at a subquery as well as using max(). i'm pretty lost. any
help is appreciated.

i have 3 tables(simplified):
cities[id,cityname]
projects[id,cityid,projectname]
reports[id,projectid,reportdate]

i'd like to make a list that shows all cities and projects with the
latest date from the report table if there are reports for that
project. like:

city1: project1: max(report date) -some have many reports, some none
city1: project2: -has no reports in reports table
city2: project1: max(report date)

i can get the latest report date and city name for projects with
reports with:

SELECT CityName, ProjectName, ReportDate FROM Projects proj
LEFT OUTER JOIN Reports rep ON proj.ID = rep.ProjectID
LEFT OUTER JOIN Cities cit ON proj.CityID = cit.ID
WHERE ReportDate = (SELECT MAX(ReportDate) FROM Reports WHERE
ProjectID = rep.ProjectID)

result:
city1: project1: max(report date)
city2: project1: max(report date)

how to i get the rest of the projects to show up if they have no
reports. i hope this is enough info to make sense.
thanks,
mark



Tue, 09 Nov 2004 04:26:23 GMT
 query help appreciated. max()/joins/subquery

[posted and mailed, please reply in news]

Quote:

> i have 3 tables(simplified):
> cities[id,cityname]
> projects[id,cityid,projectname]
> reports[id,projectid,reportdate]

> i'd like to make a list that shows all cities and projects with the
> latest date from the report table if there are reports for that
> project. like:

> city1: project1: max(report date) -some have many reports, some none
> city1: project2: -has no reports in reports table
> city2: project1: max(report date)

> i can get the latest report date and city name for projects with
> reports with:

> SELECT CityName, ProjectName, ReportDate FROM Projects proj
> LEFT OUTER JOIN Reports rep ON proj.ID = rep.ProjectID
> LEFT OUTER JOIN Cities cit ON proj.CityID = cit.ID
> WHERE ReportDate = (SELECT MAX(ReportDate) FROM Reports WHERE
> ProjectID = rep.ProjectID)

> result:
> city1: project1: max(report date)
> city2: project1: max(report date)

It is always a good idea for a question like this one, to include
CREATE TABLE statments for the involved tables, INSERT statements
with sample data, and the desired output from those tables. This
permits the person who answering your question to test his solution.

Without this, and feeling somewhat uncertain of you what are looking
for, I will have to let it sugffice with a guess. Move the condition
in the WHERE clause to the ON clause that follows Reports.

And please accept an advice about coding style: in a multi-table query,
do always prefix your columns.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Tue, 09 Nov 2004 05:18:41 GMT
 query help appreciated. max()/joins/subquery
I don't have anything to create an automatic script so i am trying to
make these up by looking at online examples. hopefully they are correct.
i have some comments in the insert portion (in case they aren't) that
will need to be removed.

CREATE TABLE [dbo].[CITIES] (
        [ID] [int] IDENTITY (1, 1) NOT NULL ,
        [CityName] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PROJECTS] (
        [ID] [int] IDENTITY (1, 1) NOT NULL ,
        [CityID] [int] NULL ,
        [Name] [varchar] (100) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[REPORTS] (
        [ID] [int] IDENTITY (1, 1) NOT NULL ,
        [ProjectID] [int] NULL ,
        [SubmitDate] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO CITIES VALUES ( 'Dallas' ) *assuming City.ID becomes 1
INSERT INTO CITIES VALUES ( 'Houston' ) *assuming City.ID becomes 2

INSERT INTO PROJECTS VALUES ( 1, 'PROJECT1' ) *assuming Projects.ID
becomes 1
INSERT INTO PROJECTS VALUES ( 1, 'PROJECT2' ) *assuming Projects.ID
becomes 2
INSERT INTO PROJECTS VALUES ( 2, 'PROJECT1' ) *assuming Projects.ID
becomes 3

INSERT INTO REPORTS VALUES ( 1, '5/23/02 7:00 PM' )
INSERT INTO REPORTS VALUES ( 1, '5/23/02 6:00 PM' )
INSERT INTO REPORTS VALUES ( 3, '5/23/02 5:00 PM' )
INSERT INTO REPORTS VALUES ( 3, '5/23/02 4:00 PM' )

desired result: show all cities and projects and, if existing, max
report date for project

Dallas: PROJECT1: 5/23/02 7:00:00 PM
Dallas: PROJECT2:
Houston: PROJECT1: 5/23/02 5:00:00 PM

Thank you,

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



Tue, 09 Nov 2004 09:10:16 GMT
 query help appreciated. max()/joins/subquery
hi look at this

SELECT DISTINCT
    city.city_name, PROJECTS.proj_name, MAX(report.report_date)
    AS Expr1
FROM city INNER JOIN
    PROJECTS ON
    city.city_id = PROJECTS.city_id LEFT OUTER JOIN
    report ON PROJECTS.proj_id = report.proj_id
GROUP BY city.city_name, PROJECTS.proj_name

-v

Quote:
> I don't have anything to create an automatic script so i am trying to
> make these up by looking at online examples. hopefully they are correct.
> i have some comments in the insert portion (in case they aren't) that
> will need to be removed.

> CREATE TABLE [dbo].[CITIES] (
>    [ID] [int] IDENTITY (1, 1) NOT NULL ,
>    [CityName] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO

> CREATE TABLE [dbo].[PROJECTS] (
>    [ID] [int] IDENTITY (1, 1) NOT NULL ,
>    [CityID] [int] NULL ,
>    [Name] [varchar] (100) NULL
> ) ON [PRIMARY]
> GO

> CREATE TABLE [dbo].[REPORTS] (
>    [ID] [int] IDENTITY (1, 1) NOT NULL ,
>    [ProjectID] [int] NULL ,
>    [SubmitDate] [datetime] NULL
> ) ON [PRIMARY]
> GO

> INSERT INTO CITIES VALUES ( 'Dallas' ) *assuming City.ID becomes 1
> INSERT INTO CITIES VALUES ( 'Houston' ) *assuming City.ID becomes 2

> INSERT INTO PROJECTS VALUES ( 1, 'PROJECT1' ) *assuming Projects.ID
> becomes 1
> INSERT INTO PROJECTS VALUES ( 1, 'PROJECT2' ) *assuming Projects.ID
> becomes 2
> INSERT INTO PROJECTS VALUES ( 2, 'PROJECT1' ) *assuming Projects.ID
> becomes 3

> INSERT INTO REPORTS VALUES ( 1, '5/23/02 7:00 PM' )
> INSERT INTO REPORTS VALUES ( 1, '5/23/02 6:00 PM' )
> INSERT INTO REPORTS VALUES ( 3, '5/23/02 5:00 PM' )
> INSERT INTO REPORTS VALUES ( 3, '5/23/02 4:00 PM' )

> desired result: show all cities and projects and, if existing, max
> report date for project

> Dallas: PROJECT1: 5/23/02 7:00:00 PM
> Dallas: PROJECT2:
> Houston: PROJECT1: 5/23/02 5:00:00 PM

> Thank you,

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



Tue, 09 Nov 2004 20:56:02 GMT
 query help appreciated. max()/joins/subquery
viswanathan,

thanks so much! it works great. i'd like to try one more thing though.
it doesn't seem to show cities if they don't have any projects. it'll
give me something to do this weekend.

add this to my previous list of insert statements:
INSERT INTO CITIES VALUES ( 'Austin' )

new desired result:
Austin:
Dallas: PROJECT1: 5/23/02 7:00:00 PM
Dallas: PROJECT2:
Houston: PROJECT1: 5/23/02 5:00:00 PM

thanks again.
mark

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



Tue, 09 Nov 2004 22:30:30 GMT
 query help appreciated. max()/joins/subquery
last post hasn't posted yet so hopefully these will come in, in order. i
got all the cities to show up now. just changed the inner join to a left
outer join.

also, is it proper syntax to have both a group by clause and an order by
clause? or just one?

thanks.

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



Tue, 09 Nov 2004 22:50:20 GMT
 query help appreciated. max()/joins/subquery
Mark,

Quote:
> is it proper syntax to have both a group by clause and an order by
> clause? or just one?

It is not only "proper syntax" to have both a GROUP BY and an ORDER BY
clause, but it is a good programming practice as well. While a GROUP BY
**might** return output in the sequence you want, it not something you
should depend on. Only an ORDER BY clause guarantees the sequencing of the
recordset.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> last post hasn't posted yet so hopefully these will come in, in order. i
> got all the cities to show up now. just changed the inner join to a left
> outer join.

> also, is it proper syntax to have both a group by clause and an order by
> clause? or just one?

> thanks.

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



Wed, 10 Nov 2004 06:35:46 GMT
 query help appreciated. max()/joins/subquery
BP Margolin (and others),
thanks for all the replies and help. this is great for those of us who
are new to this to have experienced people willing to help.

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



Thu, 11 Nov 2004 05:30:13 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. JOIN, SUBQUERY, MAX( ) - Need help with this query

2. JOIN, SUBQUERY, MIN( ) - Need help with this query

3. Really bad subquery bug in 6.5 (full example included), any help appreciated

4. Select Where MAX(Date) Query - Correlated SubQuery Question

5. Subquery help with Max !

6. Query throws access violation (right outer join with subquery)

7. Join queries vs Subqueries

8. NOT EXISTS subquery vs join query

9. Query Challenge - Help Appreciated

10. Simple Contains Query Question (any help appreciated)

11. slow query -- help much appreciated

12. query help appreciated


 
Powered by phpBB® Forum Software