Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58

    Question Unanswered: Merging 2 querys

    Hi, I'm new to this forum. I don't have a big pile of expirience with php and MySQL. For that reason I'm using the PHP Generator for MySQL from SQLMaestro wich generates ready to use php files.
    It works for me because I can sart with a working file with all the options and I can change them the way I like.

    My first questions is, I have 2 different sql querys but I want to make ons of thos. I have tried different setting but I can't get it to work, can anyone help me
    Code:
      $sql = "SELECT * FROM (SELECT 
    t1.`offerte`, 
    t1.`klant_id`, 
    lp1.`klantnaam` 
    AS 
    `lp_klant_id`, 
    t1.`ref_klant`, 
    t1.`datum_dossier`, 
    t1.`datum_offerte`, 
    t1.`bedrag`, 
    t1.`plasma`, 
    t1.`water`, 
    t1.`laser`, 
    t1.`knip_zet`, 
    t1.`handel`, 
    t1.`dikte`,
    t1.`materiaal`, 
    t1.`omschrijving`, 
    t1.`status_id`, 
    lp14.`status` 
    AS 
    `lp_status_id` 
    FROM 
    `offertenummers` 
    AS 
    t1 LEFT OUTER JOIN 
    `adressen` 
    AS
    lp1 ON (t1.`klant_id` = lp1.`id`) 
    LEFT OUTER JOIN 
    `status` 
    AS
    lp14 ON (t1.`status_id` = lp14.`id`)
    ) subq";
    Code:
      $sql = "SELECT * FROM (SELECT 
    t1.`ordernummer`, 
    t1.`offerte_id`, 
    lp1.`offerte` 
    AS 
    `lp_offerte_id`, 
    t1.`datum_order`, 
    t1.`datum_leveren` 
    FROM 
    `ordernummers` 
    AS 
    t1 LEFT OUTER JOIN 
    `offertenummers` 
    AS 
    lp1 ON (t1.`offerte_id` = lp1.`offerte`)
    ) subq";

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you cannot easily combine those queries

    please explain why you want to combine them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    It's a quotation system, combined with orderering and blilling.
    My current status is that the quotation, ordering and billing are all on seperate pages but I need them in one list.

    My table structure is:

    Quotation information:
    Code:
    `offertenummers` (
      `offerte` int(8) NOT NULL auto_increment,
      `klant_id` int(6) NOT NULL,
      `ref_klant` varchar(25) default NULL,
      `datum_dossier` date NOT NULL,
      `datum_offerte` date default NULL,
      `bedrag` varchar(15) default NULL,
      `plasma` varchar(5) default NULL,
      `water` varchar(5) default NULL,
      `laser` varchar(5) default NULL,
      `knip_zet` varchar(5) default NULL,
      `handel` varchar(5) default NULL,
      `dikte` varchar(25) default NULL,
      `materiaal` varchar(25) default NULL,
      `omschrijving` varchar(255) default NULL,
      `status_id` int(2) NOT NULL,
      PRIMARY KEY  (`offerte`)
    `klant_id`being the ID for an customer
    `status_id` being the ID for the status of this quotation

    Order information
    Code:
    `ordernummers` (
      `ordernummer` int(8) NOT NULL auto_increment,
      `offerte_id` int(8) NOT NULL,
      `datum_order` date NOT NULL,
      `datum_leveren` date NOT NULL,
      PRIMARY KEY  (`ordernummer`)
    `offerte_id` being the ID point to the table quotation `offerte`

    Billing information
    Code:
    `factuurnummers` (
      `factuurnummer` int(8) NOT NULL auto_increment,
      `offerte_id` int(8) NOT NULL,
      `type` varchar(25) NOT NULL,
      `datum_verzonden` date NOT NULL,
      `bedrag_excl` varchar(15) NOT NULL,
      `bedrag_incl` varchar(15) NOT NULL,
      `datum_betaald` date default NULL,
      PRIMARY KEY  (`factuurnummer`)
    `offerte_id` being the ID point to the table quotation `offerte`

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, one way would be to UNION the queries

    1. stick the words UNION ALL between the two queries

    2. make sure that the queries return the same number of columns (pad the shorter one out with NULLs as necessary)

    2a. columns have to have the same datatype class
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    Quote Originally Posted by r937
    well, one way would be to UNION the queries

    1. stick the words UNION ALL between the two queries

    2. make sure that the queries return the same number of columns (pad the shorter one out with NULLs as necessary)

    2a. columns have to have the same datatype class
    1. never heard of this one, what would my query look then?

    2. what do you mean exactly?

    2a. they are

    Thanks for helping me out here

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    UNION effectively "glues" two resultsets together. It combines them in to a single resultset, on the condition that both queries return the same number of columns, and the columns in each set are of the same data type (i.e. if columnn x in query 1 is an integer, so must column x in query 2).

    Give it a try
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    I have tried it and I get an error 'Every derived table must have its own alias'
    What am I doing wrong?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Muiter
    What am I doing wrong?
    you have forgotten to give a table alias to a subquery

    i can't see your query from here, or i'd tell you exactly where
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    First original code from PHP Generator for MySQL
    Code:
      $sql = "SELECT * FROM (SELECT t1.`offerte`, t1.`klant_id`, lp1.`klantnaam` AS `lp_klant_id`, t1.`ref_klant`, t1.`datum_dossier`, t1.`datum_offerte`, t1.`bedrag`, t1.`plasma`, t1.`water`, t1.`laser`, t1.`knip_zet`, t1.`handel`, t1.`dikte`, t1.`materiaal`, t1.`omschrijving`, t1.`status_id`, lp14.`status` AS `lp_status_id` FROM `offertenummers` AS t1 LEFT OUTER JOIN `adressen` AS lp1 ON (t1.`klant_id` = lp1.`id`) LEFT OUTER JOIN `status` AS lp14 ON (t1.`status_id` = lp14.`id`)) subq";
    Second original code from PHP Generator for MySQL
    Code:
      $sql = "SELECT * FROM (SELECT t1.`ordernummer`, t1.`offerte_id`, lp1.`offerte` AS `lp_offerte_id`, t1.`datum_order`, t1.`datum_leveren` FROM `ordernummers` AS t1 LEFT OUTER JOIN `offertenummers` AS lp1 ON (t1.`offerte_id` = lp1.`offerte`)) subq";
    My try with UNION
    Code:
      $sql = "SELECT * FROM (SELECT t1.`offerte`, t1.`klant_id`, lp1.`klantnaam` AS `lp_klant_id`, t1.`ref_klant`, t1.`datum_dossier`, t1.`datum_offerte`, t1.`bedrag`, t1.`plasma`, t1.`water`, t1.`laser`, t1.`knip_zet`, t1.`handel`, t1.`dikte`, t1.`materiaal`, t1.`omschrijving`, t1.`status_id`, lp14.`status` AS `lp_status_id` FROM `offertenummers` AS t1 LEFT OUTER JOIN `adressen` AS lp1 ON (t1.`klant_id` = lp1.`id`) LEFT OUTER JOIN `status` AS lp14 ON (t1.`status_id` = lp14.`id`)) UNION SELECT * FROM (SELECT t1.`ordernummer`, t1.`offerte_id`, lp1.`offerte` AS `lp_offerte_id`, t1.`datum_order`, t1.`datum_leveren` FROM `ordernummers` AS t1 LEFT OUTER JOIN `offertenummers` AS lp1 ON (t1.`offerte_id` = lp1.`offerte`)) subq";
    Last edited by Muiter; 12-30-08 at 15:30.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    may i ask you a question before we get into your query?

    why are you only interested in the COUNT(*)?

    and by the way, your UNION query seems to be missing the UNION operator

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    I'm sorry, my mistake. I changed my previous post.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let's begin

    you have two SELECTs in the UNION

    the first returns 17 columns, the second returns only 5 columns

    so right away you have an error, because they're supposed to return the same number of columns

    you will have to pad the second one out with NULLs in the 6th through 17th column positions

    secondly, have you made sure that the column types match?
    Code:
    SELECT t1.`offerte`       <=> SELECT t1.`ordernummer`
         , t1.`klant_id`      <=>      , t1.`offerte_id`
         , lp1.`klantnaam`    <=>      , lp1.`offerte`
         , t1.`ref_klant`     <=>      , t1.`datum_order`
         , t1.`datum_dossier` <=>      , t1.`datum_leveren`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    I think we are going the wrong way with unioun. Bu I'm a newbie so I might be wrong.
    The colums are not the same type, and the data is not corresponding.

    My result for ech query are:

    quotation:
    Offertenummer
    Klant
    Referentie van klant
    Datum dossier
    Datum offerte
    Bedrag
    Plasma
    Water
    Laser
    Knip/zetwerk
    Handel
    Dikte
    Materiaal
    Omschrijving
    Status

    Order:
    Ordernummer
    Offertenummer
    Orderdatum
    Leverdatum

    Billing
    Factuurnummer
    Offertenummer
    Type
    Datum verzonden
    Bedrag excl. BTW
    Bedrag incl. BTW
    Datum betaald

    I want a total of:
    Offertenummer
    Klant
    Referentie van klant
    Datum dossier
    Datum offerte
    Bedrag
    Plasma
    Water
    Laser
    Knip/zetwerk
    Handel
    Dikte
    Materiaal
    Omschrijving
    Ordernummer
    Orderdatum
    Leverdatum
    Factuurnummer
    Type
    Datum verzonden
    Bedrag excl. BTW
    Bedrag incl. BTW
    Datum betaald
    Status

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aha, so you want to join horizontally!
    Well, this query joins the tables together based on matching Offertenummer and only displays results where there is a matching Offertnummer in ann 3 tables.
    Code:
    SELECT `quotation`.`Offertenummer`
         , `quotation`.`Klant`
         , `quotation`.`Referentie van klant`
         , `quotation`.`Datum dossier`
         , `quotation`.`Datum offerte`
         , `quotation`.`Bedrag`
         , `quotation`.`Plasma`
         , `quotation`.`Water`
         , `quotation`.`Laser`
         , `quotation`.`Knip/zetwerk`
         , `quotation`.`Handel`
         , `quotation`.`Dikte`
         , `quotation`.`Materiaal`
         , `quotation`.`Omschrijving`
         , `order`.`Ordernummer`
         , `order`.`Orderdatum`
         , `order`.`Leverdatum`
         , `billing`.`Factuurnummer`
         , `billing`.`Type`
         , `billing`.`Datum verzonden`
         , `billing`.`Bedrag excl. BTW`
         , `billing`.`Bedrag incl. BTW`
         , `billing`.`Datum betaald`
         , `quotation`.`Status`
    FROM   `quotation`
     INNER
      JOIN `order`
        ON `quotation`.`Offertenummer` = `order.`Offertenummer`
     INNER
      JOIN `billing`
        ON `quotation`.`Offertenummer` = `billing`.`Offertenummer`
    George
    Home | Blog

  15. #15
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    This looks good georgev

    How does this behave if:
    - an quotation has no order?
    - an quotation has no billing?
    - an quotation has no more then one billing?

Posting Permissions

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