Tricky SQL SELECT problem (MySQL)

Question

Having this table (sorry, cant seem to get a decent layout here):

PD      Header         Text              Mask_Producer      Mask_Dep        Mask_Diam
----------------------------------------------------------------------------------------------
10      Producer       Aproducer         Aprod              *               *
10      Producer       Bproducer         Bprod              *               *
20      Diam           A                 Aprod              10              30
20      Diam           A                 Aprod              20              40
20      Diam           B                 Aprod              10              40
30      Dep            10                Aprod              10              *
30      Dep            20                Aprod              20              *
30      Dep            30                Aprod              30              *
20      Diam           A                 Bprod              20              40
30      Dep            10                Bprod              10              *

I am using the rows in the table as a filter/mask for selecting other rows.

So, user having already made a selection of rows with:

PD   Text
-------------
10   Aproducer
20   A

I would now like to find out what rows with PD=30 fits those previous choices:

PD=10, Text=Aproducer gives that Mask_Producer must be "Aprod", (Mask_Dep and Mask_Diam are allowed to be anything by the stars)

PD=20, Text=A gives that Mask_Producer must be "Aprod" and Mask_Dep must be 10 or 20 and Mask_Diam must be 30 or 40 (or star)

I want the outcome to be rows 6 and 7 from the table above.

Then imagine this example with 2000rows and 20 Mask_xx fields....

I am thinking SQL like IN, LEFT JOIN, JOIN, and temporary tables to do this, but I think I may be complicating things too much....

Answer 1

I think I understand what you are asking for but I am more familiar with SQL Server so if my syntax is off a little please forgive me. You should be able to do an inner join, joining the table to itself to get the results you want.

SELECT *
FROM tbData A
JOIN tbData B ON A.PD = B.Mask_Dep AND A.Mask_Producer=B.Mask_Producer
WHERE A.PD = 10
    AND A.Text='Aproducer'
UNION
SELECT *
FROM tbData A
JOIN tbData B ON A.PD = B.Mask_Dep AND A.Mask_Producer=B.Mask_Producer
WHERE A.PD = 20
    AND A.Text='A'

Looking at this I think I am a bit off but some of your data isn't clear. Could you give more universal rules for what you are looking for in the Mask_Dep and Mask_Diam?

Answer 2

I think you want something like this:

SELECT r.* FROM table AS c -- choices
JOIN table AS r            -- results
    ON (
        (r.mas_dep = '*' OR c.mask_dep = r.mask_dep)
        AND
        (r.mask_diam = '*' OR c.mask_diam = r.mask_diam)
    )
WHERE (
    ( c.pd = 10 AND c.text = 'Aproducer' )
    OR ( c.pd = 20 AND c.text = 'A' )
)
AND r.PD = 30

This will take the result of the choices:

PD      Header         Text         Mask_Producer   Mask_Dep    Mask_Diam
10      Producer       Aproducer    Aprod           *           *
20      Diam              A         Aprod           10          30
20      Diam              A         Aprod           20          40

And self-join it against the master table, where these conditions hold true r.mas_dep = '*' OR c.mask_dep = r.mask_dep and r.mask_diam = '*' OR c.mask_diam = r.mask_diam. This will return a set of:

PD      Header         Text         Mask_Producer   Mask_Dep    Mask_Diam
30      Dep              10         Aprod           10          *
30      Dep              20         Aprod           20          *

It will exclude the others clearly because their mask_deps of 30 isn't in the choice resultset, and it will exclude the rows pds not 30.

Answer 3

This is the type of code I enede up using, it works, but as I add more criteria and fields I get more huge joins and in each join also more AND..OR...OR sequences, so any propositions for improvements are welcome! Especially I am consernde that this may be slow as data grovs? Analysis of what the sql sever has to do for my code is welcome too!

Right now I have about 30 criteria/fields and some PHP code that generates the SQL query for me....

But here is the smaller example for the principle:

SELECT DISTINCT t30.*
FROM
  (SELECT *
   FROM YourTable
   WHERE PD = 10
     AND Text = 'Aproducer') AS t10
JOIN
  (SELECT *
   FROM YourTable
   WHERE PD = 20 AND Text = 'A') AS t20 ON 
     (t20.MaskProducer = t10.MaskProducer
                                OR t20.MaskProducer = '*'
                                OR t10.MaskProducer = '*')
     AND (t20.MaskDep = t10.MaskDep
                                OR t20.MaskDep = '*'
                                OR t10.MaskDep = '*')
     AND(t20.MaskDiam = t10.MaskDiam
                                OR t20.MaskDiam = '*'
                                OR t10.MaskDiam = '*')
//more JOINS like the one above for each criteria
// below the final join to get the result from
JOIN
  (SELECT *
   FROM YourTable
   WHERE PD = 30) AS t30 ON 
       (t30.MaskProducer = t20.MaskProducer
                             OR t30.MaskProducer = '*'
                             OR t20.MaskProducer = '*')
        AND (t30.MaskDep = t20.MaskDep
                             OR t30.MaskDep = '*'
                             OR t20.MaskDep = '*')
        AND (t30.MaskDiam = t20.MaskDiam
                             OR t30.MaskDiam = '*'
                             OR t20.MaskDiam = '*');