Question 58199 in Mysql Question posted in
The official MySQL documentation can be found here.

Writing a query to get a value depending on values of 2 rows in MySQL

I have a database table named order_data that contains data like this:

id order_id data_value data_key
9825 1156 2023-05-15 date
9826 1156 123 product_id

Each order (order_id) generates these rows, so there can be multiple rows in this table which have the same order_id.

How do I write a MySQL query to get the order_id when:

  • data_key = date AND data_value = 2023-05-15
  • data_key = product_id AND data_value = 123

I know how to do this for each individual one above, but not sure if trying to account for both of the above together, can it be done in a single query?

I have tried:

SELECT order_id FROM `order_data` WHERE `data_key` = 'product_id' AND `data_value` = '123' AND `data_key` = 'date' AND `data_value` = '2023-05-15';

But this does not return the order_id, returns no results.

Tags:

2

Answers


  1. 0

    One option would be using EXISTS

    Consider the following data example

    create table order_data (
  id int ,
  order_id int ,
  data_value varchar(15) ,
  data_key varchar(15)
) ;


insert into order_data values 
(9825,1156,'2023-05-15','date'),
(9826,1156,'123','product_id');

    Query

    select * from order_data
where order_id=1156
AND data_value='2023-05-15' AND data_key='date' 
AND EXISTS (select 1 from order_data where data_value='123' AND data_key='product_id') ;

    An index on (order_id,data_value,data_key) will speed up the query

    Demo

    Login or Signup to reply.
  2. 0

    You can do it using inner join as follows :

    SELECT DISTINCT t.order_id
FROM order_data t
inner join (
  SELECT order_id 
  FROM order_data 
  WHERE data_key = 'date' AND data_value = '2023-05-15'
) as s on s.order_id = t.order_id
WHERE t.data_key = 'product_id' AND t.data_value = '123';

    DISTINCT will prevent getting duplicates order_id if somehow one of the rows is duplicated

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search