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

Answer 2

select PURCHASE_DATE, PRICE from table group by PURCHASE_DATE

Answer 3

This will give you a slightly different format for the results, but it might be useful:

Select 
t1.id as t1_id, t1.id_book as t1_id_book, t1.price as t1_price, t1.data as t1_data,
t2.id as t2_id, t2.id_book as t2_id_book, t2.price as t2_price, t2.data as t2_data
from table t1
inner join table t2 on t1.data = t2.data
and t1.id_book = 1
and t2.id_book = 2

You would get :

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