Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    Unanswered: The results to retrive the result without cartesian join

    The data is as follows


    Table name : Forms
    ****************

    form id form name

    1 Invoice

    2 Purchase order

    3 Credit memo




    Table name : Forms_parameters
    ****************

    form id parameter name

    1 Cust no

    1 Invoice_no

    2 Cust loc



    Table name : parameters
    ****************

    parameter id parameter name

    1 Cust no

    2 Invoice_no

    3 Cust loc

    4 WO



    The sql query is required to display the parameters unassigned to the form the result set required is as follows


    form id parameter id parameter name

    1 3 Cust loc
    1 4 WO


    2 1 Cust no
    2 2 Invoice_no
    2 4 WO


    3 1 Cust no
    3 2 Invoice_no
    3 3 Cust loc
    3 4 WO


    I am currently using 'CROSS JOINS' to derive the results which servers the purpose but can you please suggest of any optimized method . The query that I am presently using is as follows:


    SELECT forms.form_id, parameters.parameter_id, parameters.parameter_name, parameters.label, parameters.parameter_data_type, parameters.control_type,
    parameters.value_length, parameters.mandatory, parameters.default_value, parameters.select_statement, parameters.help_text, 0 parameter_seq
    FROM forms
    CROSS JOIN parameters
    WHERE forms.form_id||parameters.parameter_id NOT IN (SELECT form_id||parameter_id FROM form_parameters)
    ORDER BY forms.form_id, parameters.parameter_name;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I would avoid the concatenation by using NOT EXISTS:
    Code:
    SELECT forms.form_id, parameters.parameter_id, parameters.parameter_name, parameters.label, parameters.parameter_data_type, parameters.control_type,
    parameters.value_length, parameters.mandatory, parameters.default_value, parameters.select_statement, parameters.help_text, 0 parameter_seq
    FROM forms
    CROSS JOIN parameters
    WHERE NOT EXISTS(SELECT NULL FROM form_parameters fp
    WHERE fp.form_id = forms.forms_id
    AND fp.parameter_id = parameters.parameter_id)
    ORDER BY forms.form_id, parameters.parameter_name;
    Alternatively, I might do this:
    Code:
    SELECT forms.form_id, parameters.parameter_id
    FROM forms
    CROSS JOIN parameters
    MINUS
    SELECT form_id, parameter_id FROM form_parameters
    ... but then you have more work to do to get the form and parameter attributes.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what about...
    Code:
    SELECT ...
      FROM forms
    CROSS 
      JOIN parameters
    LEFT OUTER
      JOIN form_parameters fp
        ON fp.form_id = forms.forms_id
       AND fp.parameter_id = parameters.parameter_id
     WHERE fp.form_id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Very nice!

  5. #5
    Join Date
    Sep 2007
    Posts
    2

    To obtain the above results WITHOUT using CROSS JOINS

    Thanks a lot for helping me optimize the query , but if anyone could please help me
    to obtain the above results WITHOUT using CROSS JOINS it will be great.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why do you not want to use cross joins?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Do you just want a list of parameters?

    Code:
    SELECT p.*
    FROM parameters p
    WHERE NOT EXISTS
    (SELECT NULL FROM form_parameters fp WHERE fp.parameter_id = p.parameter_id)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •