MySQL Forums - Views

北京天安中医院 (no replies)

baoshanli 丹尼 — Fri, 24 May 2013 05:46:44 +0000

Query not returning what I expect (no replies)

Bodi K — Fri, 26 Apr 2013 17:42:05 +0000

I'm not sure why I'm having a brain freeze today, but I've gone in circles long enough, so I'm here for some sanity.

For background purposes. There are 17 questions in this table:
CREATE TABLE `question` (
`ID` tinyint(4) unsigned NOT NULL AUTO_INCREMENT,
`Question` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8$$

The real work is done here:
CREATE TABLE `client-question` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ClientID` int(11) NOT NULL,
`QuestionID` tinyint(4) unsigned NOT NULL,
`UserID` int(11) DEFAULT NULL,
`Value` tinyint(3) unsigned DEFAULT NULL,
`Timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
KEY `ClientID` (`ClientID`),
KEY `QuestionID` (`QuestionID`),
KEY `UserID` (`UserID`)
) ENGINE=MyISAM AUTO_INCREMENT=18115 DEFAULT CHARSET=utf8$$

I have another (insignificant for this post) table that has 1000 Clients, and another table that has 5 users. Each of the 5 user needs to answer the 17 questions for each of the 1000 clients. By default I have populated the client-question table with the necessary records (5x17x1000 rows). Value is null and Timestamp is null so that each user can see on their screen that they need to answer that question.

Here comes the tricky part. If it's a new record then the Value gets filled in with whatever they choose from the dropdown (1-5 from a combobox) . If there is already a Value then it creates a new record (so I can keep track of history as the user changes their mind about this client). The form needs to display always the most current Value entered for that client and user. It also needs to display all 17 questions.

Scenario:
UserID 1 is on the screen of ClientID 1. They have already answered QuestionID 1 five times, but the other 16 question have never been answered, so the 16 pre-populated Value and Timestamp records are null.

I need a query to return the 16 null questions and the most recent Value of the one answered question. I'm thinking something like this, but it doesn't work!

SELECT
MAX(ID),
ClientID,
QuestionID,
UserID,
`Value`,
`Timestamp`
FROM
`client-question`
WHERE UserID=1 OR UserID IS NULL
GROUP BY ClientID, QuestionID, UserID
HAVING ClientID = 1

It successfully returns the 16 questions that haven't been answered by anybody yet; it returns the 1 question that HAS been answered by UserID 1, and it even returns the correct row ID; however, it returns a Value and Timestamp from the first row, not the Value and Timestamp from the row that it should be (in this case the ID of the MAX(ID) is 18114, but the Value and Timestamp for the returned data is from ID 884). Ideally, in Access, I would use LAST(Value), LAST(Timestamp) but it doesn't return as expected.

Any ideas?

SQL Server (1 reply)

Jamie Paul — Fri, 12 Apr 2013 16:22:30 +0000

How to recover corrupt MDF file from SQL database?

Join (7 replies)

Austin Kalb — Mon, 15 Apr 2013 21:29:09 +0000

I have 2 tables, stockOptions and stockOptionTrades. In general, for each stockOption there are many stockOptionTrades'.

1) select count(*) from todaysStockOptions; returns 147774 in .125 seconds.
2) select count(*) from todaysStockOptionTrades; returns 78538 in 0.140 seconds.

but

3) select count(*) from todaysStockOptionTrades join todaysStockOptions using (optionSymbol); returns 78538 in 86 seconds!

Simple question: Why does the joined query take so much longer? Both tables are indexed on optionSymbol. What can I do to speed this up?

Thanks
Austin

I can access views on ONE server but not on another one (4 replies)

Heinz Stoellinger — Thu, 21 Feb 2013 22:47:16 +0000

