My VIEW in MySQL its not working.

Question

Hello all,

i play whit CREATE VIEW's in MySQL 5, now i have a troble, how can i make sure when i use this code.

SELECT
    *

FROM
    view_shop_invoicer_list

WHERE
    accept_date >= '2009-10-16 00:00:00' AND
    accept_date <= '2009-10-31 23:59:59' AND
    shopid = [SHOPID];

my VIEW look like this, down here, and it will not take my accept_date into this view, is i copy/paste it from my SELECTE and insert int into my VIEW its will work, but not the order way :(

DROP VIEW IF EXISTS view_shop_invoicer_list;
CREATE VIEW view_shop_invoicer_list AS

SELECT
    SUM( it.transamount ) AS beloeb,
    SUM( it.cargo_fee ) AS cargo,
    SUM( 
     ( 
      it.transamount - 
      ( 
       ( it.transamount - it.cargo_fee ) / 100 * 
       ( ms.pay_adm_fee + ms.pay_marks_fee + ms.pay_kick_fee ) - 
       ( it.adm_fee + it.marketing_fee + it.kickback_fee )
      ) 
     ) 
    ) AS shop_payout,
    SUM( fc_return_fee( it.transamount , it.cargo_fee , ms.pay_adm_fee , it.adm_fee ) ) AS shop_adm_fee,
    SUM( fc_return_fee( it.transamount , it.cargo_fee , ms.pay_marks_fee , it.marketing_fee ) ) AS shop_marks_fee,
    SUM( fc_return_fee( it.transamount , it.cargo_fee , ms.pay_kick_fee , it.kickback_fee ) ) AS shop_kick_fee,
    it.shopid AS shopid,
    it.accept_date AS accept_date

FROM
    invoice_trans it INNER JOIN invoice i ON it.orderid = i.ordreid
    INNER JOIN shops ms ON ms.id = it.shopid

WHERE
    i.status != 0

i hobe sombardy know why its not working :/

Table 1: invoice_trans

CREATE TABLE `invoice_trans` (
  `id` int(11) NOT NULL auto_increment,
  `orderid` varchar(32) NOT NULL default '0',
  `transamount` int(11) NOT NULL default '0',
  `transact` varchar(32) NOT NULL default '',
  `transnr` int(11) NOT NULL default '0',
  `shopid` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '0',
  `accept_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `md5_key_declie` varchar(32) NOT NULL,
  `declie_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `pay_points` int(1) NOT NULL default '0',
  `cargo_fee` int(11) NOT NULL default '4900',
  `first_time_discount` int(2) NOT NULL default '0',
  `adm_fee` int(11) NOT NULL default '0',
  `marketing_fee` int(11) NOT NULL default '0',
  `kickback_fee` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Table 2: invoice

CREATE TABLE `invoice` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL default '0',
  `per_fullname` varchar(64) NOT NULL,
  `per_street` varchar(64) NOT NULL,
  `per_zipcode` int(4) NOT NULL,
  `per_city` varchar(64) NOT NULL,
  `per_email` varchar(256) NOT NULL default '',
  `fak_fullname` varchar(64) NOT NULL default '',
  `fak_street` varchar(64) NOT NULL,
  `fak_zipcode` int(4) NOT NULL,
  `fak_city` varchar(64) NOT NULL,
  `fak_email` varchar(256) NOT NULL default '',
  `push_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` int(1) NOT NULL default '0',
  `splits` int(11) NOT NULL default '0',
  `dibs_ordreid` varchar(32) NOT NULL default '0',
  `reftag` varchar(64) NOT NULL,
  `vonchers_prices` int(11) NOT NULL default '0',
  `ordre_complate` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

There's no ordreid (or orderid) column in invoice to join against. The script is still very much incomplete by the way, no function fc_return_fee or test data, so testability is very limited.

What is the error message mysql is giving you?

Answer 2

i found the problem.... i fix the problem this way.

DROP VIEW IF EXISTS view_shop_invoicer_list;
CREATE VIEW view_shop_invoicer_list AS

SELECT
    it.transamount AS beloeb,
    SUM( it.cargo_fee ) AS cargo,
    ( 
      it.transamount - 
      ( 
       ( it.transamount - it.cargo_fee ) / 100 * 
       ( ms.pay_adm_fee + ms.pay_marks_fee + ms.pay_kick_fee ) - 
       ( it.adm_fee + it.marketing_fee + it.kickback_fee )
      ) 
    ) AS shop_payout,
    fc_return_fee( it.transamount , it.cargo_fee , ms.pay_adm_fee , it.adm_fee ) AS shop_adm_fee,
    fc_return_fee( it.transamount , it.cargo_fee , ms.pay_marks_fee , it.marketing_fee ) AS shop_marks_fee,
    fc_return_fee( it.transamount , it.cargo_fee , ms.pay_kick_fee , it.kickback_fee ) AS shop_kick_fee,
    it.shopid AS shopid,
    it.accept_date AS accept_date

FROM
    invoice_trans it INNER JOIN invoice i ON it.orderid = i.ordreid

WHERE
    i.status != 0

GROUP BY
    it.id;

and i use this select ;) tanks for help dudes.

SELECT
        SUM( beloeb ) AS beloeb,
        SUM( cargo ) AS cargo,
        SUM( shop_payout ) AS shop_payout,
        SUM( shop_adm_fee ) AS shop_adm_fee,
        SUM( shop_marks_fee ) AS shop_marks_fee,
        SUM( shop_kick_fee ) AS shop_kick_fee,
        accept_date,
        shopid

       FROM
        view_shop_invoicer_list

       WHERE
        accept_date >= '". $this->from_date ."' AND
        accept_date <= '". $this->to_date ."'

       GROUP BY
        shopid