September 11, 2017 at 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
September 11, 2017 at 8:20 am
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.
September 11, 2017 at 8:43 am
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.
September 11, 2017 at 8:52 am
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.
Larnu.uk
September 11, 2017 at 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!!!
September 11, 2017 at 11:08 am
mbrady5 - Monday, September 11, 2017 8:58 AMThanks 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 11, 2017 at 11:35 am
mbrady5 - Monday, September 11, 2017 7:56 AMGood morning,
I have written a query that returns up to 4 rows of a distinct result;
Results
1. A
2. B
3. C
4. DWhat 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);
September 11, 2017 at 11:40 am
anand08sharma - Monday, September 11, 2017 11:35 AMmbrady5 - Monday, September 11, 2017 7:56 AMGood morning,
I have written a query that returns up to 4 rows of a distinct result;
Results
1. A
2. B
3. C
4. DWhat 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 11, 2017 at 11:45 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!
September 11, 2017 at 11:49 am
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
September 11, 2017 at 1:52 pm
mbrady5 - Monday, September 11, 2017 11:44 AMSteve,
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 11, 2017 at 2:40 pm
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply