Remove Fields From results from Innerjoin In Sql

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

Database Design and Application Architecture

Remove Fields From results from Innerjoin In Sql

Author

Topic

whoops
Starting Member

3 Posts

Posted - 2007-10-10 : 06:25:12

Remove Fields From results from Innerjoin In Sql
--------------------------------------------------------------------------------

Hi, I' ve been struggling for a few days with following problem.
I have a query with 2 tables (below). They are joined by one key field checknr.
I would only like to see field comm from table OTCUser.checklistcomm, but only the last result which are conform to the condition. So the fields in bold shouldn't appair.Could someone help me ?

SELECT MAX(p.Modifdatum) AS modifdatumv, OTCUser.checklistcomm.comm, p.Status, p.station, p.checkitem
FROM OTCUser.checkliststat p INNER JOIN
OTCUser.checklistcomm ON p.Checknr = OTCUser.checklistcomm.checknr
GROUP BY p.station, p.checkitem, OTCUser.checklistcomm.comm, p.Status
HAVING (p.Status = '2') AND (p.station = '00BE104740')
ORDER BY p.station, p.checkitem

modifdatum comm status checknr station
10/04/2007 11:15:20 comment0 2 00BE104740 14
10/05/2007 8:57:45 comment1 2 00BE104740 14
10/10/2007 11:59:35 comment2 2 00BE104740 14
10/04/2007 11:15:20 comment 2 00BE104740 16
10/04/2007 11:15:20 comment 2 00BE104740 17
10/08/2007 17:20:43 comment 2 00BE104740 18
10/04/2007 11:15:20 comment 2 00BE104740 22
10/04/2007 11:15:20 comment 2 00BE104740 24
10/04/2007 11:15:20 comment 2 00BE104740 25
10/04/2007 11:15:20 comment 2 00BE104740 26
10/08/2007 17:20:43 comment 2 00BE104740 26
10/04/2007 11:15:20 comment1 2 00BE104740 27
10/08/2007 17:20:43 comment2 2 00BE104740 27
10/04/2007 11:15:20 comment 2 00BE104740 31

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 06:41:50

Perhaps Number 2 from:

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Kristen

whoops
Starting Member

3 Posts

Posted - 2007-10-10 : 07:07:08

Thanks Kristen, but I wouldn't know how to adapt my sql statement according to this example, because it's only working with 1 database. I'm working with 2 databases (OTCUser.checkliststat and OTCUser.checklistcomm). So I'm using a join statement to link the 2 tables with checknr as key. The problem is that the comm field should not be in the group by. Otherwise I'm getting all the results from the comment table. So what I need is only the last inputs for a specific station,item,status from table OTCUser.checkliststat and just add the comment if there is one related to the checknr.

whoops
Starting Member

3 Posts

Posted - 2007-10-10 : 07:20:02

Maybe following will help you to better understand the problem:

Table OTCUser.checkliststat has fields:
Status, station, checkitem,checknr

Table OTCUser.checklistcomm has fields:
checknr,comm

both tables are joined by checknr.

So when entering new data in OTCUser.checkliststat I sometime provide comments which are stored in OTCUser.checklistcomm with as key checknr.
So what I would like to get is the last input for station 00BE104740 with status 2 and also display the related comment in OTCUser.checklistcomm (if available.)

So to resume ... if I'm entering 3 times data for station 00BE104740 with a status 2 I would only like to see the last one I entered, but also see the comment if available. If I would use this query I would have the correct number of results, but wouldn't have the comm field:

SELECT MAX(p.Modifdatum) AS modifdatumv, p.Status, p.station, p.checkitem
FROM OTCUser.checkliststat p INNER JOIN
OTCUser.checklistcomm ON p.Checknr = OTCUser.checklistcomm.checknr
WHERE (p.Status = '2') AND (p.station = '00BE104740')
GROUP BY p.station, p.checkitem, p.Status
ORDER BY p.station, p.checkitem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources