First of all, what you are about to design is probably a VERY bad idea. A much better solution would be to have a dynamic schema where you add new tables and have the application understand how to query those table (you could place them in a schema). This largely avoids all the locking and query plan issues you are bound to run into with this model. There is nothing wrong with applications running CREATE TABLE
now and again.
Second, I am not sure I understand why you have normalised Parameter
into its own table? Why not put that directly into the ManufacturerParameter
table.
Third, if you insist on proceeding with your current model, there are ways to achieve what you want (at least if I am interpreting your requirement correctly). What you can do is to write your query in such a way that it is a sums up the search argument when there is a match and then use HAVING
to filter out the values that match. I am assuming that only one of the fields Text
, Boolean
, Datum
etc are populated per ProductParameter
record (you probably want to enforce this with a constraint)
For example, to search for all products that have a bolean = true for one parameter AND text = 'abc' for some other parameter you can do:
SELECT P.Name
FROM Product P
JOIN ProductParameter PP
WHERE P.ID = Foo
AND PP.Boolean = 1 OR PP.Text = 'abc' ... /* For each filter */
GROUP BY P.Name /* And any other things you want out of product */
HAVING COUNT(*) >= [Number of where clauses]
If you need to list all the parameters of this product, you can use the above query template as a nested query and join back to ProductParameter
.
The above query CAN be optimised by maintaining a computed column in ProductParameter
that has a string representation of the different data types in that table. That way, the above OR statements can be rewritten as an IN list (which you will want to pass as a table valued parameter).
I would like to repeat that what you are doing is probably very wrong. If you do it, you will most likely need to hand tune most of your query plans - the optimiser will not help you anymore. And that is assuming you don't have too many query variants, which will run your plan cache full.
You'll need to first create a list of every product_number
and date
combination. You can do this using a CROSS JOIN
of your table:
select distinct p.product_number, d.date
from yourtable p
cross join yourtable d;
See SQL Fiddle with Demo. This will create a list of data similar to:
| PRODUCT_NUMBER | DATE |
|----------------|---------------------------------|
| 100 | January, 01 2010 00:00:00+0000 |
| 200 | January, 01 2010 00:00:00+0000 |
| 100 | February, 01 2010 00:00:00+0000 |
You will then use the above query and LEFT JOIN
to your table to return the final result:
select
pd.product_number,
pd.date,
t.value
from
(
-- list of every product/date
select distinct p.product_number, d.date
from yourtable p
cross join yourtable d
) pd
left join yourtable t
on pd.date = t.date
and pd.product_number = t.product_number
order by pd.product_number, pd.date;
See SQL Fiddle with Demo. Giving a final result of:
| PRODUCT_NUMBER | DATE | VALUE |
|----------------|---------------------------------|--------|
| 100 | January, 01 2010 00:00:00+0000 | 1 |
| 100 | February, 01 2010 00:00:00+0000 | 1 |
| 100 | March, 01 2010 00:00:00+0000 | 1 |
| 200 | January, 01 2010 00:00:00+0000 | 1 |
| 200 | February, 01 2010 00:00:00+0000 | (null) |
| 200 | March, 01 2010 00:00:00+0000 | 1 |
The LEFT JOIN
returns all rows from your list of products and dates regardless of whether a matching row exists in the other table.
This could also be written as:
select
p.product_number,
d.date,
t.value
from
(
-- list of every product
select distinct product_number
from yourtable
) p
cross join
(
-- list of every date
select distinct date
from yourtable
) d
-- then join to the table
left join yourtable t
on d.date = t.date
and p.product_number = t.product_number
order by p.product_number, d.date ;
See SQL Fiddle with Demo. This may have better performance depending on your table size.
Now if you wanted to return a list of all dates, regardless of whether or not they appear in the table, then I would suggest creating a table of dates. This table would be used in a similar manner to create a list of all dates/products which you would then join.
The table would be similar to:
CREATE TABLE dates
(`date` datetime)
;
INSERT INTO dates
(`date`)
VALUES
('2010-01-01 00:00:00'),
('2010-02-01 00:00:00'),
('2010-03-01 00:00:00'),
('2010-04-01 00:00:00'),
('2010-05-01 00:00:00')
;
You'd then use the following query to get the list of dates/products:
select distinct p.product_number, d.date
from yourtable p
cross join dates d
And finally, you would join that back to your table:
select
pd.product_number,
pd.date,
t.value
from
(
-- list of every product/date
select distinct p.product_number, d.date
from yourtable p
cross join dates d
) pd
left join yourtable t
on pd.date = t.date
and pd.product_number = t.product_number
order by pd.product_number, pd.date;
See SQL Fiddle with Demo. Or an alternative:
select
p.product_number,
d.date,
t.value
from
(
-- list of every product
select distinct product_number
from yourtable
) p
cross join
dates
d
-- then join to the table
left join yourtable t
on d.date = t.date
and p.product_number = t.product_number
order by p.product_number, d.date ;
See SQL Fiddle with Demo. Again this may have better performance based on the table size. Using this type of solution, you'd return all dates even those not in your table:
| PRODUCT_NUMBER | DATE | VALUE |
|----------------|---------------------------------|--------|
| 100 | January, 01 2010 00:00:00+0000 | 1 |
| 100 | February, 01 2010 00:00:00+0000 | 1 |
| 100 | March, 01 2010 00:00:00+0000 | 1 |
| 100 | April, 01 2010 00:00:00+0000 | (null) |
| 100 | May, 01 2010 00:00:00+0000 | (null) |
| 200 | January, 01 2010 00:00:00+0000 | 1 |
| 200 | February, 01 2010 00:00:00+0000 | (null) |
| 200 | March, 01 2010 00:00:00+0000 | 1 |
| 200 | April, 01 2010 00:00:00+0000 | (null) |
| 200 | May, 01 2010 00:00:00+0000 | (null) |
Best Answer
Is this what you're looking for?
You might be able to modify the where clause like this to exclude unselected terms: