Replace repetitive values with Null in MySQL query response

Question

I have two tables in my MySQL Server database with a common column:

Table1

+---------------------------------+
| columnA       | columnB         |
+---------------------------------+
|  123          |  lorem          |
|  456          |  ipsum          | 
|  456          |  dolor          |
+---------------------------------+

Table2

+---------------------------------+
| columnA       | columnC         |
+---------------------------------+
|  123          |  sit            |
|  123          |  amet           | 
|  123          |  consectetur    | 
|  456          |  adipiscing     |
+---------------------------------+

I want to get all values of columnB and columnC where columnA is 123. The query I'm using is:

SELECT Table1.columnB, Table2.columnC
FROM Table1
INNER JOIN Table2 ON Table1.columnA = Table2.columnA
WHERE Table1.columnA = '123';

Here's the query result:

+---------------------------------+
| columnB       | columnC         |
+---------------------------------+
|  lorem        |  sit            |
|  lorem        |  amet           | 
|  lorem        |  consectetur    | 
+---------------------------------+

I'm getting the required results but i don't want these repetitive values as in columnB in this example. Is there any way to replace (last two) redundant values lorem with either NULL or whitespace? Something like:

+---------------------------------+
| columnB       | columnC         |
+---------------------------------+
|  lorem        |  sit            |
|               |  amet           | 
|               |  consectetur    | 
+---------------------------------+

They are not redundant, they show a relationship. If you want to display data differently, do it in the presentation layer in the front end (app or reporting tool), not in the back end. — under, Jun 20 '17 at 03:35
thanks @under. Yeah you're right about that relationship and doing it in the front-end but I'm just wondering if there's any perfect/standard way to do it in backend. — Adil, Jun 20 '17 at 04:04
"I want to get all values of columnB and columnC where columnA is 123" is not clear. One would expect that you either mean rows that are in one or the other, or rows that are in both (with a 123). But from your query & example result you seem to mean columnB-columnC pairs where columnB appears with 123 in Table1 and columnC apears iwth 123 in Table2. You can't take shortcuts with language, it gives something ambiguous, wrong or nonsensical (euphemism: "unclear"). Also you call what you don't want the required results. Please make an effort to be clearer. — philipxy, Jun 20 '17 at 04:40
@philipxy Sorry if its not clear. I need the values from both the tables against columnA=123. I've updated the question for you with the sample output at the end of the question. No issue if whitespace can be replaced by NULL. is that clear now? — Adil, Jun 20 '17 at 04:43
Can you not see that "the values from both the tables against columnA=123" is just a bunch of words that have something to do with your thinking re your old query but don't say it? (See how I wrote it clearly.) (What would the definition even *be* for "against"?) (Rhetorical.) I did anticipate that picture from your old output & description of how it was bad. But: What is the output desired in *the general case* with multiple A values matched with multiple B values? Any relevant *PKs/UNIQUEs/CKs* & *FKs* here? When is an A kept vs null? This is why a question is off-topic without a [mcve]. — philipxy, Jun 20 '17 at 05:45
[Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) — philipxy, Jun 20 '17 at 05:51
@philipxy If you got what exactly I'm looking for and how it should be clearly written unlike how it is currently written, you're welcome to edit the question text. — Adil, Jun 20 '17 at 05:54

score 2 · Answer 1 · answered Jun 20 '17 at 03:52

You can use this. I hope it will work for you.

;WITH temp AS
(
    SELECT *,
           Row_number() over(partition by A.columnA order by A.columnC) as [Rn]           
    FROM 
    (
      SELECT Table1.columnA, Table1.columnB, Table2.columnC
      FROM Table1
      INNER JOIN Table2 ON Table1.columnA = Table2.columnA
      WHERE Table1.columnA = '123'
    ) A
)
SELECT 
(CASE WHEN [Rn] = 1 THEN columnB ELSE NULL END) columnB
,columnC
FROM temp

score 1 · Answer 2 · answered Jun 20 '17 at 05:43

Based on Tien Nguyen suggestion.

        SELECT  IF(ctr = 1, columnB, '') columnB ,
                columnC
        FROM    ( SELECT    a.columnB ,
                            b.columnC ,
                            ROW_NUMBER() OVER ( PARTITION BY a.columnA ORDER BY b.columnA ) AS ctr
                  FROM      Table1 A
                            INNER JOIN Table2 B ON b.columnA = a.columnA
                  WHERE     a.columnA = '123'
                ) tbl

RESULT:

    columnB     columnC
    ----------- ------------
    lorem       sit
                amet
                consectetur

    (3 row(s) affected)

Replace repetitive values with Null in MySQL query response

2 Answers2