Hello,
My problem is as follows:
The same view (combining more than one table) is defined on a particular mysql-server. SHOW GRANTS FOR userid shows:
(1) on Server1 (MySQL V 5.5.28-1-log (Debian-Wheezy), connected to the client through local lan, same network):
GRANT USAGE ON *.* TO 'userid'@'%' .
I can query the view as expected.
(2) on Server2 (MySQL V 5.1.66-0+squeeze1-log (Debian-Squeeze), connected to the client through the Internet:
GRANT ALL PRIVILEGES ON *.* TO 'userid'@'%' IDENTIFIED BY PASSWORD '***********' WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON 'dbname'.* TO 'userid'@'%';
GRANT SELECT ON 'dbname'.'viewViewName' TO 'userid'@'%';
Whenever I query the view on THIS server I get an empty set of replies.
Any ideas about what I do wrong?
Thanks a lot for any hints
H. Stoellinger

Any way to optimize view (no replies)

Olivier Bourdon — Mon, 21 Jan 2013 14:55:28 +0000

Views are really great however there are somecase where I find them very non performant and was wonderning if there was a way to cope with this.

Let's say I have a view which is a UNION of 3 select queries with INER JOINs and which look alike but do not work on the same tables.

Running explain on each and every of the 3 select with a WHERE clause containing
item.name like '%pattern%' shows perfect usage of indexes and each select return in
less than 1/10th of a second

However the view is defined with
CREATE VIEW MyInfos AS
select item.name .......
from ..... innner join .....
union
select item.name ......
from ..... innner join .....
union
select item.name ......
from ..... innner join .....

and of course running
select * from MyInfos where item.name like '%pattern%'
takes around 15s because the item.name like '%pattern%'
can not be used with each select to make proper uses of indexes

Is there a way to solve this or should I mode to procedure instead of view for this
very particular case ?

View problems when updating to 5.5 from 5.1 (1 reply)

Howard Walker — Tue, 15 Jan 2013 17:34:10 +0000

This may be too advanced for the Newbie forum, but I cannot work out just where it should go.

I just upgraded to version MySQL 5.5 from 5.1. My website uses a view for all its operations in some 80 files. There are 8 files, all linked into this view, which was originally generated by Microsoft Access and moved across into MySQL.

When I look at the view table (and only the view table), I get a message just under the Browse button that reads "#1449 - The user specified as a definer (username@'192.168.0.%) does not exist" The correct username is installed in the user tables, and has full rights as far as I can see, as he is a copy of the root user with a different password. He can access all parts of the system and should be able to do anything (including GRANT). He is the same username and has the same password as the original one, though I believe that version 5.5 uses a different hashing algorythm for passwords.

The database has been exported and imported from 5.1 database into a 5.5.25 database. I have searched chapter 19 on Views, but am sorry to say that it makes little sense at all, and does not seem to cover any changes that may be required due to the update from 5.1. It mentions "definer" in many paragraphs, but does not state how to change or create a definer. The use of a definer was never mentioned in 5.1, or if it was I have never come across it in the 11 years or so that my website has been running, in various versions of MySQL from 4 upwards.
The same problem happens when anyone attempts to access the website, which uses the same username/password combination as the control panel.
So how do I fix this problem?
Maybe you have come across it.

Join with a View (no replies)

Albert Stewart — Thu, 10 Jan 2013 20:33:22 +0000

Using 5.5 I created a View with WorkBench:
USE `rxcds_db`;
CREATE OR REPLACE VIEW `rxcds_db`.`ViewRenalData` AS
SELECT Patient.Acct, Patient.LastName, Patient.FirstName, Patient.Loc, Patient.Age, Patient.Sex, Patient.Ht, Patient.Wt, Lab.LabID, Lab.CreatTime, Lab.Creat,
Lab.BUNTime, Lab.BUN
FROM (Lab INNER JOIN
Patient ON Patient.Acct = Lab.Acct);

The SELECT .... returns a table of data fine, but if I
"select * from ViewRenalData;" the returned table has no data.

I also note that in WorkBench the ViewRenalData displays as a View in Model Overview, but is listed as a Table in the Object Browser of the Sql Editor.

Please advise. Why doesn't "select * from ViewRenalData" return data ?

VIEW crashing MySQL... (no replies)

Christophe NOUVEL — Wed, 09 Jan 2013 07:54:43 +0000

Hi everybody,

I don't frequently use views in MySQL, but a customer tried the following :
- create a table with a "text" column containing 2 digits integers,
- select avg on this field : OK
- create a view with this select : crash MySQL???
- the same view with a round(avg()) behaves correctly.

Crash occurs on genuine CentOS 6.3 32 bits updated :
LSB Version: :core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: CentOS
Description: CentOS release 6.3 (Final)
Release: 6.3
Codename: Final
mysql-5.1.66-2.el6_3.i686
mysql-connector-odbc-5.1.5r1144-7.el6.i686
mysql-libs-5.1.66-2.el6_3.i686
MySQL-python-1.2.3-0.3.c1.1.el6.i686
mysql-server-5.1.66-2.el6_3.i686
perl-DBD-MySQL-4.013-3.el6.i686
php-mysql-5.3.3-14.el6_3.i686

The same view on
LSB Version: :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: CentOS
Description: CentOS release 5.8 (Final)
Release: 5.8
Codename: Final
mysql-5.0.95-1.el5_7.1
mysql-devel-5.0.95-1.el5_7.1
mysql-server-5.0.95-1.el5_7.1
perl-DBD-MySQL-3.0007-2.el5
php-mysql-5.1.6-39.el5_8

behaves as expected.

An export of the database :
test.sql
-- phpMyAdmin SQL Dump
-- version 3.5.4
-- http://www.phpmyadmin.net
--
-- Client: localhost
-- Généré le: Mer 09 Janvier 2013 à 08:28
-- Version du serveur: 5.1.66
-- Version de PHP: 5.3.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Base de données: `test`
--

-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `avg`
--
DROP VIEW IF EXISTS `avg`;
CREATE TABLE IF NOT EXISTS `avg` (
`Moyenne` double
);
-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `round_avg`
--
DROP VIEW IF EXISTS `round_avg`;
CREATE TABLE IF NOT EXISTS `round_avg` (
`Moyenne` double(17,0)
);
-- --------------------------------------------------------

--
-- Structure de la table `t_tir`
--

DROP TABLE IF EXISTS `t_tir`;
CREATE TABLE IF NOT EXISTS `t_tir` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`score` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

--
-- Contenu de la table `t_tir`
--

INSERT INTO `t_tir` (`id`, `score`) VALUES
(1, '87'),
(2, '93'),
(3, '90'),
(4, '83'),
(5, '90'),
(6, '83'),
(7, '80'),
(8, '77'),
(9, '73'),
(10, '87'),
(11, '83'),
(12, '73'),
(13, '93'),
(14, '87'),
(15, '77'),
(16, '80'),
(17, '73'),
(18, '93'),
(19, '80'),
(20, '93'),
(21, '93'),
(22, '83'),
(24, '83'),
(25, '77');

-- --------------------------------------------------------

--
-- Structure de la vue `avg`
--
DROP TABLE IF EXISTS `avg`;

CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`localhost` SQL SECURITY DEFINER VIEW `avg` AS select avg(`t_tir`.`score`) AS `Moyenne` from `t_tir`;

-- --------------------------------------------------------

--
-- Structure de la vue `round_avg`
--
DROP TABLE IF EXISTS `round_avg`;

CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`localhost` SQL SECURITY DEFINER VIEW `round_avg` AS select round(avg(`t_tir`.`score`),0) AS `Moyenne` from `t_tir`;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Any idea??

Thanks,

Christophe.

Crown Capital Eco Management-Zimbio (no replies)

Macki Andrews — Fri, 14 Dec 2012 05:47:59 +0000

Crown Capital Eco Management works with government bodies, international entities, private sectors and other non-governmental organizations in providing extensive information to the public, media and policymakers that are involved in addressing environmental issues and sustainable initiatives in a worldwide scale. Though our group does not personally conduct research, we analyze and review both recent and old data on technical and socio-economic sectors that are relevant to our field, which is environment preservation.
We all know that regardless of where you live, the weather is often unpredictable. A comfy 60 degree winter day in the Midwest; chilly and breezy on a late spring day in the pacific coast. To some of us, the prevalence of unusual weather is simply the work of Mother Nature.

Reviews

Northern Europe picks on the Atlantic Ocean because of its wet summer according to a new study. The rising and falling of ocean temperature or the so called cyclical deception is seen as a major extortion on the weather. The said pattern reported will last long as the Atlantic warming persists. The research was carried out at the University of Reading and is published in the journal Nature
Geoscience.
The heavily forested city of Ketchikan, Alaska, is built on rock and surrounded by water. Every commodity that comes into Ketchikan must arrive by sea or air. The use of fuel oil is problematic for both economic and environmental reasons because the oil must be obtained and refined elsewhere and transported (using additional fuel). What's more, fuel oil is subject to price instability.

Is MySQL suitable for software management? (no replies)

Prasoon Prahaladan — Thu, 13 Dec 2012 11:46:05 +0000

If we use blogs or CMS softwares like wordpress and drupal, we have to place the website on a web host that provides a MySQL database. The database can be used by any software (for ex: educational software,MLM software, computer software) to store articles, web pages as well as comment's made by the visitors.

Refernce http://infinitemlmsoftware.com

Brightbridge Wealth Management-Windows messenger live to close-Rediffpages (no replies)

Maxx Webber — Thu, 13 Dec 2012 07:28:40 +0000

Microsoft has announced it intends to “retire” its instant message chat tool and replace it with Skype’s messaging tool. The news comes 18 months after the software giant announced it was paying $8.5bn (£5.3bn) for the communications software developer. Brightbridgewealth-management report, Microsoft said Windows Live Messenger (WLM) would be turned off by March 2013 worldwide, with the exception of China. It reflects the firm’s determination to focus its efforts on Skype.
WLM launched in 1999 when it was known as MSN Messenger. Over time, photo delivery, video calls and games were added to the package’s text-based messages. According to internet analysis firm Comscore-Brightbridgewealth-management, WLM still had more than double the number of Skype’s instant messenger facility at the start of this year and was second only in popularity to Yahoo Messenger.
But the Brigthbridge report suggested WLM’s US audience had fallen to 8.3 million unique users, representing a 48% drop year-on-year. By contrast, the number of people using Skype to instant message each other grew over the period.
“When a company has competing products that can result in cannibalization it’s often better to focus on a single one,” said Brian Blau from the consultancy Gartner.
“Skype’s top-up services offer the chance to monetize its users and Microsoft is also looking towards opportunities in the living room.
“Messenger doesn’t seem like an appropriate communications platform for TVs or the firm’s Xbox console – but Skype does.”
He also noted that the firm had opted to integrate Skype into its new Windows Phone 8 smartphone software, eclipsing the effort to integrate WLM into the message threads of the operating system’ previous version. To ease the changeover, Microsoft is offering a tool to migrate WLM messenger contacts over. The risk is that the move encourages users to switch instead to rival platforms such as WhatsApp Messenger, AIM or Google Talk. But Microsoft is at least partially protected by its tie-up with Facebook last year. Skype video calls are now offered as an extra to the social network’s own instant messaging tool.

Views - Affected by 'root' accounts? (2 replies)

Leon Seaman — Wed, 28 Nov 2012 13:07:43 +0000

Hi all,

My first post, so i'll try and be as clear as possible.

I hope someone can shed some light on this for me.

I've set up some views on my MySQL Database to make the syntax a little clearer when running through php.

My user setup to start with was basically:

User - Host
============
root - localhost
root - %
user1 - %
user2 - %
user2 - %

Which worked fine. However:

When checking the logs today i was concerned to see a load of failed attempts from around the world (China most of them) so decided to restrict the root@% to a specific location.

So, the user setup changed to:

User - Host
============
root - localhost
root - domain.com
user1 - %
user2 - %
user2 - %

But now, if i log in as any of the user accounts i cannot access anything that uses a view, general SELECT statements work fine however.

I just can't understand what's going on, there's nothing in the php connect strings that refer to a root account so i'm finding it hard to understand how this can affect things.

Would appreciate any help on this.

For reference MySQL is running on a windows 2008 server and the connections are being made off-site.

Thanks.

Simple But Not So Simple (2 replies)

Jamie B — Fri, 23 Nov 2012 02:15:35 +0000

Hi All,

I have the following table structure machine_statuses:

p_id machine_id machine_status status_date

1 100 start 2012/10/05 90:00:00
2 101 start 2012/10/05 90:00:01
3 101 moving 2012/10/05 90:00:02
4 102 start 2012/10/05 90:00:03

I want to group the above table by machine_id and have only the LASTEST status as entered in status_date. Hence, I am doin the following SQLs, but it is not working:

select p_id, machine_id, machine_status, MAX(status_date) from machine_statuses
group by machine_id

or

select p_id, machine_id, machine_status, status_date from machine_statuses
group by machine_id
order by MAX(status_date)

or

select p_id, machine_id, machine_status, status_date from machine_statuses
group by machine_id
order by MAX(p_id)

...but I am getting incorrect results, like:

1 100 start 2012/10/05 90:00:00
2 101 start 2012/10/05 90:00:02
4 102 start 2012/10/05 90:00:03

or

1 100 start 2012/10/05 90:00:00
2 101 start 2012/10/05 90:00:01
4 102 start 2012/10/05 90:00:03

...when I would like the following:

1 100 start 2012/10/05 90:00:00
3 101 moving 2012/10/05 90:00:02
4 102 start 2012/10/05 90:00:03

...I would be most appreciative if anyone could help me solve this challenging scenario.

Many thanks,
Jamie B

Strange Outcome (no replies)

Jamie B — Thu, 04 Oct 2012 17:59:46 +0000

Hi All,

I have a very strange results when running the following query using MySQL 5.1.65 Community:

select sess.session_id, auser.user_id, user_asset.entity_id, ast.asset_id, a_status.asset_state

FROM `session` AS sess

LEFT JOIN `app_user` AS auser ON (sess.user_id=auser.user_id)

LEFT JOIN `group_entity_list` AS user_asset ON (auser.group_id=user_asset.group_id AND user_asset.entity_type = 'asset')

LEFT JOIN `asset` AS ast ON (user_asset.entity_id=ast.asset_id)

LEFT JOIN `asset_status` AS a_status ON (a_status.asset_id=ast.asset_id)

group by a_status.asset_id

order by MAX(a_status.last_updt_drvtime)

...from the above query I was hoping to get only the lastest asset statuses as I have used the MAX function of the DATETIME field for last update.

I could probably solve this problem by doing a nested query, but I believe that is not the best way forward.

Anyone have any ideas on how to solve this seemly simple problem, but in reality it is much more complex???

For example (in short may not reflect the column names above), if I have the following subset table data:

1 (status_asset_id), started (status_stage), 2012/10/04 09:00:01 (datetime)
1 (status_asset_id), moving (status_stage), 2012/10/04 09:00:02 (datetime)
2 (status_asset_id), starting (status_stage), 2012/10/04 09:00:03 (datetime)
3 (status_asset_id), starting (status_stage), 2012/10/04 09:00:04 (datetime)

The expected output should be:

1 (status_asset_id), moving (status_stage), 2012/10/04 09:00:02 (datetime)
2 (status_asset_id), starting (status_stage), 2012/10/04 09:00:03 (datetime)
3 (status_asset_id), starting (status_stage), 2012/10/04 09:00:04 (datetime)

...that is, status_asset_id with value 1, should have the moving state listed, but in reality, I am getting:

1 (status_asset_id), started (status_stage), 2012/10/04 09:00:01 (datetime)
2 (status_asset_id), starting (status_stage), 2012/10/04 09:00:03 (datetime)
3 (status_asset_id), starting (status_stage), 2012/10/04 09:00:04 (datetime)

Many thank...

Use xml data as view column in Mysql (no replies)

Rulin Tang — Thu, 04 Oct 2012 09:37:35 +0000

hi all,
In MSSQL code, it's easy to collect data in xml format as a column when create a view.
In mysql, kind of like this:

CREATE VIEW myview
AS
SELECT event.End_date as date, (SELECT people.Last_name from people INNER JOIN ... WHERE ... FOR XML PATH('') )as person,
FROM event
INNER JOIN ...
WHERE ...

Where "FOR XML PATH('')" comes from MSSQL. From my understanding, it will format the subquery results as xml, and then this xml data will be used as a colum in view. Because this subquery will return many rows (This makes sense why this subquery uses xml format to collect data), if we ingore this xml code, there'll be problems im mysql, saying the subquery has more than one rows.
On the other hand,
When SELECT event.End_date as date returns one row, the subquery will return more than one rows.

According to http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html, is this the only handy way to get xml output in Mysql? I mean 'It is possible to obtain XML-formatted output from MySQL in the mysql and mysqldump clients by invoking them with the --xml option'.

According to http://web.archive.org/web/20100212101622/http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html it's complicated to get xml data.

Is there any xml solution for view in Mysql? Thank you.

Creating view for sales data analysis (1 reply)

P Kiran — Sat, 22 Sep 2012 13:55:53 +0000

Hi -

I have 3 tables: customers, suppliers, and invoices, from which I need to create some sales history reports with invoice amount sums for various date ranges, corresponding to each customer-supplier pairing:

- currmon
- prevmtd (from first to today for this month last year)
- curryr
- prevytd (from first day of last year up to today)
- prevyr (all of last year)

and 2 more columns:

- lastinvoice (date of most recent invoice for this customer-supplier pairing)
- pctdiffytd (percent *change* in total amount between prevytd and curryr)

I created a set of simple views for each of the amount sum columns and am attempting to combine them into one result set. The result should have *at most* one row for each customer-supplier pairing (some customers may not have bought from all suppliers).

My result has way too many records, 1216 total, with only 6 customers and 3 suppliers in my sample data. here is the SQL I am using (FYI, this application is being migrated from MS Access):

CREATE ALGORITHM=UNDEFINED DEFINER=`evsalestool`@`%` SQL SECURITY DEFINER VIEW `customer_sales`
AS select
`suppliers`.`name` AS `supplier`,
`suppliers`.`id` AS `sid`,
`customer_sales_currmon`.`currmon` AS `currmon`,
`customer_sales_prevmtd`.`prevmtd` AS `prevmtd`,
`customer_sales_curryr`.`curryr` AS `curryr`,
`customer_sales_prevytd`.`prevytd` AS `prevytd`,
`customer_sales_prevyr`.`prevyr` AS `prevyr`,max(`invoices`.`date`) AS `lastinvoice`,
`invoices`.`customer` AS `customer`
from ((((((`suppliers` join `invoices` on((`suppliers`.`id` = `invoices`.`supplier`))) left join `customer_sales_prevytd` on((`suppliers`.`id` = `customer_sales_prevytd`.`supplier`))) left join `customer_sales_currmon` on((`suppliers`.`id` = `customer_sales_currmon`.`supplier`))) left join `customer_sales_prevmtd` on((`suppliers`.`id` = `customer_sales_prevmtd`.`supplier`))) left join `customer_sales_prevyr` on((`suppliers`.`id` = `customer_sales_prevyr`.`supplier`))) left join `customer_sales_curryr` on((`suppliers`.`id` = `customer_sales_curryr`.`supplier`))) group by `suppliers`.`name`,`suppliers`.`id`,`customer_sales_currmon`.`currmon`,`customer_sales_prevmtd`.`prevmtd`,`customer_sales_curryr`.`curryr`,`customer_sales_prevytd`.`prevytd`,`customer_sales_prevyr`.`prevyr`,`invoices`.`customer` order by `customer_sales_curryr`.`curryr` desc;

Does anyone have a suggestion on how I might resolve this? Thanks!

Combining a tree structured Group model with its HABTM User model in one VIEW (no replies)

Evert van Brussel — Thu, 20 Sep 2012 00:04:24 +0000

Here is the situation:
I have a tree structured model Group which has the columns id, parent_id, name, lft and rght. I also have a model User which has the columns id, name and password. Group and User have a HABTM relationship using the join-table groups_users

Now I want to create a view which creates a new tree which includes all the groups and all the users as children of the groups they belong to. The view should have the columns id, parent_id, model, foreign_key, lft and rght.
The id column should just be the nth row of course and parent_id, lft and rght can't be just copied from the Group model, they will have to calculated for the tree of the view.

An example of what the view could look like:

| id | parent_id | model | foreign_key | lft | rght
----------------------------------------------------
| 1 | NULL | Group | 1 | 1 | 14
| 2 | 1 | User | 1 | 2 | 3
| 3 | 2 | Group | 2 | 4 | 7
| 4 | 3 | User | 3 | 5 | 6
| 5 | 4 | Group | 3 | 8 | 13
| 6 | 5 | User | 2 | 9 | 10
| 7 | 5 | User | 4 | 11 | 12
| 8 | NULL | Group | 4 | 15 | 20
| 9 | 8 | User | 4 | 16 | 17
| 10 | 8 | User | 5 | 18 | 19

Now my question is, how the hell do I do this?
Can it be done with some super complex SELECT statement or do I need to use a procedure?
I'm not super skilled at MySQL, I can do simple SELECTs and JOINs, but this is way beyond my skills.

view data is retrieved in full underlying (no replies)

monty shely — Wed, 19 Sep 2012 09:40:49 +0000

We have a project tracking system our employees are required to fill out each day. They log in, select the project they worked on, and enter the number of hours they worked on it. Managers then run reports, for budgetary reasons, that show them the cost of each project. I fetch the report in a simple query that goes through all the projects (Projects table), joins each project up with their rate (Rates table), and sums up the cost for each project. The tricky part now is a manager may set a specific rate for an employee working on project X. Therefore when the query runs it must use a different rate (from the Rates table) for the given project / employee. sons of anarchy season 5 episode 2

The calculation I'm using in the query is a basic: sum(RATE * HOURS)

A quick summary of the table relationships:
All projects have a "default" rate associated with them and thus will have 1 entry in Rates for each project. Any additional entries in Rates for a particular project will be because an empID was given to change the rate for a particular employee for that project.
sons of anarchy season 5 episode 2
The query I'm currently using to fetch a list of projects and their "default" costs:

SELECT p.projID, p.projName, sum(r.rate * ph.hours) AS totalCost
FROM Projects p, ProjectHours ph, Rates r
WHERE p.projID=r.projID
GROUP BY p.projID, p.projName
sons of anarchy season 5 episode 2
Now I just need to throw in the twist of an specific employee having a different rate then the "default" rate for a particular project. Any help would be greatly appreciated. The creation of a View is acceptable... but I'd rather see if this is possible using some combination of JOINS.

Help with read only view please (no replies)

Bodi K — Wed, 12 Sep 2012 16:19:18 +0000

Hi,

I've done some research on how to solve the non-updatable recordset problem but have not been able to figure it out, so I'm hoping somebody here can provide some direction. I can't think of how to make my view any simpler and I have all the necessary PKs defined.

I have one simple table. Here is the create table:

CREATE TABLE `cast` (
`ID` int(11) NOT NULL AUTO_INCREMENT,

`ImportDate` datetime NOT NULL,
`ImportUser` varchar(15) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `ImportDate` (`ImportDate`),
) ENGINE=InnoDB AUTO_INCREMENT=37695 DEFAULT CHARSET=utf8$$

This table will have thousands of records imported each day - it's a historical table. What I want to do is create a view that gives me just todays records...but I need to be able to edit them.

Here's the view I created:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cast` AS select `c`.`Sales Series` AS `Sales Series`,`c`.`Account Number` AS `Account Number`,`c`.`Account Name` AS `Account Name`,`c`.`Account ID` AS `Account ID`,`c`.`ImportDate` AS `ImportDate`,`c`.`ImportUser` AS `ImportUser` from (`cast` `c` join `max_cast` on((`c`.`ImportDate` = `max_cast`.`MaxOfImportDate`)))

and here's the view for the max_cast view that the above is joined with:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `max_cast` AS select max(`cast`.`ImportDate`) AS `MaxOfImportDate` from `cast`

Any ideas on how I can accomplish this? FYI, I will be using this view in MS Access.

Thanks :)

MySQL View not responding? - URGENT (1 reply)

Venugopal Saride — Mon, 25 Jun 2012 23:01:00 +0000

Hi All,

I have two views in which I am able to run the select statement successfully but when i create it as a view with below statement example:

create view XXXX as (select.....from....) is not working. It is ugent - Please help.

concat in where clause (1 reply)

pradeep kumar — Mon, 25 Jun 2012 23:00:05 +0000

# sorry wrong place to post # i dont know how to delete my post.

Why doesn't these query work?

1:

select * from table where col in (concat("\'","value","\'"))

2:

select * from table where col1 = if ( col1 = 'value1', col1 = concat_ws(" AND ", "\'value1\'","col2 = \'value2\'" ) ,""))

if select does not work in view (1 reply)

Denis Kochlyak — Wed, 30 May 2012 07:29:19 +0000

Hi, I am new in mysql and i have a problem to display data in a view, but when I execute my querry without create view, the result is correct. It is only when the view is created the coloumn in question is displaying zeros. My code is the following:

select`p`.`Nom` AS `nom`,
`p`.`Prenom` AS `prenom`, `c`.`Anne` AS `anne`,
`c`.`Personnes_id` AS `Personnes_id`,
`c`.`Date_Debut_Conge` AS `Date_Debut_Conge`,
`c`.`Date_Fin_Conge` AS `Date_Fin_Conge`,
`c`.`DemiJourneD` AS `DemiJourneD`,
`c`.`DemiJourneF` AS `DemiJourneF`,
`c`.`Type_Absence` AS `Type_absence`,
`c`.`Notes` AS `Notes`,
(select if (`c`.`Type_Absence`='congé',((select count(`congett`.`calendrier`.`WeekEnd`) from `congett`.`calendrier`
where ((`congett`.`calendrier`.`Date` between `c`.`Date_Debut_Conge` and `c`.`Date_Fin_Conge`)
and (`congett`.`calendrier`.`WeekEnd` <> 1) and (`congett`.`calendrier`.`CongeLULU` <> 1) and
(`congett`.`calendrier`.`AutreConge` <> 1))) - `c`.`DemiJourneD` - `c`.`DemiJourneF` +
`c`.`CongesDejaPris` ),0)) AS `CongePris`,
((select count(`congett`.`calendrier`.`WeekEnd`) from `congett`.`calendrier` where
(`congett`.`calendrier`.`Date` between `c`.`Date_Debut_Conge` and `c`.`Date_Fin_Conge`))
- `c`.`DemiJourneD` - `c`.`DemiJourneF`) AS `Quantite`
from Personnes p join conges_utilises c on p.id= c.`Personnes_id`

