-1

I have a simple SQL query running on MySQL, which doesn't return the results I expect. I would have sworn that it used to work, and I feel like I'm missing something obvious.

UPDATED - Sample data for testing

CREATE TABLE TABLE1 (
  ID INT AUTO_INCREMENT KEY
);
INSERT INTO TABLE1(ID) VALUES (1), (2), (3);

CREATE TABLE TABLE2 (
  parent_id INT,
  meta_key VARCHAR(100),
  meta_value VARCHAR(100)
);

INSERT INTO TABLE2(parent_id, meta_key, meta_value) VALUES 
    (1, 'John', 'some value'),
    (1, 'Sam', 'some value'),
    (1, 'Jill', 'some value'),
    (2, 'John', 'some value'),
    (3, 'Jill', 'some value')
;

The query is the following:

SELECT
  TABLE1.ID
FROM
  TABLE1
  LEFT JOIN
  TABLE2 ON
    (TABLE2.parent_id = TABLE1.ID) AND
    (TABLE2.meta_key IN ('JOHN', 'SAM', 'JILL'))
WHERE
  (TABLE2.meta_value IS NULL)

SQL Fiddle: https://sqlfiddle.com/mysql/online-compiler?id=157bfd6e-873e-4382-931c-0b53305575f2

The relationship between TABLE1 and TABLE2 is one to many. The purpose of the query would be to return all the rows in TABLE1 that don't have a corresponding match in TABLE2 for any of the values.

For example, if TABLE2 contains the following:

parent_id meta_key meta_value
1 John some value
1 Sam some value
1 Jill some value
2 John some value
3 Jill some value

I would expect the query to return IDs 2 nd 3, because they don't have a match for Sam and Jill (ID 2), or John and Sam (ID 3). Instead, the query doesn't return any result. It looks like, since ID 2 matches John, and ID 3 matches Jill, the fact that these IDs don't match other values is ignored.

As mentioned, my objective would be to fetch all IDs that are missing any of the matches. If they do match one or more values, that's fine, but I need to know which ones are missing.

It feels like the solution is easy enough and I just can't see it. 😅

2

4 Answers 4

0

So you want id's that don't have all of 'John', 'Sam' and 'Jill' among their meta_keys? That can't be easily done with a join because you're matching individual rows against an aggregated result.

Instead, find all the id's that do match your keys criteria and exclude them:

select 
  id 
from table1 
where id not in 
( select parent_id 
  from table2 
  where meta_key in ( 'John', 'Sam', 'Jill' )
  group by parent_id 
  having count( * ) = 3 /* must have all three values */ 
) 
order by 1 
2
  • having count( * ) = 3 must have all three values , it doesn't do what you are describing . It will get a result even for John,John,John or John,John,Jill and so on. having count( * ) = 3 needs to be replaced with having count(DISTINCT meta_key) = 3 Mar 26 at 15:18
  • This could work partially, but I also need to know which values are not matching. That's why I used a JOIN. Without the WHERE clause, I would have expected to always get three entries for each ID, some of which would have NULL as a matched value.
    – Diego
    Mar 26 at 15:20
0
CREATE TABLE test (
  parent_id INT,
  meta_key VARCHAR(50),
  meta_value VARCHAR(50)
  );
INSERT INTO test VALUES
(1, 'John', 'some value'),
(1, 'Sam',  'some value'),
(1, 'Jill', 'some value'),
(2, 'John', 'some value'),
(3, 'Jill', 'some value');
SELECT * FROM test;
parent_id meta_key meta_value
1 John some value
1 Sam some value
1 Jill some value
2 John some value
3 Jill some value
SELECT parent_id 
FROM test
-- WHERE meta_key IN ('Jill', 'John', 'Sam')
GROUP BY parent_id
HAVING GROUP_CONCAT( /* DISTINCT */ meta_key ORDER BY meta_key) = 'Jill,John,Sam';
parent_id
1

fiddle

