Taking one column from MySQL joined tables

Question

I have a query in MySQL and I am making a crystal report by using this.

Now inside the query i have a column called scan_mode and it is coming from gfi_transaction table. This scan_mode I am using in report to suppress some sections. But some times this value is coming null for some transaction ids.

So now I want to take this scan_mode as separate query so that it will work. Can any one please help how I can modify the below query to take only scan_mode column.

SELECT 
    cc.cost_center_code AS cccde,
    cc.name AS ccnme,gf.scan_mode,
    cc.cost_center_id AS ccid,
    site.name AS siteme,
    crncy.currency_locale AS currency_locale,
    cntry.language AS LANGUAGE,
    cntry.country_name AS cntrynm,
    crncy.decimal_digits AS rnd,
    gf.transaction_no AS Serial_No,
    brnd.name AS brand_name,
    rsn.description AS reason,
    gf.comment AS COMMENT,
    ts.status_description AS STATUS,
    DATE_FORMAT(gf.created_date,'%d/%m/%Y') AS created_date,
    gf.created_by AS created_by,
    IFNULL(gf.approval_no,'Not authorized') AS Trans_no,
    gf.approved_date AS approval_dt,
    gf.approved_by AS approved_by,gf.status AS status1,
    IFNULL(loc.cost_center_code,cc.cost_center_code) AS cur_location,
    gf.document_ref_no,gf.document_ref_type,
    ,DATE_FORMAT(document_ref_date1,'%d/%m/%Y')) AS invoice_no
        FROM 
    gfi_transaction gf
    INNER JOIN gfi_instruction gfn ON (gf.transaction_id=gfn.transaction_id)
    INNER JOIN gfi_document_instruction doc ON (gf.ref_transaction_no =      doc.document_instruction_id)
    INNER JOIN reason rsn ON (gf.reason_id = rsn.reason_id)
    INNER JOIN gfi_status ts ON (gf.status = ts.gfi_status_id)
    INNER JOIN transaction_type tt ON (gf.transaction_type_id = tt.transaction_type_id)
    INNER JOIN brand brnd ON(gf.brand_id=brnd.brand_id)
    -- cc details
    INNER JOIN cost_center cc ON (brnd.parent_brand  = cc.brand_id OR gf.brand_id = cc.brand_id)
    INNER JOIN site site ON(cc.site_id = site.site_id)
    INNER JOIN country cntry ON (site.country_id = cntry.country_id)
    INNER JOIN currency crncy ON (cntry.currency_id=crncy.currency_id)
    LEFT OUTER JOIN alshaya_location_details loc ON 
        (gf.brand_id = loc.brand_id AND loc.cost_center_id =   gf.cost_centre_id)
    LEFT OUTER JOIN alshaya_location_details locto ON 
        (locto.cost_center_id = gf.from_cost_center_id)         
WHERE 
    gf.transaction_id='{?TransID}'
    AND rsn.transaction_type_id IN (10,11,14)

score 0 · Answer 1 · edited May 23 '17 at 12:21

wow, that's a big query. I ran across a similar problem in a query i was building and found the if syntax to be a solution to my problem. This was also answered in this question: MYSQL SELECT WITHIN IF Statement

$psdb->query = "SELECT count, s.classid,
    if (k.sic != k.siccode, k.siccode, s.siccode) as siccode,
    if (k.sic != k.siccode, k.sicdesc, s.sicdesc) as sicdesc,
    if (k.sic != k.siccode, k.sicslug, s.sicslug) as sicslug
  FROM ...

score 0 · Answer 2 · answered Aug 27 '13 at 06:07

It looks like scan_mode column comes from "gfi_transaction" table which seems to be primary table in your query. If you get null for this column then it means your table itself have NULL value for this column. Taking that separately in a query wont solve your problem. Try replacing null with a default value and handle it in code. You can add default value instead of NULL by using ifnull(scan_mode, 'default')

Taking one column from MySQL joined tables

2 Answers2