Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vmahesh
Starting Member
19 Posts |
 Posted - 2008-03-20 : 20:40:22
|
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 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 DataPaintOrderId 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 5I 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 42. Find PaintOrders with MaterialIDs 2 OR 3 OR 43. Find PaintOrders with MaterialIDs 3 AND 4 OR 5 OR 1Hope 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. Thanksvmrao |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-03-21 : 00:04:18
|
quote:
Originally posted by vmahesh1. Find PaintOrders with MaterialIDs 1 AND 2 OR 4
Sorry, but did you mean "(1 AND 2) or 4" or did you mean "1 AND (2 OR 4)"?quote:
Originally posted by vmahesh3. Find PaintOrders with MaterialIDs 3 AND 4 OR 5 OR 1
Sorry, but did you mean "(3 AND 4) OR 5 OR 1" or did you mean "3 AND (4 OR 5 OR 1)" or did you mean "(3 AND (4 OR 5)) OR 1"?Are you catching on to the fact that this is going to be MUCH more difficult than you anticipated?e4 d5 xd5 Nf6 |
 |
|
|
vmahesh
Starting Member
19 Posts |
Posted - 2008-03-21 : 18:26:47
|
| I am migrating MS access application into Java and this feature in MS access does not work. I was assuming 'AND' operators will get precedence over 'OR' operators in the expression. |
|
|
|
vmahesh
Starting Member
19 Posts |
Posted - 2008-03-22 : 10:30:03
|
| 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 materialsmatRes[]//Initialise paintOrdersFinal arraypaintOrdersFinal[]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[i] and paintOrderId = @paintOrderId //store the result into matRes array matRes[i] = @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 endsreturn paintOrdersFinal[];IF ANYONE CAN HELP WITH PROCEDUR FOR THE ABOVE PSUEDO CODE THAT WOULD BE GREAT.Thanksvmrao |
|
|
|
|
|
|
|
|
Complicated Query - SQL Gurus help please