Php mysql select all with same data

Question

I have a table with 3 columns, incremental identifier, name and data

What am i trying to achieve

you enter $ name ... and return $ data ...
then it takes $ data and finds all $ names that have the same $ data.

SELECT * FROM table WHERE name ='$name' data='$data' and data!='0'

doesn't seem to cut it.

I'm basically trying to get all rows that have the same data as the entered data $ name $.

early.

Answer 1

You can try the following:

SELECT  t2.name
FROM    `table` t1
JOIN    `table` t2 ON (t2.data = t1.data)
WHERE   t1.name = '$name' AND t1.data != '0';

Test case:

CREATE TABLE `table` (id int, name varchar(10), data varchar(10));

INSERT INTO `table` VALUES (1, 'name1', 'data-a');
INSERT INTO `table` VALUES (2, 'name2', 'data-b');
INSERT INTO `table` VALUES (3, 'name3', 'data-a');
INSERT INTO `table` VALUES (4, 'name4', 'data-b');
INSERT INTO `table` VALUES (5, 'name5', 'data-a');

Result:

SELECT  t2.name
FROM    `table` t1
JOIN    `table` t2 ON (t2.data = t1.data)
WHERE   t1.name = 'name2' AND t1.data != '0';

+-------+
| name  |
+-------+
| name2 |
| name4 |
+-------+
2 rows in set (0.00 sec)

The nested solution proposed by @Borealid is another valid solution that returns the same result:

SELECT  name 
FROM    `table` 
WHERE   data IN (SELECT data FROM `table` WHERE name = 'name2');

+-------+
| name  |
+-------+
| name2 |
| name4 |
+-------+
2 rows in set (0.00 sec)

Answer 2

SELECT * FROM table WHERE name ='$name' and data='$data' and data!='0'

and

EDIT:

select * from table where
 name in 
   ( select data from table where name = '$name' )

Answer 3

, :

SELECT * FROM table t1,table t2 WHERE t1.name = '$name' AND t1.data=t2.data AND t1.name != t2.name

:

SELECT name FROM table WHERE data IN (SELECT data FROM table WHERE name='$name')

. .

What is wrong with the original request, as published, is that you are missing an AND. But he will also not do what you say you want.