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 black shouldn't appair.
Can 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 comment 0 2 00BE104740 14
10/05/2007 8:57:45 comment 1 2 00BE104740 14 10
/10/2007 11:59:35 comment 2 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 comment 1 2 00BE104740 27
10/08/2007 17:20:43 comment 2 2 00BE104740 27
10/04/2007 11:15:20 comment 2 00BE104740 31

Oct 10 '07 #1

Subscribe Post Reply

1710

whoops

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 black 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 comment 0 2 00BE104740 14
10/05/2007 8:57:45 comment 1 2 00BE104740 14 10
/10/2007 11:59:35 comment 2 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 comment 1 200BE104740 27
10/08/2007 17:20:43 comment 2 2 00BE104740 27
10/04/2007 11:15:20 comment 2 00BE104740 31

Oct 10 '07 #2

azimmer

200

Expert 100+

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 black 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 comment 0 2 00BE104740 14
10/05/2007 8:57:45 comment 1 2 00BE104740 14 10
/10/2007 11:59:35 comment 2 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 comment 1 200BE104740 27
10/08/2007 17:20:43 comment 2 2 00BE104740 27
10/04/2007 11:15:20 comment 2 00BE104740 31

It may seem too trivial but isn't the result what you want if you use "WHERE p.Status=2" instead of "HAVING p.Status=2"?
It it isn't, please make sure your example is well aligned and the right headers are in place because it's difficult to make out which column is which. Also please clarify what you mean by "the last result which are conform to the condition" (i.e. last by what condition?)

Oct 10 '07 #3

whoops

Thanks for quick reply, but ... nope, unfortunately. I tried with where but had the same results.
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
WHERE (p.Status = '2') AND (p.station = '00BE104740')
GROUP BY p.station, p.checkitem, OTCUser.checklistcomm.comm, p.Status
ORDER BY p.station, p.checkitem

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

Table OTCUser.checklistcomm has fields:
checknr,comm

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

Oct 10 '07 #4

iburyak

1,017

Expert 512MB

Try this

Expand|Select|Wrap|Line Numbers

 SELECT MAX(p.Modifdatum) AS modifdatumv, OTCUser.checklistcomm.comm, p.Status, p.station, max(p.checkitem)

FROM OTCUser.checkliststat p INNER JOIN

OTCUser.checklistcomm ON p.Checknr = OTCUser.checklistcomm.checknr

GROUP BY p.station, OTCUser.checklistcomm.comm, p.Status

HAVING (p.Status = '2') AND (p.station = '00BE104740')

ORDER BY p.station, p.checkitem

Look at column p.checkitem. It is why you get more records.
Make sure you know which p.checkitem you get and which you want to have.

Good Luck.

Oct 10 '07 #5

Motoma

3,237

Expert 2GB

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 black shouldn't appair.
Can 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 comment 0 2 00BE104740 14
10/05/2007 8:57:45 comment 1 2 00BE104740 14 10
/10/2007 11:59:35 comment 2 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 comment 1 2 00BE104740 27
10/08/2007 17:20:43 comment 2 2 00BE104740 27
10/04/2007 11:15:20 comment 2 00BE104740 31

I can't really tell what your data represents, but it seems like you want to GROUP BY station.

Oct 10 '07 #6

iburyak

1,017

Expert 512MB

It is the same question as this:
http://www.thescripts.com/forum/thread720953.html

Oct 10 '07 #7

Motoma

3,237

Expert 2GB

It is the same question as this:
http://www.thescripts.com/forum/thread720953.html

Thanks iburyak, I have merged the threads.

Oct 10 '07 #8

iburyak

1,017

Expert 512MB

Thanks....
I am not sure thou if person was interested in an answer thou... :)
I didn't get back to this site for a long time purely for this reason. You never know if you are wasting your time for something useful and with my job requirements I have no time to just play.
I just had some private questions and came back for some time.
Nice talking to you.

Irina.

Oct 10 '07 #9

whoops

Hi Irina,

Thanks a lot for your reply. And yes, in my case it helped. So I'm very grateful.
It wasn't totaly the correct solution, but it helped me to fnd the solution, which is:
SELECT MAX(p.Modifdatum) AS modifdatum, MAX(OTCUser.checklistcomm.comm) AS comm, p.Status, p.station, MAX(p.checkitem) AS Expr1
FROM OTCUser.checkliststat p INNER JOIN
OTCUser.checklistcomm ON p.Checknr = OTCUser.checklistcomm.checknr
GROUP BY p.station, p.Status, p.checkitem
HAVING (p.Status = '2') AND (p.station = '00BE104740')
ORDER BY p.station, p.checkitem

I tried to remove the other thread which was accidentally posted, but couldn't find any way on this size to manage and remove your own threads.

Oct 11 '07 #10

Similar topics

Google autofill fields

by: shortbackandsides.no | last post by:

I'm having a lot of difficulty trying to persuade the Google toolbar autofill to act consistently, for example ======================= <html><head> <title>autofill test</title> </head><body>...

HTML / CSS

Dynamic number of fields in an Access 2000 Report

by: CSDunn | last post by:

Hello, I have a situation with MS Access 2000 in which I need to display report data in spreadsheet orientation (much like a datasheet view for a form). If you think of the report in terms of what...

Microsoft Access / VBA

Programmatically remove a form from Access 2000

by: u7djo | last post by:

Hi, I'm currently building an application in Access and as part of this need to import forms and modules from another database. Some of the imports will be revisions of existing forms/modules so I...

Microsoft Access / VBA

Extracting highest value from a set of fields?

by: Dirtyweeker | last post by:

Hi, I have a database which records fitness test results of pupils. There are the usual name fields and then a series of fields holding results, e.g. field BP1 and field BP2; each of these...

Microsoft Access / VBA

filling fields with query results

by: Brian | last post by:

Hello, Basically, I'm running a query on a form's activation, and I'd like to have the results of the query be placed into other fields on the same form automatically. Does anybody know how...

Microsoft Access / VBA

VBA to Add or Remove a Field From a Table?

by: No Spam | last post by:

Dear Access 2003 users, Can anyone assist me with creating either code (preferred) or a query that would remove a single field (called ID) from a table? And as a bonus question, can anyone...

Microsoft Access / VBA

Query criteria for Filtering Date fields: IIF Nested Operators won't work

by: Ragbrai | last post by:

Howdy All, I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then unbound text boxes on the form that also contains...

Microsoft Access / VBA

Dynamic creation of fields based on results of a query?

by: olivero | last post by:

Hi group, Is there an easy way to make a form create the same set of fields dynamically, once for each record returned by a query? I have a query that's being called from a form and the results...

Microsoft Access / VBA

Remove filter does not work...

by: Matthew Wells | last post by:

Hi there. I am a programmer for a living, but this problem has my whole team stumped. I have a subform based on a query. I'm not using the "LinkMaster/ChildFields" properties ( I have my...

Microsoft Access / VBA

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA