Complicated Query - SQL Gurus Help please

Question

Complicated Query - SQL Gurus Help please

vmrao

SSC Enthusiast

Points: 133
More actions
March 20, 2008 at 6:29 pm

#74656

Hi,
I am positive some SQL Gurus can help me with this complicated query. I am not sure if it is possible with a single query or needs multiple queries.
I think if you take a look at the attached file, you will get clear understanding of the requirement.
I have 3 objects. First, PaintOrder; Second, Combination; and Third, Material. Each Paint Order can have multiple Combinations and each Combination can have multiple (from 0 to 5) Materials. All the data is stored in single table called paintMatCom.
Here is the sample Data
PaintOrderId CombId MaterialId
1000 1 1
1000 1 2
1000 2 3
2000 1 1
2000 2 3
2000 2 4
3000 1 1
3000 1 2
3000 2 3
3000 3 4
3000 3 5
I have a Search Screen where user can select multiple Materials (upto 5) to search for the appropriate PaintOrders. Also, use has the option to select AND/OR between materials. Please look at the attached image for clear understanding.
Some of the sample searches are as follows (Combination IDs are not needed in the Output; just PaintOrder IDs).
1. Find PaintOrders with MaterialIDs 1 AND 2 OR 4
2. Find PaintOrders with MaterialIDs 2 OR 3 OR 4
3. Find PaintOrders with MaterialIDs 3 AND 4 OR 5 OR 1
Hope you got the idea. I think it would require some Joins and GROUP BY which I am not expert at. I would appreciate any help.
Thanks
vmrao

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

It would help if you could provide the DDL for the tables, sample data in the form of an insert with unioned select statements.

Please, only provide enough detail to assist in helping with the problem. If the tables have many columns, only provide those relevent to the problem.

Also if you read this article it will guide you on what is needed to help you:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Lynn is right, if you post some more details you will get a better response.

In an "and" situation I would use where materialID in (1,2,3) wher I dynamically build the "IN". For or's you just need to append to the where clause WHere materialid = 1 or materialid = 1 ...

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

vmrao SSC Enthusiast Points: 133 More actions · Answer 3

I gave more thought on this and came up with some ideas.

The material expression would be evaluated as follows [where op is AND/OR].

((((m1 op1 m2) op2 m3) op3 m4) op4 m5)

I think I would need to write a procedure to accomplish this but I am not expert at it.

The procedure would be something like this (psuedo code).

Step1

------

Select paintOrderId from paintMatCom where MaterialId IN (materialIDs selected) -> This would retrieve only those PaintOrders associated with the material selections, would give few tens of records from thousands.

Step2

------

The selected materials are stored in an array, mat[].

// max 4 operators for 5 materials, default undefined op

// to 'and'

The selected operators are stored into an array of size 4, op[]

//Initialise matRes array, set 'false' for defined materials,

// default to 'true' for undefined materials

matRes[]

//Initialise paintOrdersFinal array

paintOrdersFinal[]

Step3

------

for Loop on PaintOrders result of Step1, var paintOrderId {

for Loop on mat[], i=0; {

//if select is successful, reset variable 'res' to true

select 'true' as @res From paintMatCom where materialId = mat and paintOrderId = @paintOrderId

//store the result into matRes array

matRes = @res;

} // Inner for loop ends

//Evaluate the expression to determine whether

// paintOrderId meets the criteria.

if ( ((((matRes[0] op[0] matRes[1]) op[1] matRes[2]) op[2] matRes[3]) op[3] matRes[4]) op[4] matRes[5]) )

{ add paintOrderId to paintOrdersFinal[] array }

} // outer for loop ends

return paintOrdersFinal[];

IF ANYONE CAN HELP WITH PROCEDUR FOR THE ABOVE PSUEDO CODE THAT WOULD BE GREAT.

Thanks

vmrao

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

If you would take the data you originally posted and put it and a simple table definition into the form that Lynn asked you to, that would be great 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)