Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL-server-2005
I need to modify this query: basically to get the following:
An order (orderid) is to contain gadgets and gizmos and nothing else.
At a minimum, then, an order would have one gadget and one gizmo, period.
But an order could contain any number of different kinds of gadgets AND any number of different kinds of gizmos as long as it had NO OTHER PARTS.
Meaning, an orderid can have gadget or gizmo and the same orderid can have many other inventory. descriptions. I need only the orderids that has only gadget and gismo and those orderids not tied to any other inventory.description.
Example. if orderid 100 has gismo and gadget, that orderid can not be also tied to any other inventory.description.
select oi.orderid as 'order id'
 from orderitems as oi
 where exists(Select 1 from  inventory as inv
 where oi.partid = inv.partid and inv.description like '%gizmo%')
and exists(Select 1 from  inventory as inv
 where oi.partid = inv.partid and inv.description like '%gadget%')
and not exists ( Select 1 from  inventory as inv
 where oi.partid = inv.partid and inv.description not like '%gizmo%'
and inv.description not like '%gadget%')
Posted 20-Jan-13 17:27pm
_Maxxx_ at 21-Jan-13 22:41pm
You haven't said what is wrong with the SQL given?
The first EXISTS should give orders with gizmos
The second should give those with gadgets
and the third (not exists) should give those that dont have lines that are neither gizmos nor gadgets
Is that now what you required?

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

There's a lot of inefficiency in there.
0) You do not want to be relying on LIKE to select records for such basic actions.
1) Each part should have a Type, and you should be using that instead.
2) The Type should not be a string -- string compares are much less speedy than numeric compares.
3) Type should probably be an integer, and have a table to assign a Description to the value.
4) As to values, many will recommend using an Identity column, but I won't.
5) I recommend specifying your values in a structured manner rather than a simple progression.
6) My first thought was to use powers of two so you can OR the values in an order together to easily determine whether or not an order contains types other than Gizmos and Gadgets, but that severely limits the number of types you can have (to 32 or 64 types). If you can work within those limits, it's still a reasonable idea -- it allows you to use an enum in your program code.
7) But, if not, then I would define ranges of type IDs, like the 1000s are Gizmos, the 2000s are Gadgets, the 3000s are Widgets, and the 4000s are Doohickeys.
8) Or, IDs ending in 01 are Gizmos, etc.
Essentially, think about how this system may grow in the future. Or, if this is homework for a class, think about what sorts of systems you may need to develop or maintain in the real world.
Think about these things, and see what you can come up with.
Marcus Kramer at 21-Jan-13 11:26am
My 5.
_Maxxx_ at 21-Jan-13 22:38pm
Options 6, 7 and 8 are horrible solutions relying on magic numbers which are exceedingly prone to blowing up in your face in the future. (e.g. what if you get more than 999 gizmos in case 7, what happens when you introduce Dumwhickeys, fandoogles etc. etc.
The op got that sql from a previous question where, I think, that sql answered the question - sure it is inefficient etc. but I don't think you really made an attempt to help with the sql
PIEBALDconsult at 21-Jan-13 22:41pm
I said he should think about that sort of thing. I hope he didn't get what he posted from anyone here; it's dreadful.
_Maxxx_ at 21-Jan-13 22:54pm
He got the sql from me - and yes, it is dreadful (but based up on his original sql that he was having trouble with)
Rather than suggesting a complete database redesign etc. I tried to help him with his immediate problem - maybe he had an existing DB with the tables he spoke of, maybe it was homework (I suspect so) but the sql was provided to the OP in the form he was writing SQL, in the hope of helping him solve his problem, rather than a bunch of advice on why he shouldn't be doing what he was doing in the first place.
PIEBALDconsult at 21-Jan-13 22:59pm
You gave him a fish rather than teaching him to fish?
I'd rather point him on the path to success now and not have to deal with continued questions here.
_Maxxx_ at 21-Jan-13 23:51pm
Giving him a fish allows him to eat for another day - gives him longer to survive and hopefully thrive.
Telling him how he's doing it all wrong, should bloody learn to fish properly and then advising him to use golf clubs, a table leg or a hari net to do his fishing, isn't what was required here (IMHO)
And your arrogance in saying "not have to deal with continued questions here" is, I think, the sort of thing that puts people off using CP Q&A

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 150
2 Richard MacCutchan 135
3 Andreas Gieriet 100
4 DamithSL 95
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,260

Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 21 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100