Pass Distinct Results to New query (IN)

  • mbrady5

    SSCrazy

    Points: 2835

    Good morning,
    I have written a query that returns up to 4  rows of a distinct result;
    Results
    1. A
    2. B
    3. C
    4. D

    What I am trying to accomplish is to pass the above results into a separate query in the where clause.
    Select *
    From Table
    where column IN ('A','B','C','D')

    Any help  would be appreciated.

    Thanks

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717408

    You're attacking the problem in the wrong way. we want everything to be embedded. For example, I could do this:

    select *
     from table
    where  column in (select  id from othertable where id < 5)

    This lets me combine some data into a subquery.

    However, you may  just want 

    select a.*
     from table  a
      inner join  table2 b
      on a.columnn = b.column
    where b.id < 5

    Without knowing more about your code, hard to know if a single query or a subquery makes more sense.

  • mbrady5

    SSCrazy

    Points: 2835

    Thanks..To add a bit more context, I will add the SQL;
    select distinct
      JI.item
    from JobInfo as JI
    where (1 = 1)
      and (JI.job = 'P000111032')

    This returns Items ('115235','115236','126391')
    -----
    Now, I'd like to take the above item #'s and add them to the below query in the 'where';
    select distinct *
    from lot_loc
      left outer join matltran
       on matltran.item = lot_loc.item
        and matltran.lot = lot_loc.lot
        and matltran.trans_type = 'F'
      left outer join item
       on item.item = lot_loc.item
    where (1 = 1)
      and (matltran.ref_num <> 'P000111032')
      and (lot_loc.item = '115235')
      or (lot_loc.item = '115236')
      or (lot_loc.item = '126391')

    The reason I am using the separate queries is because the job is equal to a specific job in the first query but I am excluding that job in the second query.
    Hope this helps. As always, I really appreciate the help.

  • Thom A

    SSC Guru

    Points: 98515

    A CTE might work well here then. I've not used your above query, as I don't quite understand your goal, but this should put you on the right path:
    WITH JobInfoDistinct AS (
      SELECT DISTINCT JI.item
      FROM JobInfo JI
      WHERE (1 = 1) --why is this here??? It serves no purpose
        AND (JI.job = 'P000111032'))
    SELECT *
    FROM item i
        JOIN JobInfoDistinct JID ON i.item = JID.item;

    Also, as I said in my comment, what is the purpose of (1 = 1)? It serves no benefit and would (likely) be ignored my the query planner.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • mbrady5

    SSCrazy

    Points: 2835

    Thanks for your response, I was thinking CTE too but, not 100%. Will try now.

    The 1=1 is maybe the coolest time saver ever!!!!
    It enables you  to quickly add and remove items in your where clause without having to do much.
    Example without 1=1;
    select distinct *
    from lot_loc
      left outer join matltran
       on matltran.item = lot_loc.item
        and matltran.lot = lot_loc.lot
        and matltran.trans_type = 'F'
      left outer join item
       on item.item = lot_loc.item
    where  (matltran.ref_num <> 'P000111032')
      and (lot_loc.item = '115235')
      or (lot_loc.item = '115236')
      or (lot_loc.item = '126391')

    If I want to comment out matltran.ref_num, I need to remove the 'And' on the next line and again if I want to add it back in, I need to write 'and' again and reconfigure.

    If I always use 1=1, it makes it really simple to add and remove items in the where clause.

    select distinct *
    from lot_loc
      left outer join matltran
       on matltran.item = lot_loc.item
        and matltran.lot = lot_loc.lot
        and matltran.trans_type = 'F'
      left outer join item
       on item.item = lot_loc.item
    where (1 = 1)
      and (matltran.ref_num <> 'P000111032')
      and (lot_loc.item = '115235')
      or (lot_loc.item = '115236')
      or (lot_loc.item = '126391')

    Give it a try, I know you will love it!!!

  • sgmunson

    SSC Guru

    Points: 110443

    mbrady5 - Monday, September 11, 2017 8:58 AM

    Thanks for your response, I was thinking CTE too but, not 100%. Will try now.

    The 1=1 is maybe the coolest time saver ever!!!!
    It enables you  to quickly add and remove items in your where clause without having to do much.
    Example without 1=1;
    select distinct *
    from lot_loc
      left outer join matltran
       on matltran.item = lot_loc.item
        and matltran.lot = lot_loc.lot
        and matltran.trans_type = 'F'
      left outer join item
       on item.item = lot_loc.item
    where  (matltran.ref_num <> 'P000111032')
      and (lot_loc.item = '115235')
      or (lot_loc.item = '115236')
      or (lot_loc.item = '126391')

    If I want to comment out matltran.ref_num, I need to remove the 'And' on the next line and again if I want to add it back in, I need to write 'and' again and reconfigure.

    If I always use 1=1, it makes it really simple to add and remove items in the where clause.

    select distinct *
    from lot_loc
      left outer join matltran
       on matltran.item = lot_loc.item
        and matltran.lot = lot_loc.lot
        and matltran.trans_type = 'F'
      left outer join item
       on item.item = lot_loc.item
    where (1 = 1)
      and (matltran.ref_num <> 'P000111032')
      and (lot_loc.item = '115235')
      or (lot_loc.item = '115236')
      or (lot_loc.item = '126391')

    Give it a try, I know you will love it!!!

    Yeah, I know...  that's one of the laziest excuses I've ever heard for wasting cpu cycles and ram on query elements that have no effect.  What you're basically saying is that you're willing to waste resources just to save you from having to think.   Sad...   I know that if I were a hiring manager, and I saw you use that practice in an interview, I wouldn't hire you because I'd conclude that if you're lazy enough to do that, what else would you be lazy about.  I'd also question whether or not logic and reason were your primary tools to solve problems.  I'd much rather have folks take a little more time and think things through than insert extraneous code into a project just to save a fraction of a second of editing time.

    Steve
    β€Œ(aka sgmunson)
    β€Œ:) πŸ™‚ πŸ™‚
    β€ŒHealth & Nutrition

  • anand08sharma

    SSC Enthusiast

    Points: 180

    mbrady5 - Monday, September 11, 2017 7:56 AM

    Good morning,
    I have written a query that returns up to 4  rows of a distinct result;
    Results
    1. A
    2. B
    3. C
    4. D

    What I am trying to accomplish is to pass the above results into a separate query in the where clause.
    Select *
    From Table
    where column IN ('A','B','C','D')

    Any help  would be appreciated.

    Thanks

    select *
    from table1
    where column1 in (select distinct1 column from table1);

  • sgmunson

    SSC Guru

    Points: 110443

    anand08sharma - Monday, September 11, 2017 11:35 AM

    mbrady5 - Monday, September 11, 2017 7:56 AM

    Good morning,
    I have written a query that returns up to 4  rows of a distinct result;
    Results
    1. A
    2. B
    3. C
    4. D

    What I am trying to accomplish is to pass the above results into a separate query in the where clause.
    Select *
    From Table
    where column IN ('A','B','C','D')

    Any help  would be appreciated.

    Thanks

    select *
    from table1
    where column1 in (select distinct1 column from table1);

    That's not even valid syntax, much less a working solution.   I suspect you have the right idea, but as written, it would look at the same exact table, so I suspect you meant to use the word "table2" at the end of your query.

    Steve
    β€Œ(aka sgmunson)
    β€Œ:) πŸ™‚ πŸ™‚
    β€ŒHealth & Nutrition

  • mbrady5

    SSCrazy

    Points: 2835

    Steve,
    In the many years  I have come to SQL Server Central for some help, this is the only time I've ever had a bad experience.
    Based on your pattern of thinking and logic ("willing to waste resources just to save you from having to think"), I am certainly glad that I don't have to work for you. How on earth does commenting items out in the where clause in this manner have anything to do with thinking? Help me out here..?  Please explain how I could have thought that particular part of the query out in a better way? It's either you add it or, you don't, right?
    The only thing that is "sad"  is your pompous and condescending attitude.
    Sorry you're having a bad day!

  • mbrady5

    SSCrazy

    Points: 2835

    Thanks Thom A..CTE was the solution for this, for me. Anyhow, here it is. Thanks again.

    declare @Job varchar(20)
    set @Job = 'P000111045'

    with ItemDistinct
    as (select distinct
       JI.item
      from JobInfo as JI
      where (JI.job = @Job)
     )
    select distinct
      case
       when Uf_MaterialCategory = 'Work in Process' then
        1
       else
        0
      end                 as MatlPMTCode
    , matltran.ref_num             as RefNum
    , matltran.ref_line_suf           as RefLineSuf
    , lot_loc.whse
    , lot_loc.item
    , lot_loc.lot
    , lot_loc.loc
    , lot_loc.unit_cost            as UnitCost
    , lot_loc.qty_on_hand            as QtyOnHand
    , lot_loc.matl_cost            as MatlCost
    , lot_loc.lbr_cost             as LbrCost
    , lot_loc.fovhd_cost            as FovhdCost
    , lot_loc.vovhd_cost            as VovhdCost
    , lot_loc.out_cost             as OutCost
    , lot_loc.qty_rsvd             as QtyRsvd
    , item.description
    , item.Uf_MaterialType
    , item.Uf_MaterialCategory
    , item.unit_cost
    , cast(qty_on_hand * item.unit_cost as decimal(18, 2)) Std_LotValue
    from lot_loc
      left outer join matltran
       on matltran.item = lot_loc.item
        and matltran.lot = lot_loc.lot
        and matltran.trans_type = 'F'
      left outer join item
       on item.item = lot_loc.item
      inner join ItemDistinct ID
       on ID.item = matltran.item
        and ref_num <> @Job
        and qty_on_hand > 0

  • sgmunson

    SSC Guru

    Points: 110443

    mbrady5 - Monday, September 11, 2017 11:44 AM

    Steve,
    In the many years  I have come to SQL Server Central for some help, this is the only time I've ever had a bad experience.
    Based on your pattern of thinking and logic ("willing to waste resources just to save you from having to think"), I am certainly glad that I don't have to work for you. How on earth does commenting items out in the where clause in this manner have anything to do with thinking? Help me out here..?  Please explain how I could have thought that particular part of the query out in a better way? It's either you add it or, you don't, right?
    The only thing that is "sad"  is your pompous and condescending attitude.
    Sorry you're having a bad day!

    FACT:  adding 1 = 1 to your WHERE clause is a waste of resources solely to satisfy your personal convenience.   If your thought process allows you to be that kind of lazy, then it's likely that your other thought processes are "similarly lazy".   Defending this foolishness is proof of that.   I'm not here to bash you personally, but I will continue to try to discourage lazy thinking wherever I encounter it.  Surely you wouldn't accept sub-standard work if offered up by a co-worker, so why allow yourself the "luxury" of sub-standard thinking?   If that ruffles your feathers, ... then all I can tell you is that it's likely that such thinking will eventually bite you in the back side, and it will then hurt far more than anything I might say here ever will.

    I see way too much sloppy thinking and lazy attitudes in the SQL Development community, and to be honest, it hurts ALL of us.

    Steve
    β€Œ(aka sgmunson)
    β€Œ:) πŸ™‚ πŸ™‚
    β€ŒHealth & Nutrition

  • mbrady5

    SSCrazy

    Points: 2835

    Steve. You are clearly miserable and you have not been helpful..at all. You didn’t answer any question with any substance. You are an β€œelitist” and I strongly emphasize the quotes. A legend in your own mind, perhaps.? I’m certain the rest of the community will give you accolades for calling out this atrocity. Just kidding, they won’t!

    On a final note, I doubt sincerely that you would be a hiring manager for anyone other than yourself. I hope you have a nice evening at home, undoubtably alone. πŸ™‚

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply