Dynamic query string

Question

I want to add some dynamic content in from clause based on one particular column value. is it possible? For Example,

 SELECT BILL.BILL_NO AS BILLNO,
        IF(BILL.PATIENT_ID IS NULL,"CUS.CUSTOMERNAME AS NAME","PAT.PATIENTNAME AS NAME")
 FROM
        BILL_PATIENT_BILL AS BILL 
        LEFT JOIN IF(BILL.PATIENT_ID IS NULL," RT_TICKET_CUSTOMER AS CUS ON BILL.CUSTOMER_ID=CUS.ID"," RT_TICKET_PATIENT AS PAT ON BILL.PATIENT_ID=PAT.ID")

But This query is not working.

Here

BILL_PATIENT_BILL table is a common table.
It can have either PATIENT_ID or CUSTOMER_ID. If a particular record has PATIENT_ID i want PATIENTNAME in RT_TICKET_PATIENT as NAME OtherWise it will hold CUSTOMER_ID. If it is i want CUSTOMERNAME as NAME.

Here I m sure That BILL_PATIENT_BILL must have either PATIENT_ID or CUSTOMER_ID. Can anyone help me?

score 1 · Accepted Answer · edited May 23 '17 at 12:20

You can also use IF() to select the right values instead of constructing your query from strings:

 SELECT 
        BILL.BILL_NO AS BILLNO,
        IF( BILL.PATIENT_ID IS NULL, cus.CUSTOMERNAME, pat.PATIENTNAME ) AS NAME
 FROM
        BILL_PATIENT_BILL AS BILL 
 LEFT JOIN RT_TICKET_CUSTOMER cus ON BILL.CUSTOMER_ID = cus.ID
 LEFT JOIN RT_TICKET_PATIENT pat ON BILL.PATIENT_ID = pat.ID

However, it would also be possible to PREPARE a statement from strings and EXECUTE it but this technique is prone to SQL injections, i can only disadvise to do so:

read here: Is it possible to execute a string in MySQL?

Dynamic query string

1 Answers1