MySQL: how to delete one row of a multi-line record based on a column
If I have two tables that I am joining to and I write the most simple query like this:
SELECT *
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
There are several records that have multiple lines per ID because they have multiple employers, so t1
it looks like this:
ID Name Employer
12345 Jerry Comedy Cellar
12345 Jerry NBC
12348 Elaine Pendant Publishing
12346 George Real Estate
12346 George Yankees
12346 George NBC
12347 Kramer Kramerica Industries
t2
linked to similar ids, but with some action I would like to see - hence the SELECT *
above. While I don't want multiple rows returned if the column Employer
is "NBC", everything else is fine.
The only thing that matters here is what is t2
less than t1
, because t1
- that's everything, but t2
- only people who performed certain actions - so some of the matches won't return anything from t2
, but I would still like. to be returned, therefore LEFT JOIN
.
If I write my request like this:
SELECT *
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE Employer <> "NBC"
Then it removes Jerry and George entirely - when really all I want is not to keep the NBC string from returning, but to return any other strings associated with them.
How can I write a request for accession t1
to the t2
to return to each line, except for NBC? The ideal output would be all lines from t1
regardless of whether they match all parameters t2
, except for deleting all lines with "NBC" as employer in the return file. Basically, the ideal here is to return JOINs where they fit, but regardless of deleting the entire line for anyone with "NBC" as an employer, without deleting other lines.
The more I write about it, it seems to me that I should just run a query before mine JOIN
to delete all lines in t1
that have "NBC" as their employer, and then run a regular query.
source to share
Basic subset filtering
You can filter out one of the two combined (joined) subsets by expanding the sentence ON
.
SELECT *
FROM t1
LEFT JOIN t2
ON t1.ID = t2.ID
AND t2.Employer != 'NBC'
If you are now getting values null
and you don't want them, add:
WHERE t2.Employer IS NOT NULL
extended logic:
SELECT *
FROM t1
LEFT JOIN t2
ON (t1.ID = t2.ID AND t2.Employer != 'NBC')
OR (t2.ID = t2.ID AND t2.Employer IS NULL)
Using UNION
Basically, it is JOIN
intended for horizontal binding, while UNION
vertical binding of datasets.
It merges with result sets: the first one without NBC, and the second (which is basically OUTER JOIN
) adds everyone to t1, which is not part of t2.
SELECT *
FROM t1
LEFT JOIN t2
ON t1.ID = t2.ID
AND t2.Employer != 'NBC'
UNION
SELECT *
FROM t1
LEFT JOIN t2
ON t1.ID = t2.ID
AND t2.Employer IS NULL
Processing rows in a result set
If you just want to delete NBC
as a string, this is a workaround:
SELECT
t1.*,
IF (t2.Employer = 'NBC', NULL, t2.Employer) AS Employer
FROM t1
LEFT JOIN t2
ON t1.id = t2.id
This basically replaces "NBC"
withnull
source to share