I have a table with 3 cols, incremeting id, name, and data
What i'm trying to achieve
you enter a $name... and returns $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'
does not seem to cut it..
I'm basically trying to get all rows that have the same data as the entered $name's $data.
thanks in advance.
SELECT * FROM table WHERE name ='$name' and data='$data' and data!='0'
You were missing an and
EDIT:
select * from table where
name in
( select data from table where name = '$name' )
Try joining the table with itself, if you must do this in one query:
SELECT * FROM table t1,table t2 WHERE t1.name = '$name' AND t1.data=t2.data AND t1.name != t2.name
Alternately, use a nested query:
SELECT name FROM table WHERE data IN (SELECT data FROM table WHERE name='$name')
I recommend the nested query. It's easier to read.
What's wrong with your original query as posted is that you're missing an 'AND'. But it also won't do what you say you want it to.
WHERE should be after the LEFT JOIN. Nevertheless, I don't think this is what the OP is looking for. Your nested query solution is good though :)You may want to 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 query solution suggested 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)
name2 as $name. Now name2 has data-b, which is shared by name4, so the query returns name2 and name4. Is this right?