1

I think a case statement would be the best approach to this but am open to any other suggestions. What I am trying to do is this. The query is generated based on a users input regarding the type report that they are seeking. The thing is all possible tables except for one contain a message_id column. On the odd table, I can get the data though another field which is time_sent. Here is an example of a generated query after user input.

  SELECT 
                ps.account_id,
                ba.account_name,
                NULL AS total_users,
                COUNT(DISTINCT(ps.uid)) AS unique_users,
                COUNT(DISTINCT r.msg_timestamp) AS messages_received,
                NULL AS total_clicks,
                NULL AS unique_clicks,
                COUNT(DISTINCT s.message_id) AS messages_sent, // THIS IS WHERE I NEED TO USE A CASE OR SOME OTHER MEANS TO DETERMINE IF THE message_id COLUMN EXISTS
                COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
            FROM conversation_facebook_page_subscribers ps
                LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
            WHERE 
                (ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
                AND
                (r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
            GROUP BY ps.account_id, ba.account_name

I have tried to use a case statement to check the name of the table and based on what the table name is select the appropriate field, but that didn't work. I have searched numerous questions on SO to see if I could find something that would work, but nothing I have found works. Can someone please point me in the right direction? Thank you in advance for any assistance.

11
  • 1
    You can't do that without dynamic SQL. All referenced identifiers are validated during query parsing, not during actual execution.
    – mustaccio
    Commented Jul 3, 2020 at 14:51
  • @trouble705 so when the column exists you want this COUNT(DISTINCT s.message_id) AS messages_sent. What would you want if the column does not exist? COUNT(DISTINCT s.time_sent) AS messages_sent ? Commented Jul 3, 2020 at 14:55
  • @mustaccio actually it is possible. Without dynamic SQL. Commented Jul 3, 2020 at 14:55
  • @ypercubeᵀᴹ, yes, that is correct. If s.message_id does not exist, I would then need s.time_sent.
    – trouble706
    Commented Jul 3, 2020 at 15:06
  • SO let me get this straight. Your query is not always exactly like above and in the LEFT JOIN conversation_facebook_page_sent s part you can replace the table with some other different table (and some of them have, some not, a message_id column). Correct? Commented Jul 3, 2020 at 15:31

1 Answer 1

0

You can use INFORMATION_SCHEMA COLUMNS Table

But you can't use it in a normal sql statement, because mysql will parse all parts.

I still have problems to understand, why you have missing columns in the first place.

CREATE PROCEDURE `new_procedure` (IN _account_id1,IN _account_id2)
BEGIN
        IF (EXISTS (SELECT 1 FROM information_schema.COLUMNS 
                    WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'conversation_facebook_page_sent ' 
                    AND COLUMN_NAME = 'message_id')) THEN
            SELECT 
                ps.account_id,
                ba.account_name,
                NULL AS total_users,
                COUNT(DISTINCT(ps.uid)) AS unique_users,
                COUNT(DISTINCT r.msg_timestamp) AS messages_received,
                NULL AS total_clicks,
                NULL AS unique_clicks,
           COUNT(DISTINCT s.message_id) AS messages_sent,
                COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
            FROM conversation_facebook_page_subscribers ps
                LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
            WHERE 
                (ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
                AND
                (r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
            GROUP BY ps.account_id, ba.account_name;
        ELSE
            SELECT 
                ps.account_id,
                ba.account_name,
                NULL AS total_users,
                COUNT(DISTINCT(ps.uid)) AS unique_users,
                COUNT(DISTINCT r.msg_timestamp) AS messages_received,
                NULL AS total_clicks,
                NULL AS unique_clicks,
            COUNT(DISTINCT s.send_time) AS messages_sent,
                COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
            FROM conversation_facebook_page_subscribers ps
                LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
            WHERE 
                (ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
                AND
                (r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
            GROUP BY ps.account_id, ba.account_name;
        END IF;
        
END
5
  • The first part of that does indeed work. But then when I add the else so that, if the message_id column doesn't exist, the query should then return the send_time, it says I have an error in my sql syntax. Maybe because all I am doing is " ELSE COUNT(DISTINCT s.send_time) AS messages_sent," ?
    – trouble706
    Commented Jul 3, 2020 at 15:12
  • there is no else in sql see changed answer
    – nbk
    Commented Jul 3, 2020 at 15:20
  • That does look like it is working. Thank you very much. Accepting your answer now.
    – trouble706
    Commented Jul 3, 2020 at 15:24
  • Actuall @nbk, that doesn't work. When I run the query with the email option, which is where the issue of not having the message_id field and instead needing the send_time, I get an error that message_id is an unknown column.
    – trouble706
    Commented Jul 3, 2020 at 16:30
  • No mysql will also parse the true part, i think you need actually make a stored procedure
    – nbk
    Commented Jul 3, 2020 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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