#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0

    How to combine (UNION?) 3 columns, while using a WHERE clause on a different column


    Hi there,

    I'm trying to join 3 email columns from the same table into 1 big email column, of which I can do with this SQL:

    Code:
    SELECT email
    FROM (
    SELECT email
    FROM accounts
    UNION 
    SELECT email2
    FROM accounts
    UNION 
    SELECT email3
    FROM accounts
    )accounts  WHERE email LIKE '%@%'
    But I also want to be able to only return rows where a different column from the same table (accountgroup) is = to a particular value. For example, what I thought would work:

    Code:
    SELECT email, accountgroup
    FROM (
    
    SELECT email, accountgroup
    FROM accounts
    UNION 
    SELECT email2, accountgroup
    FROM accounts
    UNION 
    SELECT email3, accountgroup
    FROM accounts
    )accounts
    WHERE email LIKE  '%@%'
    AND accountgroup = 'Vyrav'
    This second statement does not work as intended though, and only returns 1 row. I think the UNION is merging the rows where the accountgroup column values are the same (which will be all rows).

    If I modify the first statement to make the last line like this:

    Code:
    )accounts  WHERE email LIKE '%@%' AND accountgroup='Vyrav'
    It says theres an 'Unkown column 'accountgroup' in 'where clause'

    A kind chap at stack overflow suggested the following statement, but this also only returns one row:

    Code:
    SELECT
          email,
          accountgroup
    FROM
          (SELECT
                email AS email,
                accountgroup
           FROM
                accounts
           UNION ALL
           SELECT
                email2 AS email,
                accountgroup
           FROM
                accounts
           UNION ALL
           SELECT
                email3 AS email,
                accountgroup
           FROM
                accounts) account
    WHERE
          email LIKE '%@%'
          AND accountgroup = 'Vyrav'
    Can anybody advise what I'm doing wrong here, or maybe what I should be doing instead?

    Thanks in advance, any help is much appreciated.

    (I'm using MySQL version 5.5.30-cll)
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    when you find yourself fumbling with lots of unions and weird queries only to get some trivial data, that's a warning sign. It means there's an issue with your data model.

    Read up on the First normal form, in particular the issue of repeating groups. Your "email1", "email2", ..., "email592" columns are such a group. Repair your model by storing all the email addresses as rows, not columns. This will require an additional table.

    When you're done, the query will be trivial.

    Apart from that, I wonder what the '%@%' is supposed do do. Shouldn't every email address contain an "@" character?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0
    Thanks for the response, I was actually able to get it working with:

    Code:
    SELECT
    	  email,
    	  accountgroup, othercode
    FROM
    	  (SELECT
    			email AS email,
    			accountgroup, othercode
    	   FROM
    			accounts
    	   UNION ALL
    	   SELECT
    			email2 AS email,
    			accountgroup, othercode
    	   FROM
    			accounts
    	   UNION ALL
    	   SELECT
    			email3 AS email,
    			accountgroup, othercode
    	   FROM
    			accounts) account
    WHERE
    	  email LIKE '%@%'
    	  AND accountgroup = 'LEADS' AND othercode = 'Dealer'

    Apart from that, I wonder what the '%@%' is supposed do do. Shouldn't every email address contain an "@" character?
    I think I'm right in saying, LIKE '@' would only return email addresses, that are just @ on its own (so not actually an email address) '%@%' returns all values that have @ somewhere in the value, the % before @ ignores whatever characters come before the @, and the % after does the same - ignoring any characters that come after the @. So as long as there is an @ in the string, it will be returned regardless of the other characters

  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by Jonny125
    Thanks for the response, I was actually able to get it working with:
    Um, did you read what I explained about the underlying problem?

    Your query isn't "working". At best, you've done a quick hack to buy you some time until you run into the same problem again. If fetching a bunch of friggin' email addresses already takes you 24 lines of SQL with a subquery and two unions, then good luck trying to write more complex queries.

    I mean, you can do with your data whatever you want. But if I were you, I'd fix the problem rather than spending the rest of the lifetime of the application with hacks and workarounds.



    Originally Posted by Jonny125
    So as long as there is an @ in the string, it will be returned regardless of the other characters
    I repeat: Shouldn't this apply to all strings you have in the email fields? Or is your database filled with all kind of garbage data?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0
    Read up on the First normal form, in particular the issue of repeating groups. Your "email1", "email2", ..., "email592" columns are such a group. Repair your model by storing all the email addresses as rows, not columns. This will require an additional table.
    I have, and I appreciate your input. I did not create our company database, it has 3 email columns, 2 of which are barely used, but they are used - please don't ask me why. The database is part of a very complex support system of which I had no input in its development (there may be a reason for it, but I don't know.) I'm just trying to make a nice and simple report so my colleague can get an entire list of unique email addresses that she can just copy + paste into Thunderbird.

    I'm new to SQL so im glad to have gotten this 'working' even it is only a hack in your opinion. If you had made a suggestion to what I should have done differently / properly. I would greatly appreciate it.

    Quote:
    Originally Posted by Jonny125
    So as long as there is an @ in the string, it will be returned regardless of the other characters


    I repeat: Shouldn't this apply to all strings you have in the email fields? Or is your database filled with all kind of garbage data?
    All be it very few, when this system was created some email values were set to things like 'Unknown' or 'to be confirmed'. So the like '%@%' is just intended to filter out these values.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Well, if your company definitely isn't interested in fixing their data, then there's probably not much you can do. Let the application rot and try to avoid the query writing jobs in the future.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo