Tricky SQL SELECT problem (MySQL)
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开发者_JAVA技巧 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....
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?
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.
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 = '*');
精彩评论