Help joining two queries together...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help joining two queries together...

Author

Topic

glawrie
Starting Member

2 Posts

Posted - 2014-02-08 : 10:13:41

Hi

I am sure this is a lame question, but I'd be very grateful for any help.

I have two SQL queries that each work separately. But I'd like to link them together, and I simply can't get them to work. I'm sure it can be done, but how...?

The first query pulls a table of records from a set of tables. Here it is:

SELECT Clients.clientname AS Client, 
	Projects.projectname AS Project, 
	Projects.dbestimatedhours AS "Win Probability", 
	Projects.dbamountquoted AS "Est. Total Value", 
	ROUND(Projects.dbamountquoted*Projects.dbestimatedhours,0) AS "Pipeline Value", 
	Projects.startdate AS "Est. Start", 
	substr(trim(replace(Categories.categoryinfo,'OINSDPRPR8N3OSIFVN',' ')),0,11) AS Who, 
	CASE WHEN Projects.status = 'PS-1111' THEN 'Prospect' WHEN Projects.status = 'PS-1112' THEN 'Proposal' END AS Status
FROM Projects INNER JOIN Clients ON Projects.AttachedToID = Clients.SID
	 INNER JOIN Categories ON Projects.category = Categories.SID
WHERE (Projects.status = "PS-1111" or Projects.status = "PS-1112")
ORDER BY Projects.status DESC, Who ASC, "Est. Start" ASC

The Second query pulls the most recent log statements for all projects in the database

SELECT MAX(NETdatemodifiedstring) AS LogDate, CLogs.Subject||' // '||CLogs.Content AS Comment
FROM CLogs
GROUP BY CLogs.Links
ORDER BY CLogs.NETdatemodifiedstring ASC

What I am trying to do (and so far failing) is to combine these two queries so I end up with a single table that has project information for the subset of projects chosen by first query, and the most recent comment for the project (if there is one, many don't have one).

Can anyone help?

Thanks a lot in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-10 : 08:35:12

I cant see any common fields between queries which you can use for linking and join them together. Do you have any of such fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

glawrie
Starting Member

2 Posts

Posted - 2014-02-10 : 08:50:22

Hi,

I'm sorry. Yes, the CLogs has a field called .Links that contains values that line up with Projects.SID.

This version of the SQL includes a LEFT OUTER JOIN to CLogs using this field, but reports only the first log message that matches in each case, rather than the most recent.

SELECT Clients.clientname AS Client, 
	Projects.projectname AS Project, 
	Projects.dbestimatedhours AS "Win Probability", 
	Projects.dbamountquoted AS "Est. Total Value", 
	ROUND(Projects.dbamountquoted*Projects.dbestimatedhours,0) AS "Pipeline Value", 
	Projects.startdate AS "Est. Start", 
	substr(trim(replace(Categories.categoryinfo,'OINSDPRPR8N3OSIFVN',' ')),0,11) AS Who, 
	CASE WHEN Projects.status = 'PS-1111' THEN 'Prospect' WHEN Projects.status = 'PS-1112' THEN 'Proposal' END AS Status, 
	CLogs.LogDate AS 'Last Update',
	CLogs.Subject||' // '||CLogs.Content AS 'Update Comments'
FROM Projects INNER JOIN Clients ON Projects.AttachedToID = Clients.SID
	 INNER JOIN Categories ON Projects.category = Categories.SID
	 LEFT OUTER JOIN CLogs ON Projects.SID = CLogs.Links
WHERE (Projects.status = "PS-1111" or Projects.status = "PS-1112")
ORDER BY Projects.status DESC, Who ASC, "Est. Start" ASC

The CLogs SQL in my first post will pull the most recent comment for each value of Projects.SID in the Links field, but I can't work out how to get this subset to be used in the LEFT OUTER JOIN - if that makes sense... (

)

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources