0

I can work on basic queries and have been having a bit of trouble the way Wordpress stores their database information. The below query I have been using to download all form submissions for contests that we run. I usually download the results and then sort what I need to using excel or google sheets. Our last bunch of contests have had a lot of submissions, and I would like to take the following query and take it one step further :

SELECT b.id, b.date_created, a.lead_id, a.form_id,
   max(case when a.field_number = 13 then value end) as FirstName,
   max(case when a.field_number = 14 then value end) as LastName,
   max(case when a.field_number = 15 then value end) as Address,
   max(case when a.field_number = 16 then value end) as Address2 ,
   max(case when a.field_number = 17 then value end) as City,
   max(case when a.field_number = 18 then value end) as Province,
   max(case when a.field_number = 19 then value end) as PostalCode,
   max(case when a.field_number = 20 then value end) as Country,
   max(case when a.field_number = 4 then value end) as PhoneNumber,
   max(case when a.field_number = 3 then value end) as EmailAddress,
   max(case when a.field_number = 44 then value end) as Question,
   max(case when a.field_number LIKE 42.1 then value end) as ClientNewsletter,
   max(case when a.field_number LIKE 27.1 then value end) as LocalNewsletter
FROM wp_rg_lead_detail a
LEFT JOIN wp_rg_lead b
ON a.lead_id = b.id
WHERE a.form_id = 45
GROUP BY a.lead_id;

This gets me my full list of entries. I would like to be able to generate three more results.

This current result I would like to get unique entries by EmailAddress

The two new lists I would like are based on whether ClientNewsletter is selected, as well as LocalNewsletter.

Any help is appreciated.

  • Please consider reading this suggestion – mustaccio Jan 8 at 16:32
  • Welcome to the ugly world of EAV. See the link I added. – Rick James Jan 8 at 21:53
  • What do you mean "This current result I would like to get unique entries by EmailAddress"? You mean you want one email address per entry (add it to another max()) or do you mean if you have two different entries for the same email address, even across-users you want only one of them? – Evan Carroll Jan 9 at 1:06
0

Are you saying just all the unique email addresses? Have you tried incorporating DISTINCT in any way?

As for the others, seems like an WHERE statement would be the way to go.

  • DISTINCT will only trivially help if the all the returned columns are identical, rather than just the email address. – RDFozz Jan 8 at 18:51
0
SELECT b.id, b.date_created, a.lead_id, a.form_id,
   max(case when a.field_number = 13 then value end) as FirstName,
   max(case when a.field_number = 14 then value end) as LastName,
   max(case when a.field_number = 15 then value end) as Address,
   max(case when a.field_number = 16 then value end) as Address2 ,
   max(case when a.field_number = 17 then value end) as City,
   max(case when a.field_number = 18 then value end) as Province,
   max(case when a.field_number = 19 then value end) as PostalCode,
   max(case when a.field_number = 20 then value end) as Country,
   max(case when a.field_number = 4 then value end) as PhoneNumber,
   max(case when a.field_number = 3 then value end) as EmailAddress,
   max(case when a.field_number = 44 then value end) as Question,
   max(case when a.field_number LIKE 42.1 then value end) as ClientNewsletter,
   max(case when a.field_number LIKE 27.1 then value end) as LocalNewsletter
FROM wp_rg_lead_detail a
LEFT JOIN wp_rg_lead b
ON a.lead_id = b.id
WHERE a.form_id = 45
GROUP BY a.lead_id;

Rather than doing the whole

max(case when a.field_number = 3 then value end) as EmailAddress

I would suggest using group_concat and not losing duplicate values that many be useful

group_concat(case when a.field_number = 3 then value end) as EmailAddress

This will return all values comma-seperated as a string. In the case that you want only distinct values you can do

group_concat(DISTINCT case when a.field_number = 3 then value end) as EmailAddress

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.