Merging 2 querys

Muiter · #1 (**permalink**) 12-29-08, 11:17

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";

r937 · #2 (**permalink**) 12-29-08, 13:53

you cannot easily combine those queries

please explain why you want to combine them

Muiter · #3 (**permalink**) 12-29-08, 14:14

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`

r937 · #4 (**permalink**) 12-29-08, 15:00

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

Muiter · #5 (**permalink**) 12-30-08, 10:27

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

gvee · #6 (**permalink**) 12-30-08, 10:33

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

Muiter · #7 (**permalink**) 12-30-08, 13:40

I have tried it and I get an error 'Every derived table must have its own alias'
What am I doing wrong?

r937 · #8 (**permalink**) 12-30-08, 14:15

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

Muiter · #9 (**permalink**) 12-30-08, 14:23

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";

r937 · #10 (**permalink**) 12-30-08, 14:24

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

Muiter · #11 (**permalink**) 12-30-08, 14:31

I'm sorry, my mistake. I changed my previous post.

r937 · #12 (**permalink**) 12-30-08, 14:43

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`

Muiter · #13 (**permalink**) 12-31-08, 06:15

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

gvee · #14 (**permalink**) 12-31-08, 07:15

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`