Obviusly the issue is on the "select if" level, because the column is displaying "0" in the view.
My sevrer version is 5.5.23

Adding firewall rule for MySQL55 on port 3306. (no replies)

mohit negi — Wed, 25 Apr 2012 21:26:39 +0000

Adding firewall rule for MySQL55 on port 3306 failed.though mysql server is installed.please help me with this.
how can i solve it?

Can Trigger check authorization ? (no replies)

Didier Morandi — Tue, 17 Apr 2012 12:34:13 +0000

Hello,
I see in the doc that a trigger can be fired before and/or after the table is actually updated.
Can a before-trigger start a procedure or any other process to check the authorization of a user to perform the requested sql command on that particular table?
Thanks.

create view with new field that counts duplicate entries (no replies)

Kenny Ford — Mon, 16 Apr 2012 15:25:34 +0000

I have a table where i track test data. I would like to create a view with a new field for counting, so when a duplicate entry is made on a serial number field the counter will auto increment by 1. Is this possible? I have been searching and have not found anything yet that will work.

Create VIEW table using multiple inner join & union error (1 reply)

MAGESH GEORGE — Fri, 23 Mar 2012 07:19:50 +0000

hi..
below this is my working sql statement. i need to convert this to a VIEW table format.

SELECT a.item, b.item_id, b.item_name, a.certification_id, c.certification_type,  a.certification_serial_no, a.certification_status, a.row_id 
FROM 
(SELECT mitrace_business_location_certification.business_location_id AS item_id, 'GLN' AS item, certification_id, certification_serial_no, certification_status, id AS row_id FROM mitrace_business_location_certification WHERE  certification_status IS NOT NULL AND deleted=FALSE
UNION 
SELECT  mitrace_epc_certification.epc_id AS item_id, 'GTIN' AS item, certification_id, certification_serial_no, epc_certification_status AS certification_status, id AS row_id FROM mitrace_epc_certification WHERE epc_certification_status IS NOT NULL AND deleted=FALSE) a

