I have created the following query:
SELECT wvwcs1.*, wvwcs2.* FROM wvwcs1
inner join wvwcs2 on wvwcs1.id = wvwcs2.wvwcs1_id
inner join wvwcs2 w2 on wvwcs1.id = wvwcs2.wvwcs1_id AND wvwcs2.value LIKE '%ee%'
My tables are created like so:
CREATE TABLE `wvwcs1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`form_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COMMENT=' ';
CREATE TABLE `wvwcs2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`wvwcs1_id` int(10) unsigned NOT NULL,
`value` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;
The data, as an example just using two records:
mysql> select * from wvwcs1;
+----+---------+
| id | form_id |
+----+---------+
| 1 | 23 |
| 2 | 24 |
+----+---------+
2 rows in set (0.00 sec)
mysql> select * from wvwcs2;
+----+-----------+-------+
| id | wvwcs1_id | value |
+----+-----------+-------+
| 1 | 1 | aa |
| 2 | 1 | bb |
| 3 | 1 | cc |
| 4 | 2 | aa |
| 5 | 2 | cc |
| 6 | 2 | dd |
| 7 | 2 | ee |
+----+-----------+-------+
2 rows in set (0.00 sec)
What I need to do, is to select all the records from wvwcs2 where ONE of the records in wvwcs2 is a given value. So, assume that we want to select records where wvwcs2.value = dd. In this case, we would get returned:
+----+-----------+-------+
| id | wvwcs1_id | value |
+----+-----------+-------+
| 4 | 2 | aa |
| 5 | 2 | cc |
| 6 | 2 | dd |
| 7 | 2 | ee |
+----+-----------+-------+
When I run this query currently, it gives me all records. I thought I would need to do an inner join on the same table, but I must be off. I am stumped.
Try this:
SELECT A.*, C.form_id
FROM wvwcs2 A JOIN
(SELECT DISTINCT wvwcs1_id FROM wvwcs2 WHERE LOWER(`value`) LIKE '%dd%') B
ON A.wvwcs1_id=B.wvwcs1_id
LEFT JOIN wvwcs1 C
ON A.wvwcs1_id=C.id;
See Demo on SQL Fiddle.