CODEWITHSUNDEEP
mysql
Fyodor Iz
Fyodor Iz

Reputation: 77

How to compare two columns in the same table, and get a distinct one

I have a table let's call it a 'options' and some columns in it: 

CREATE TABLE IF NOT EXISTS `options` (`key` VARCHAR(255), `value` VARCHAR(255), `user` VARCHAR(255));

A little data with NULL user field for exmaple:

INSERT INTO `options` (`key`, `value`) VALUES ('port', '8080'), ('length', '3000'), ('country', 'USA');

And a one row with a user field named 'John':

 INSERT INTO `options` (`key`, `value`, `user`) VALUES ('port', '5000', 'John');

First couple of rows have 'User' column set NULL. Other rows in 'User' column could have names like 'John' (for example, lets take only one). Think about the 'User' columns with NULL values as a default set of options that I always get out from the database, but some Users like John have individual options and I need to get his option(s) prior to default ones. Here is an image of table:

I need a query to get Johns options 'key', 'value' parameters where 'User' is NULL, but if some 'User' like 'John' has something different from default options (if the 'key' match, 'port' in this case) I need it to show only John's port, and the others are with 'User' = null, without duplicates, so I need the answer like this:

key | value | user
port | 5000 |John
length | 3000 | NULL
country | USA | NULL

While the database consists of this:

key | value | user
port | 8080 |NULL
length | 3000 | NULL
country | USA | NULL
port | 5000 | John

I tried a lot of queries but none of them gave me the positive result. Tried something with self join, right join, left join, disctinct columns, with IFNULL() function, but everything was wrong. I searched a lot , but can't find an answer to this, so I am humbly ask for help or advice here.

Upvotes: 0

Views: 2727

Answers (2)

George Pant
George Pant

Reputation: 2117

You can first fetch the default key,values pairs, excluding the ones that have a value for the corresponindg user and then use UNION and fetch the key,values pairs for the user.

A query using NOT IN and a subquery:

SELECT * FROM `options` WHERE `user` IS NULL 
AND `key` NOT IN 
(SELECT `key` FROM `options` WHERE `user`='John')
UNION ALL 
SELECT * FROM `options` WHERE `user`='John'

The same query using JOIN

SELECT o.* FROM `options` o 
LEFT JOIN 
(SELECT `key` FROM `options` WHERE `user`='John') o2
ON o.key=o2.key WHERE o2.key IS NULL AND o.user IS NULL
UNION ALL
SELECT * FROM `options` WHERE `user`='John'

Upvotes: 2

The Impaler
The Impaler

Reputation: 48770

Use the CASE clause to apply simple logic to a query. For example:

select
  key,
  case when user is not null then user else value end as value
from t

Upvotes: 1

Related Questions