INNER JOIN (SELECT mitrace_business_location.location_code AS item_name, 'GLN' AS item, id AS item_id FROM mitrace_business_location WHERE location_code IS NOT NULL AND deleted=FALSE
UNION
SELECT mitrace_epc.code AS item_name, 'GTIN' AS item, id AS item_id FROM mitrace_epc WHERE id IS NOT NULL AND deleted=FALSE) b 
ON b.item_id=a.item_id

INNER JOIN mitrace_certification c 
ON c.id=a.certification_id;

When i changed it, it gives syntax error at the 1st inner join.

DROP TABLE IF EXISTS mitrace_view_epc_bizloc_certs;
CREATE OR REPLACE
VIEW mitrace_view_epc_bizloc_certs AS SELECT a.item, b.item_id, b.item_name, a.certification_id, c.certification_type,  a.certification_serial_no, a.certification_status, a.row_id, 

(SELECT mitrace_business_location_certification.business_location_id AS item_id, 'GLN' AS item, certification_id, certification_serial_no, certification_status, id AS row_id FROM mitrace_business_location_certification WHERE  certification_status IS NOT NULL AND deleted=FALSE
UNION 
SELECT  mitrace_epc_certification.epc_id AS item_id, 'GTIN' AS item, certification_id, certification_serial_no, epc_certification_status AS certification_status, id AS row_id FROM mitrace_epc_certification WHERE epc_certification_status IS NOT NULL AND deleted=FALSE) a

