Is there a way to select one value and other same values transform to null

Question

I have 3 connected tables and I have a single query to get a values from them. All of them have duplicates. Now I need all duplicates from 2 tables, but from third table I want to get only first distinct value and other to transform to NULL.

This is my query:

SELECT 
  operacije.nazivEng, slike.img_name, izvestaji.operacija 
FROM izvestaji 
  INNER JOIN operacije
    ON izvestaji.operacijaId=operacije.id 
  INNER JOIN slike 
    ON izvestaji.id=slike.izvestajId 
WHERE izvestaji.operacija IN (
    SELECT operacija 
    FROM izvestaji 
     WHERE projekatId='8' 
        AND datum='2019-10-03' 
     GROUP BY operacija) 
  AND izvestaji.projekatId='8' AND izvestaji.datum='2019-10-03' 
GROUP BY slike.img_name,operacije.nazivEng, izvestaji.operacija 
ORDER BY operacije.nazivEng DESC;

see the image

I tried this also

SELECT 
  operacije.nazivEng, slike.img_name, izvestaji.operacija 
FROM izvestaji 
  INNER JOIN operacije 
    ON izvestaji.operacijaId=operacije.id 
  INNER JOIN slike 
    ON izvestaji.id=slike.izvestajId 
WHERE izvestaji.operacija IN (
           SELECT DISTINCT operacija 
           FROM izvestaji 
          WHERE projekatId='8' AND datum='2019-10-03') 
       izvestaji.operacija IN (
                 SELECT operacija 
                 FROM izvestaji 
                 WHERE projekatId='8' AND datum='2019-10-03' 
                 GROUP BY operacija) 
     AND izvestaji.projekatId='8' AND izvestaji.datum='2019-10-03' 
GROUP BY slike.img_name,operacije.nazivEng, izvestaji.operacija 
ORDER BY operacije.nazivEng DESC

Ankit Bajpai · Accepted Answer · 2019-10-07T11:34:48.133

1

If you are using MySQL version 8.0 or greater, You can easily achieve this with LAG analytical function -

SELECT operacije.nazivEng
      ,slike.img_name
      ,CASE WHEN izvestaji.operacija <> LAG(izvestaji.operacija) OVER(PARTITION BY operacije.nazivEng ORDER BY slike.img_name)
                 THEN izvestaji.operacija
            ELSE
                 NULL
       END operacija
FROM izvestaji 
INNER JOIN operacije ON izvestaji.operacijaId=operacije.id 
INNER JOIN slike ON izvestaji.id=slike.izvestajId 
WHERE izvestaji.operacija IN (SELECT operacija 
                              FROM izvestaji 
                              WHERE projekatId='8' 
                              AND datum='2019-10-03' 
                              GROUP BY operacija)
AND izvestaji.projekatId='8'
AND izvestaji.datum='2019-10-03' 
GROUP BY slike.img_name
        ,operacije.nazivEng
        ,izvestaji.operacija 
ORDER BY operacije.nazivEng DESC;

edited Oct 07 '19 at 11:34

answered Oct 07 '19 at 08:00

Ankit Bajpai

13,128
4
25
40

I get this error ```#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.operacija FROM izvestaji INNER JOIN operacije ON izvestaji.operacijaId=opera' at line 7 ``` but if I remove izvestaji.operacija before "FROM" then I get this error ``` #4018 - No order list in window specification for 'lag' ``` – Aleksandar Milosevic Oct 07 '19 at 11:27
Can you please try now? – Ankit Bajpai Oct 07 '19 at 11:35
This works perfectly on localhost. Thanks. But on my online server I get this error. ``` #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OVER(PARTITION BY operacije.nazivEng ORDER BY slike.img_name) ' at line 3 ``` Do you know what can be a problem here? – Aleksandar Milosevic Oct 07 '19 at 11:45
What is your MySQL version? I have mentioned this in the answer also, window functions are only allowed on version 8.0 or higher. – Ankit Bajpai Oct 07 '19 at 12:24

Is there a way to select one value and other same values transform to null

1 Answers1