Need an sql query...

[praskuma]

I have these two tables..

Product
=============================
ID Title Description
=============================
1 My first product First description
2 My second product Second description
… … …
…. …. ….

META
=============================
ID PRODID KEY VALUE
=============================
1 1 StartDate 22-MAR-2009
2 1 EndDate 23-MAR-2009
3 2 StartDate 28-MAR-2009
4 2 EndDate 29-MAR-2009
5 3 StartDate 05-APR-2009
6 3 EndDate 10-APR-2009
.. .. .. ..
.. .. .. ..

I want to get the list of all products which have currentdate between startdate and enddate.

I know this can be achived by using joins, but im not so good at it.

If you can help me with the query it would be great.

PS: startdate and enddate are not fields....

Thanks
Prasanna

[darkagn]

Hi praskuma and welcome to DaniWeb :)

-- First, what info do you need?
-- I have assumed just the title and description fields are what you want returned from this query
SELECT Title, Description FROM Product
-- Now we need to join to the Meta table to limit the results accordingly
INNER JOIN Meta
-- how are the tables related? By Product ID
ON Product.ID = Meta.PRODID
-- limit the results
-- start date must be less than current date
WHERE (Meta.KEY = 'StartDate' AND Meta.VALUE < NOW())
-- and end date must be greater than current date
AND (Meta.KEY = 'EndDate' AND Meta.VALUE > NOW())

I haven't tested my query, but it should at least give you a solid starting point. The comments in the SQL have described what I am trying to do for you, but if you don't understand what I have written let me know and I will try to elaborate.

Hope this helps. :)

[praskuma]

Hi

Thanks for responding...

I tested this sql query...
but it dint give the intended result.....
According to the suggestion given by one of my friend i modified the last AND statement as given below and it worked...athough i dont know the difference b/w both it works form me....

SELECT Title, Description FROM Product
INNER JOIN Meta
ON Product.ID = Meta.PRODID
WHERE (Meta.KEY = 'StartDate' AND Meta.VALUE < NOW())
AND Product.ID IN (select id from meta where
Meta.KEY = 'EndDate' AND Meta.VALUE > NOW());

[darkagn]

Ah yes, I do see the difference, but I think that your second select should be on prodid not id?

[praskuma]

Yes you are right, thats a type, it should be prodid and not id