INNER JOIN 
(SELECT mitrace_business_location.location_code AS item_name, 'GLN' AS item, id AS item_id FROM mitrace_business_location WHERE location_code IS NOT NULL AND deleted=FALSE
UNION
SELECT mitrace_epc.code AS item_name, 'GTIN' AS item, id AS item_id FROM mitrace_epc WHERE id IS NOT NULL AND deleted=FALSE) b 
ON b.item_id=a.item_id

INNER JOIN mitrace_certification c 
ON c.id=a.certification_id;

please help....

CREATE VIEW vs JOIN (1 reply)

Heru Setiawan — Tue, 06 Mar 2012 20:57:42 +0000

Hi everyone,

I have the following query:

SELECT t.*, a.hits AS ahits
FROM t, a
WHERE (t.TRACK LIKE 'xxx')
AND a.A_ID = t.A_ID
ORDER BY t.hits DESC, a.hits DESC

which runs very frequently. Table `t` has around 15M+ rows and `a` has around 3M+ rows.

When I did an `EXPLAIN` on the above query, I received a note saying that it always created a temp table. I noticed that creating a temp table based on the above query took quite a while. And, this is done plenty of time.

Thus, I am wondering if I create a view using the above say:

CREATE VIEW v_t_a
SELECT t.*, a.hits AS ahits
FROM t, a
WHERE a.A_ID = t.A_ID