Pay attention - the names in the criteria list which is used in HAVING must be ordered strictly in the alphabetic order.

If (parent_id, meta_key) is not defined as UNIQUE then uncomment DISTINCT.

If parent_id which contains meta_key values other than ones in your criteria must be selected too then uncomment WHERE clause.

0

If you are looking for parent_id that does not have a "complete" set of meta_values, something like:

select parent_id
from test
group by parent_id
having count(distinct meta_key) <> (select count(distinct meta_key) from test)

I borrowed @Akina 's Fiddle

0

I think I found the answer to my own question. As I suspected, I was making a simple mistake, which was in front of my eyes.

What I'm trying to fetch is a list like the following:

  1. ID 1 + John -> Value?
  2. ID 1 + Sam-> Value?
  3. ID 1 + Jill -> Value?
  4. ID 2 + John -> Value?
  5. ID 2 + Sam-> Value?
  6. ID 2 + Jill -> Value?
  7. ID 3 + John -> Value?
  8. ID 3 + Sam-> Value?
  9. ID 3 + Jill -> Value?

I need an entry for each ID + key value, whether there is a matching value or not. However, a simple LEFT JOIN is the wrong approach. The clause

LEFT JOIN
  TABLE2 ON
    (TABLE2.parent_id = TABLE1.ID) AND
    (TABLE2.meta_key IN ('John', 'Sam', 'Jill'))

means "fetch the entries that match John, Sam or Jill. If one or more is found, return all the matches. If none is found, return one entry with NULL." Long story short, I'm using the wrong approach.

Possible solution

I found a way to fetch the data I needed by creating a "virtual table" and a second join. Example:

-- INIT database
CREATE TABLE TABLE1 (
  ID INT AUTO_INCREMENT KEY
);
INSERT INTO TABLE1(ID) VALUES (1), (2), (3);

CREATE TABLE TABLE2 (
  parent_id INT,
  meta_key VARCHAR(100),
  meta_value VARCHAR(100)
);

INSERT INTO TABLE2(parent_id, meta_key, meta_value) VALUES 
    (1, 'John', 'some value'),
    (1, 'Sam', 'some value'),
    (1, 'Jill', 'some value'),
    (2, 'John', 'some value'),
    (3, 'Jill', 'some value')
;

-- Fetch all the entries from TABLE1, with the value from TABLE2 
-- matching the meta_key from TABLE3
SELECT
  TABLE1.ID
  ,TABLE3.meta_key
  ,TABLE2.meta_value
FROM
  TABLE1
  JOIN 
    (
      SELECT 'John' AS meta_key
      UNION ALL
      SELECT 'Sam' AS meta_key
      UNION ALL
      SELECT 'Jill' AS meta_key
     ) AS TABLE3
     
  LEFT JOIN
  TABLE2 ON
    (TABLE2.parent_id = TABLE1.ID) AND
    (TABLE2.meta_key = TABLE3.meta_key)
ORDER BY
    TABLE1.ID;
    
-- Fetch all the entries from TABLE1 for which the value of one or
-- more of the meta_keys from TABLE3 is NULL in TABLE2
SELECT
  TABLE1.ID
  ,TABLE3.meta_key
  ,TABLE2.meta_value
FROM
  TABLE1
  JOIN 
    (
      SELECT 'John' AS meta_key
      UNION ALL
      SELECT 'Sam' AS meta_key
      UNION ALL
      SELECT 'Jill' AS meta_key
     ) AS TABLE3
     
  LEFT JOIN
  TABLE2 ON
    (TABLE2.parent_id = TABLE1.ID) AND
    (TABLE2.meta_key = TABLE3.meta_key)
WHERE
    (TABLE2.meta_value IS NULL)
ORDER BY
    TABLE1.ID;

SQL Fiddle: https://sqlfiddle.com/mysql/online-compiler?id=babf6d7d-5dbe-4b52-9316-2ea37645d551

This returns the result I need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.