0

I'm trying to execute a MySQL query by putting the email adresses in 3 different tables (oc_customer, oc_journal2_newsletter and oc_d_newsletter_subscriber) together. When all emails are put into 1 column, I want to group it to avoid duplicates and sort the data ascending.

I have the following query below, but each time I receive the message about the invalid token ')' before the query is effectively executed. The message is pointing to the line having the code .... subscribed = '1')....

Can somebody help me out to avoid this warning?

SELECT `emailTOTAAL`.`emailTOT`
FROM (
SELECT 
  `oc_customer`.`email` AS `emailTOT`
FROM
  `oc_customer`
WHERE
  `oc_customer`.`language_id` = '2' AND 
  `oc_customer`.`newsletter` = '1'
UNION ALL
SELECT 
  `oc_journal2_newsletter`.`email` AS `emailTOT`
FROM
  `oc_journal2_newsletter`
UNION ALL
SELECT 
  `oc_d_newsletter_subscriber`.`email` AS `emailTOT`
FROM
  `oc_d_newsletter_subscriber`
WHERE
  `oc_d_newsletter_subscriber`.`language_id` = '2' AND 
  `oc_d_newsletter_subscriber`.`subscribed` = '1') 
AS `emailTOTAAL`
GROUP BY   
 `emailTOTAAL`.`emailTOT`
ORDER BY
 `emailTOTAAL`.`emailTOT` ASC

Thanks, Vicef

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
SabKo
  • 57
  • 10
  • what's the data type of subscribed? – RoMEoMusTDiE Jan 04 '18 at 20:38
  • Data-type of subscribed is int(1) – SabKo Jan 04 '18 at 20:39
  • Since this is `MySQL` syntax I removed `SQL-Server tag`. You can revert if you have the reason to have both RDBMs tagged. – PM 77-1 Jan 04 '18 at 20:39
  • As an aside, note that the 1 in int(1) is almost meaningless – Strawberry Jan 04 '18 at 20:42
  • What happens when you just execute the unions, not as a subselect? Why not just UNION, and not UNION ALL, to remove duplicates? – SQLCliff Jan 04 '18 at 20:48
  • if subscribed is supposed to be a Yes or No then don't use INT or TINYINT rather use BIT(1) https://stackoverflow.com/questions/290223/what-is-the-difference-between-bit-and-tinyint-in-mysql – RoMEoMusTDiE Jan 04 '18 at 20:51
  • I don't think "Invalid token" is a MySQL error message. Is this a popup in your IDE? – Barmar Jan 04 '18 at 20:58
  • Hi SQLCliff, if simply UNION is used without subselect, the query is executed without error message but the data are not sorted... – SabKo Jan 04 '18 at 21:02
  • Hi Barmar, the error message is generated by the application Maestro anySQL which is a tool to simplify the creation of mysql queries (for dummies like me). I will try the command in phpmyadmin as well. – SabKo Jan 04 '18 at 21:05
  • Hi Barmar, very strange... my code is running well on phpmyadmin, so I suppose it is a bug in Maestro AnySQL So problem solved. The most important is that it runs smoothly on the database. – SabKo Jan 04 '18 at 21:11

1 Answers1

1

If you change UNION ALL to UNION, it should remove duplicates for you. Then you can get rid of the outer SELECT and the GROUP BY. Your query would then look like this:

SELECT 
    `email` AS `emailTOT`
FROM
    `oc_customer`
WHERE
    `language_id` = '2' AND 
    `newsletter` = '1'

UNION

SELECT 
    `email` AS `emailTOT`
FROM
    `oc_journal2_newsletter`

UNION

SELECT 
    `email` AS `emailTOT`
FROM
    `oc_d_newsletter_subscriber`
WHERE
    `language_id` = '2' AND 
    `subscribed` = '1'
ORDER BY `emailTOT` ASC
digital.aaron
  • 5,435
  • 2
  • 24
  • 43
  • Hi Digital_Aaron, thank you for this simplified version. It is running smoothly on both phpmyadmin and Maestro Anysql and is having no duplicates indeed. It solved my problem! Thanks – SabKo Jan 04 '18 at 21:24