How to do this query?

Question

Hello everybody!

I have a mysql table with these columns:

    * ID (auto-increment)
    * ID_BOOK (int)
    * PRICE (double)
    * DATA (date)

I know two ID_BOOK values, example, 1 and 2.

QUERY:

I have to extract all the PRICE (of the ID_BOOK=1 and ID_BOOK=2) where DATA is the same!

Table example:
-------------------------------------------------------
1  1  10.00  2010-05-16

2  1  11.00  2010-05-15

3  1  12.00  2010-05-14

4  2  18.00  2010-05-16

5  2  11.50  2010-05-15
===========================================
Result example:
----------------------------------------------------------
1  1  10.00  2010-05-16

4  2  18.00  2010-05-16

2  1  11.00  2010-05-15

5  2  11.50  2010-05-15
----------------------------------------------------------
ID_BOOK=2 hasn't 2010-05-14 so i jump it.

Thank you so much!

Answer 1

I've taken a guess at how you want hte results odered and you'll need to replace myTable with the actual name of your table.

SELECT *
FROM myTable
WHERE DATA IN
(
    SELECT
        DATA
    FROM myTable
    GROUP BY
        DATA
    HAVING COUNT(*) > 1
)
ORDER BY
    DATA DESC,
    ID_BOOK,
    ID