And change my code to:

SELECT * FROM v_t_a WHERE TRACK LIKE 'xxx' ORDER BY hits DESC, ahits DESC

Will it improve the performance? Will it remove the create temp table time?

Thank you so much for your help!

OR clause Vs In Clause in Views (3 replies)

Naveen Nayak — Tue, 06 Mar 2012 20:56:35 +0000

mysql Server version: 5.1.41
ubuntu 10.04

i came across a scenario when modifying queries and wanted to know why MySQL behaved differently -
Basically i am creating a view and when i query the view, the number of rows read are different for an "IN" clause and for an "OR" clause - below is a simple example

Note that the result set is the same for both queries

CREATE TABLE country (
id_country int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
PRIMARY KEY (id_country)
) ENGINE=InnoDB;

INSERT INTO country (name) VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H');

CREATE TABLE status (
id_status int(11) NOT NULL AUTO_INCREMENT,
id_country int(11) NOT NULL,
status tinyint(4) NOT NULL,
PRIMARY KEY (id_status)
) ENGINE=InnoDB;
ALTER TABLE status ADD INDEX ( id_country );
ALTER TABLE status ADD FOREIGN KEY ( id_country ) REFERENCES test.country (id_country) ON DELETE RESTRICT ON UPDATE RESTRICT ;

