CODEWITHSUNDEEP
mysqlsql
HorusLupercal
HorusLupercal

Reputation: 13

SQL request with variable equal to current row column value in WHERE clause

Hi, just registered to ask this since I did not find answers after some research :

My table my_table has several columns, but only two matter here : id (integer, primary key, ...) and children (varchar that contains one or several {"number":"number"} as seen in the example below)

id | children
0  | {"0":"0"}
1  | {"1":"1"} {"2":"2"} {"3":"3"}
2  | {"2":"2"}
3  | {"3":"3"}
4  | {"4":"4"}
5  | {"5":"5"} {"6":"6"}
6  | {"6":"6"}

You can see that for any choosen row, the column children will always contain at least one occurence of {"number":"number"} where 'number' is equal to the value of column id of this row.

Sometimes it contains more than one occurence of {"number":"number"} with numbers of other rows' id.

I would like to build a SQL query that returns all rows :

I tried :

SELECT * FROM my_table 

WHERE children=CONCAT('{"', my_table.id, '":"', my_table.id, '"}')

This returns nothing obviously... I'm still searching but I guess some more experienced users will have a solution :)

EDIT1 : I wrote '=' instead of ':' in my query and never noticed it xD Thank you. The query is now correct and working as intended.

Upvotes: 0

Views: 965

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

Your method should work. There must be some other characters that you are missing.

Here is another method:

where children like '%{%' and
      children not like '%{%{%' and
      children like concat('%"', id, '":%') and
      children like concat('%:"', id, '"%')

This is just comparing each section of the pattern.

You might also try:

WHERE children LIKE CONCAT('%{"', my_table.id, '":"', my_table.id, '"}%') AND
      children NOT LIKE '%{%{%';

The most likely problem are spaces (or other characters) at the beginning or end of the string. This allows those, but doesn't allow multiple items in the list.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

Select all records with an ID mismatch (you had an equal sign where it must be a colon):

SELECT * 
FROM my_table 
WHERE children NOT LIKE CONCAT('%{"', my_table.id, '":"', my_table.id, '"}%');

Select all records with only one pair:

SELECT * 
FROM my_table 
WHERE children NOT LIKE '{%{%';

Combine the two somehow if you want a cobined result :-)

Upvotes: 1

Eduard Void
Eduard Void

Reputation: 2714

I agree with very bad db design notice.

your problem is maybe very simple - see the change in concat function

SELECT * FROM my_table 

WHERE children=CONCAT('{"', my_table.id, '":"', my_table.id, '"}')

Upvotes: 0

Related Questions