locked
Display grid on basis of Enquiry No RRS feed

  • Question

  • User-1506965535 posted

    The below query displays me the result which I want to show in the grid

    SELECT bb.project_id, bb.building_id, bb.flat_id AS flat_id,
       cc.cust_enq_no AS enquiry_no, cc.NAME enquiry_name,
       TO_CHAR (cc.f_followup_date, 'dd/MM/yyyy') AS last_followup_date,
       cc.f_remarks AS last_followup_comments  FROM xxacl_pn_flat_det_v bb INNER JOIN xxcus.xxacl_pn_customer_enquiry_v cc
       ON bb.project_id = cc.mkey

    but there is some change in my requirement.

    Now for each F_FLAT_ID there is multiple CUST_ENQ_NO say

    Select * from xxcus.xxacl_pn_customer_enquiry_v where F_FLAT_ID = '18158'

    it has 3 records.

    So with my first query,

    How to get multiple CUST_ENQ_NO

     

    Saturday, January 16, 2016 8:17 AM

Answers

  • User475983607 posted

    The below query displays me the result which I want to show in the grid

    SELECT bb.project_id, bb.building_id, bb.flat_id AS flat_id,
    cc.cust_enq_no AS enquiry_no, cc.NAME enquiry_name,
    TO_CHAR (cc.f_followup_date, 'dd/MM/yyyy') AS last_followup_date,
    cc.f_remarks AS last_followup_comments FROM xxacl_pn_flat_det_v bb INNER JOIN xxcus.xxacl_pn_customer_enquiry_v cc
    ON bb.project_id = cc.mkey
    but there is some change in my requirement.

    Now for each F_FLAT_ID there is multiple CUST_ENQ_NO say

    Select * from xxcus.xxacl_pn_customer_enquiry_v where F_FLAT_ID = '18158'
    it has 3 records.

    So with my first query,

    How to get multiple CUST_ENQ_NO

    It's tough to answer this question. The script is returning data where mkey from xxcus.xxacl_pn_customer_enquiry_v matches project_id from xxacl_pn_flat_det_v.

    If this is a one-to-many relationship between the tables through ON bb.project_id = cc.mkey, meaning that the value of bb.project_id shows up many times in the mkey field of xxacl_pn_flat_det_v, the result set will have many records with the same cust_enq_no.

    If you wish to filter the resulting set by F_FLAT_ID = '18158' then simply add a WHERE clause.

    WHERE cc.F_FLAT_ID = '18158'

    However, it looks like F_FLAT_ID exists in xxacl_pn_flat_det_v too. So, I'm not sure what to make of this.  Maybe the _v naming convention means view. If so, that complicates matters as we don't have the views.  I have the feeling that you're asking a different question than what the code infers.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 16, 2016 7:07 PM

All replies

  • User475983607 posted

    The below query displays me the result which I want to show in the grid

    SELECT bb.project_id, bb.building_id, bb.flat_id AS flat_id,
    cc.cust_enq_no AS enquiry_no, cc.NAME enquiry_name,
    TO_CHAR (cc.f_followup_date, 'dd/MM/yyyy') AS last_followup_date,
    cc.f_remarks AS last_followup_comments FROM xxacl_pn_flat_det_v bb INNER JOIN xxcus.xxacl_pn_customer_enquiry_v cc
    ON bb.project_id = cc.mkey
    but there is some change in my requirement.

    Now for each F_FLAT_ID there is multiple CUST_ENQ_NO say

    Select * from xxcus.xxacl_pn_customer_enquiry_v where F_FLAT_ID = '18158'
    it has 3 records.

    So with my first query,

    How to get multiple CUST_ENQ_NO

    It's tough to answer this question. The script is returning data where mkey from xxcus.xxacl_pn_customer_enquiry_v matches project_id from xxacl_pn_flat_det_v.

    If this is a one-to-many relationship between the tables through ON bb.project_id = cc.mkey, meaning that the value of bb.project_id shows up many times in the mkey field of xxacl_pn_flat_det_v, the result set will have many records with the same cust_enq_no.

    If you wish to filter the resulting set by F_FLAT_ID = '18158' then simply add a WHERE clause.

    WHERE cc.F_FLAT_ID = '18158'

    However, it looks like F_FLAT_ID exists in xxacl_pn_flat_det_v too. So, I'm not sure what to make of this.  Maybe the _v naming convention means view. If so, that complicates matters as we don't have the views.  I have the feeling that you're asking a different question than what the code infers.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 16, 2016 7:07 PM
  • User-1716253493 posted

    There are INNER JOIN and OUTER JOIN in JOINS

    https://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx

     

    Monday, January 18, 2016 1:31 AM