INSERT INTO status(id_country, status) VALUES
(1,0), (2,1), (3,0), (4,1), (5,0),(6,1), (7,0), (8,1);

CREATE
ALGORITHM=MERGE
VIEW view_country AS
SELECT c.*, s.id_status, s.status
FROM country c JOIN status s ON c.id_country = s.id_country;

===================================================================================
The below 2 explain statements show different number of rows parsed

EXPLAIN EXTENDED
SELECT * FROM view_country WHERE id_country IN (1, 2, 3);

EXPLAIN EXTENDED
SELECT * FROM view_country WHERE id_country = 1 OR id_country = 2 OR id_country = 3;

The query with "OR" clause reads lesser rows compared to "IN" - and this adds up for huge tables and joins

Can someone help me understand why this is so

Thank you for your time.

Searching a varbinary field as a number (2 replies)

Melissa Jacob — Thu, 02 Feb 2012 13:36:48 +0000

Hello,
I am new to mySQL so still learning. I have created some views using tables in which I have concatenated a text and number (REAL) field together. However, when I search this new field in the view (now a varbinary field), I'd still like to search it based on the separate number field of which it is constructed. However, it is searched as the varbinary. Is it possble to search as a number but display as the concatenated version (easier to look at and decreases width of view).
thanks in advance,
melissa