CODEWITHSUNDEEP
mysqlsql
Liga
Liga

Reputation: 3439

How to filter and order by a row that exists on the same table?

I have a simple table with 2 columns "id" and "ink_number" and I would like to create another column called "nk_exists" that will have a value of 1 or 0 depending on if there is a row like it but with a "NK" at the beginning. And be able to sort by "nk_exits".

For example if there are two rows "123" and "NK123" then for row "123" nk_exists is true because there exists a row 123 with NK at the beginning.

Here is my original table "inkasso"

+-------+--------------+
| id  1 |  ink_number  |
+-------+--------------+
|     1 | 538032S      |
|     2 | NK538032S    |
|     3 | 114702A      |
|     4 | 159631D      |
|     5 | NK9761926001 |
|     6 | 9761926001   |
|     7 | 29-00002411L |
|     8 | 42032502V    |
|     9 | NK42032502V  |
|    10 | 454339KDB    |
+-------+--------------+

I want the end result to look like this

+-------+--------------+-----------+
| id  1 |  ink_number  | nk_exists |
+-------+--------------+-----------+
|     1 | 538032S      |         1 |
|     2 | NK538032S    |         0 |
|     3 | 114702A      |         0 |
|     4 | 159631D      |         0 |
|     5 | NK9761926001 |         0 |
|     6 | 9761926001   |         1 |
|     7 | 29-00002411L |         0 |
|     8 | 42032502V    |         1 |
|     9 | NK42032502V  |         0 |
|    10 | 454339KDB    |         0 |
+-------+--------------+-----------+

My query is getting a synax error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax

select
    inkasso.id,
    inkasso.ink_number,
    jointable.nk_exists
from inkasso
left join (
    select
        case
            when exists 1
            when not exists 0
        end as nk_exists
    from inkasso
    where exists(
        select
            1
        from inkasso ink
        where left(ink_number, 2) = 'NK' and inkasso.id = ink.id
    ) as subjoin on 
) as jointable on inkasso
WHERE jointable.nk_exists = 1
ORDER BY jointable.nk_exists

Upvotes: 0

Views: 45

Answers (2)

Amadan
Amadan

Reputation: 198446

SELECT *,
  CONCAT('NK', ink_number) IN (SELECT ink_number FROM inkasso) AS nk_exists
FROM inkasso;

Fiddle

Upvotes: 2

Robert Kock
Robert Kock

Reputation: 6028

If sou want to add the column nk_exists, you might try something like this:

SELECT          ink.id,
                ink.ink_number,
                CASE
                  WHEN nk_ink.id IS NULL THEN 0
                  ELSE                        1
                END AS nk_esists
FROM            inkasso ink
LEFT OUTER JOIN inkasso nk_ink
             ON nk_ink.ink_number = 'NK' + ink.ink_number

In case you only want to filter on that column, you could try the following:

SELECT ink.id,
       ink.ink_number,
FROM   inkasso ink
WHERE  EXISTS (SELECT nk_ink.id
               FROM   inkasso nk_ink
               WHERE  nk_ink.ink_number = 'NK' + ink.ink_number)

Upvotes: 1

Related Questions