SQL query modification - get only unique items

Question

0.00/5 (No votes)

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.

SQL

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 16:27pm

7prince

Add a Solution

Comments

_Maxxx_ 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

PIEBALDconsult · Answer 1 · 2013-01-21T05:21:00

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.