MySQL Forums
Forum List  »  Newbie

Operand should contain 1 column(s) - how to get extra info from subquery?
Posted by: Alan Densmore
Date: February 05, 2019 04:31PM

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!

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.