Skip to Content
-2

how to use IN operator in INNER JOIN

hello all,

how do i use IN operator in INNER JOIN,

inner-join.jpg (94.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Feb 18, 2017 at 01:00 PM

    Hello,

    "IN" operation ,you must use it after where condition.

    select a~pspnr
           a~posid
           a~objnr
           a~vernr
           a~verna
           a~fkstl
           a~prctr
           a~werks
           into corresponding fields of table it_prps
           from prps as a
           inner joing jest as b
           on a~objnr = b~objnr
           where a~werks eq p_werks and
                 a~belkz eq 'X' and
                 a~loevm ne 'X' and
                 b~stat in ('I0001','E0008') and
                 b~inact eq  ''.
    
    Add comment
    10|10000 characters needed characters exceeded

    • That's because a single record cannot have a STAT field that contains I0001 and at the same time E0008. It is a common feature of database tables that a field in a record can only have one value at a time.

      What you need to do is write a program that matches up the records you are interested.

      I think this question has moved away from its original intent and has become consultancy on how to program. That being the case, I'm closing it.

    Further commenting has been locked.
  • Feb 20, 2017 at 07:01 AM

    hi vinay,

    try this code, after add prps table where condition.

    if return records,you problem prps where condition.

    select a~pspnr
           a~posid
           a~objnr
           a~vernr
           a~verna
           a~fkstl
           a~prctr
           a~werks
           into corresponding fields of table it_prps
           from prps as a
           inner join jest as b
           on a~objnr = b~objnr
           where  
                 b~stat in ('I0001','E0008') and
                 b~inact eq  ''.
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 20, 2017 at 07:28 AM

    Use TWO joins (or better two subqueries) as you actually want to check existence of two records.

    Regards,
    Raymond

    Add comment
    10|10000 characters needed characters exceeded

    Further commenting has been locked.
  • Feb 20, 2017 at 07:33 AM

    hi vinay,

    i get the records my system.

    i think,you should go to step by step.

    1-select prps table where conditon

    2-select jest table where conditon

    3-loop at compare 2 tables

    4-write result

    after this senerio,if ok,try again join 2 tables.

    Add comment
    10|10000 characters needed characters exceeded

    Further commenting has been locked.