0

For context, I'm pretty new to SQL.

I'm doing a Leetcode problem #608, don't want it solved for me, but here it is for reference:

https://leetcode.com/problems/tree-node/

The table has 2 columns:

id: int
p_id: int

I basically want to see for each id value whether or not it exists in the p_id column.

I tried the following to test this:

SELECT id NOT IN (p_id) 
FROM tree

What I want is, using this input as reference, is an output of (1, 1, 0, 0, 0) Boolean values.

However, from what I currently UNDERSTAND, this doesn't work because the column p_id is not in a comma-separated list. Is there a way to convert a column into a comma separated list? Or is there another way to approach this to get my desired outcome?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jcjcjcjc
  • 1
  • Remember that in the select clause you show data from one row at a time. This means you are looking at a row's id and p_id. This means `id NOT IN (p_id)` is the same as `id <> p_id`. You want to select all p_id from the table instead in order to look for the id in that data set. – Thorsten Kettner Jul 14 '22 at 20:15
  • 1
    Going to a comma-separated list in a database is the wrong direction, an anti-pattern even: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/1115360) (TLDR: yes). – Andrew Morton Jul 14 '22 at 20:16

1 Answers1

0

yes you can use a a cooma seperated list or simply make a sub select like

 SELECT id  from tree WHERE id NOT IN (SELECT p_id FROM tree)

The result set acts like the list

nbk
  • 45,398
  • 8
  • 30
  • 47