Remove Fields From results from Innerjoin In Sql

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • whoops
    New Member
    • Oct 2007
    • 4
    #1

    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.checkli stcomm, 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.Modifdatu m) AS modifdatumv, OTCUser.checkli stcomm.comm, p.Status, p.station, p.checkitem
    FROM OTCUser.checkli ststat p INNER JOIN
    OTCUser.checkli stcomm ON p.Checknr = OTCUser.checkli stcomm.checknr
    GROUP BY p.station, p.checkitem, OTCUser.checkli stcomm.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
    Tags: None
    • whoops
      New Member
      • Oct 2007
      • 4
      #2
      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.checkli stcomm, 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.Modifdatu m) AS modifdatumv, OTCUser.checkli stcomm.comm, p.Status, p.station, p.checkitem
      FROM OTCUser.checkli ststat p INNER JOIN
      OTCUser.checkli stcomm ON p.Checknr = OTCUser.checkli stcomm.checknr
      GROUP BY p.station, p.checkitem, OTCUser.checkli stcomm.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

        Comment

        • azimmer
          Recognized Expert New Member
          • Jul 2007
          • 200
          #3
          Originally posted by 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.checkli stcomm, 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.Modifdatu m) AS modifdatumv, OTCUser.checkli stcomm.comm, p.Status, p.station, p.checkitem
          FROM OTCUser.checkli ststat p INNER JOIN
          OTCUser.checkli stcomm ON p.Checknr = OTCUser.checkli stcomm.checknr
          GROUP BY p.station, p.checkitem, OTCUser.checkli stcomm.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?)

            Comment

            • whoops
              New Member
              • Oct 2007
              • 4
              #4
              Thanks for quick reply, but ... nope, unfortunately. I tried with where but had the same results.
              SELECT MAX(p.Modifdatu m) AS modifdatumv, OTCUser.checkli stcomm.comm, p.Status, p.station, p.checkitem
              FROM OTCUser.checkli ststat p INNER JOIN
              OTCUser.checkli stcomm ON p.Checknr = OTCUser.checkli stcomm.checknr
              WHERE (p.Status = '2') AND (p.station = '00BE104740')
              GROUP BY p.station, p.checkitem, OTCUser.checkli stcomm.comm, p.Status
              ORDER BY p.station, p.checkitem

              Table OTCUser.checkli ststat has fields:
              Status, station, checkitem,check nr

              Table OTCUser.checkli stcomm has fields:
              checknr,comm


              So when entering new data in OTCUser.checkli ststat I sometime provide comments which are stored in OTCUser.checkli stcomm 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.checkli stcomm (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.Modifdatu m) AS modifdatumv, p.Status, p.station, p.checkitem
              FROM OTCUser.checkli ststat p INNER JOIN
              OTCUser.checkli stcomm ON p.Checknr = OTCUser.checkli stcomm.checknr
              WHERE (p.Status = '2') AND (p.station = '00BE104740')
              GROUP BY p.station, p.checkitem, p.Status
              ORDER BY p.station, p.checkitem

                Comment

                • iburyak
                  Recognized Expert Top Contributor
                  • Nov 2006
                  • 1016
                  #5
                  Try this

                  Code:
                  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.

                    Comment

                    • Motoma
                      Recognized Expert Specialist
                      • Jan 2007
                      • 3236
                      #6
                      Originally posted by 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.checkli stcomm, 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.Modifdatu m) AS modifdatumv, OTCUser.checkli stcomm.comm, p.Status, p.station, p.checkitem
                      FROM OTCUser.checkli ststat p INNER JOIN
                      OTCUser.checkli stcomm ON p.Checknr = OTCUser.checkli stcomm.checknr
                      GROUP BY p.station, p.checkitem, OTCUser.checkli stcomm.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.

                        Comment

                        • iburyak
                          Recognized Expert Top Contributor
                          • Nov 2006
                          • 1016
                          #7
                          It is the same question as this:
                          http://www.thescripts. com/forum/thread720953.ht ml

                            Comment

                            • Motoma
                              Recognized Expert Specialist
                              • Jan 2007
                              • 3236
                              #8
                              Originally posted by iburyak
                              It is the same question as this:
                              http://www.thescripts. com/forum/thread720953.ht ml
                              Thanks iburyak, I have merged the threads.

                                Comment

                                • iburyak
                                  Recognized Expert Top Contributor
                                  • Nov 2006
                                  • 1016
                                  #9
                                  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.

                                    Comment

                                    • whoops
                                      New Member
                                      • Oct 2007
                                      • 4
                                      #10
                                      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.Modifdatu m) AS modifdatum, MAX(OTCUser.che cklistcomm.comm ) AS comm, p.Status, p.station, MAX(p.checkitem ) AS Expr1
                                      FROM OTCUser.checkli ststat p INNER JOIN
                                      OTCUser.checkli stcomm ON p.Checknr = OTCUser.checkli stcomm.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.

                                        Comment

                                        Previous template Next
                                        Working...