Here is my code that I have and I cannot figure out why the following code returns the error Operand should contain 1 column(s). I know that both queries work independantly of the query I have below. ANy help would be appreciated
SELECT *
FROM 2014_summary, 2014_profiles
WHERE 2014_summary.player_id = 2014_profiles.player_id AND tournament_id = 'bec904c4-b2a3-4eba-a2bc-335aca680a40' AND 2014_summary.player_id
NOT IN
(SELECT
T1.*,
T2.player_id as primary_player_id
FROM (SELECT id, lead_id, form_id, MAX(case when field_number = 1 then value end) display_name,
MAX(case when field_number = 7 then value end) email,
MAX(case when field_number = 6 then value end) tournament_name,
MAX(case when field_number = 3 then value end) primary_golfer,
MAX(case when field_number = 4 then value end) backup_golfer,
MAX(case when field_number = 5 then value end) date,
MAX(case when field_number = 8 then value end) tournament_id
FROM `wp_rg_lead_detail`
GROUP BY lead_id)T1
INNER JOIN (SELECT CONCAT(first_name, ' ', last_name) as player_name, player_id FROM 2014_profiles)T2 ON T1.primary_golfer = T2.player_name
WHERE display_name = "Hosker" AND tournament_id != 'bec904c4-b2a3-4eba-a2bc-335aca680a40')
ORDER BY (last_name) ASC
Your not in
is:
2014_summary.player_id NOT IN
(SELECT T1.*, T2.player_id as primary_player_id
MySQL doesn't know which column to use in the select
for comparison to player_id
. So it returns an error. Actually, this is ANSI standard SQL functionality, and all databases work this way. The subquery in an in
needs to return only one column.
I think you want:
2014_summary.player_id NOT IN
(SELECT TT2.player_id
. . .