Search in SQL WHERE value is SELECT, why now working?-CodePudding

I want to SELECT all the rows where the value is the same as the selected value. I mean example:

id	phone_num	name
1	0123456789	VALUE 1
2	0123456789	VALUE 2
3	0987654321	VALUE 3

So for example: I want to SELECT the number 1 ID phone number and check if the phone_number is uniqe or duplicated.

I tried with this query but not working:

SELECT * 
FROM customers a, (SELECT b.phone_num as phone_search
                   FROM customers b
                   WHERE b.id = "1") AS total  
WHERE phone_num LIKE "%"|| total.phone_search ||"%"

CodePudding user response：

You should use a subquery in the WHERE clause:

SELECT *
FROM customers
WHERE phone_num = (SELECT phone_num FROM customers WHERE id = 1);

More generally, if you just want to flag any numbers which are duplicate, use an aggregation query:

SELECT phone_num
FROM customers
GROUP BY phone_num
HAVING COUNT(*) > 1;