Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2016
    Posts
    1

    Unanswered: Grrrr, cant figure it out how to...

    Dear friend

    I cant figure it out how to select only data being uniqe

    In first table I have f.e. values:

    id, item_id, ..., location_id

    in second table values:

    location_id, ..., is_valid_location (boolean 1 or 0)

    table 1:
    1 | i1 | 00001
    2 | i1 | 00002
    3 | i2 | 00003
    4 | i3 | 00004


    table 2:
    00001 | 1
    00002 | 0
    00003 | 0
    00004 | 1


    I would like to select row with item_id that is present only on location with boolean 1, but not present within any other location with boolean 0.

    In example case result would be:
    4 | i3 | 00004 | 00004 | 1

    I know I have complicated

    thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,993
    Provided Answers: 23
    Quote Originally Posted by amrak1 View Post
    (boolean 1 or 0)
    Unrelated, but: 1 and 0 are not boolean values. Those are numbers

    One way to write this could be:

    Code:
    select *
    from t1
    where not exists (select *
                      from t2 
                      where t2.location_id in (select x.location_id from t1 x where x.item_id = t1.item_id)
                      and not t2.is_valid_location)
    If your is_valid_location is in fact an integer, not a boolean you need to use and t2.is_valid_location = 0
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Aug 2017
    Posts
    13
    Provided Answers: 1

    min and max could filter it

    select * from table1 where item_id in
    (
    select item_id
    from table1 t1 inner join table2 t2 on t1.location_id=t2.location_id
    group by t1.item_id
    having min(is_valid_location)=1 and max(is_valid_location)=1
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •