Operand should contain 1 column(s) - how to get extra info from subquery?
I've been writing "basic" mysql queries for years for various php apps, but they're simple, and I'm just now hitting some things I don't know how to do. I appreciate any help very much!
Tables:
I have a 'persons' table that holds people, and a 'persons_tags' join table that tells me which people belong to which tags, and each of those can also have a start date and end date.
Problem:
I need to return people
- who are in persons_tags, but the end (expiration) date is not within 3 years of today (So, they're "expired")
OR
- *who do not have the tag AT ALL* (ie they're not even IN the persons_tags join table for that tag_id - 170)
I don't have any trouble using my "basic mysql skills" to return that list of people. BUT,
IF/WHEN they ARE in the join table, and they have 'persontag_enddate' populated, I'd like to output that date next to their name, so the viewer can see WHEN they expired.
I know how to use "date_format(b.persontag_enddate, '%M %e, %Y') as display_persontag_enddate" to get that date, but I don't know where to put it, since I can't directly query persons_tags without a subquery since I need to get the people who aren't in persons_tags at all (for that tag_id 170).
Here are my tables, followed by my "basic mysql" attempt with the date_format part in the wrong place that doesn't work:
mysql> mysql> describe persons;
+---------------------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------------------+----------------+
| person_id | int(8) | NO | PRI | NULL | auto_increment |
| client_id | int(8) | YES | | 0 | |
| status_id | int(8) | YES | | 0 | |
| person_withdrawn | int(8) | YES | | 0 | |
| person_first | varchar(75) | YES | | NULL | |
| person_last | varchar(75) | YES | | NULL | |
| person_retired | int(8) | YES | | 0 | |
| person_datebirth | datetime | YES | | NULL | |
+---------------------------+--------------+------+-----+---------------------+----------------+
(simplified this output; basic fields are above)
mysql> describe persons_tags;
+---------------------+----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+----------+------+-----+---------------------+-------+
| person_id | int(8) | NO | PRI | 0 | |
| tag_id | int(8) | NO | PRI | 0 | |
| persontag_startdate | datetime | YES | | 1776-07-04 00:00:00 | |
| persontag_enddate | datetime | YES | | 1000-01-01 00:00:00 | |
| persontag_notes | blob | YES | | NULL | |
+---------------------+----------+------+-----+---------------------+-------+
(the entire table is above)
My query start:
(don't know where to put "date_format(b.persontag_enddate, '%M %e, %Y') as display_persontag_enddate")
-----------------------------------------
select * from persons
where
client_id = 20 and
status_id = 1 and
person_withdrawn != 1 and
(
(
person_id NOT IN
(
select a.person_id from persons a, persons_tags b where a.person_id = b.person_id and b.tag_id = 170
)
)
or
(
person_id IN
(
select a.person_id from persons a, persons_tags b where a.person_id = b.person_id and b.tag_id = 170 and
b.persontag_startdate NOT BETWEEN DATE_SUB(NOW(), INTERVAL 3 YEAR) AND NOW()
)
)
)
group by person_id
order by
person_last,
person_first
-----------------------------------------
*If I try to put the "date_format(b.persontag_enddate, '%M %e, %Y') as display_persontag_enddate" part IN the subquery then I get:
ERROR 1241 (21000): Operand should contain 1 column(s)
I don't know how to do what I'm trying to do all in one query without having to use php code and a loop to do it, which would be wonderful to avoid for my structure/use case.
Any help is very much appreciated!