TIME NOW
World current time now,
CALENDAR
Calendar monthly, yearly
login CONVERT LENGTH
login CONVERT TEMPERATURE
login DICTIONARIES, LISTS
login SCIENCE EDUCATION RELIGION
login WORK CALCULATOR
login CALCULATE LIFE

MYSQL dabatase. How to, tutorial, examples

Previous articlePage bottomNext article  ALL TOPICS

Mysql. select query example, subquery

 Example of Mysql query (4 tables):
one LEFT join to one LEFT join to one LEFT join to one

or can be
one LEFT join to many - one LEFT join to many,  one LEFT join to many

SELECT *
FROM jos_users
LEFT JOIN (
(

SELECT user_id, lastname, `type` , `id_r_teacher` , firstname, fullname
FROM jos_comprofiler
LEFT JOIN (

SELECT *
FROM t_teachers_choose_users
LEFT JOIN (
(

SELECT CONCAT( firstname, ' ', lastname ) AS fullname, user_id AS user_id_mod
FROM jos_comprofiler
) AS t1
) ON ( t1.user_id_mod = t_teachers_choose_users.id_r_teacher )
) AS t3 ON t3.user_id_mod = jos_comprofiler.user_id
) AS t1
) ON ( t1.user_id = jos_users.id )

Very slow, even setting index on: iso
SELECT *
FROM t_geo_countryinfo right join t_geo_alternatename_ru ON t_geo_countryinfo.geonameid = t_geo_alternatename_ru.geonameid
WHERE EXISTS (

SELECT *
FROM t_geo_post_available
WHERE t_geo_countryinfo.iso = t_geo_post_available.country_code

)

Million times faster

SELECT *
FROM t_geo_alternatename_ru
RIGHT JOIN (
(

SELECT *
FROM t_geo_countryinfo, t_geo_post_available
WHERE t_geo_countryinfo.iso = t_geo_post_available.country_code
) AS t1
) ON ( t1.geonameid = t_geo_alternatename_ru.geonameid )

Select similar rows in the table:
SELECT * 
FROM t_food, t_food AS vtable
WHERE vtable.id_r > t_food.id_r
AND vtable.name = t_food.name
AND SUBSTRING( vtable.text, 1, 100 ) = SUBSTRING( t_food.text, 1, 100 ) 
LIMIT 0 , 30

LEFT JOIN subqueries
 
Stack of four tables
one LEFT join to one LEFT join to one LEFT join to one

or good to case
one LEFT join to many - one LEFT join to many, one LEFT join to many:

SELECT *
FROM jos_users
LEFT JOIN (
(

SELECT user_id, lastname, `type` , `id_r_teacher` , firstname, fullname
FROM jos_comprofiler
LEFT JOIN (

SELECT *
FROM t_teachers_choose_users
LEFT JOIN (
(

SELECT CONCAT( firstname, ' ', lastname ) AS fullname, user_id AS user_id_mod
FROM jos_comprofiler
) AS t1
) ON ( t1.user_id_mod = t_teachers_choose_users.id_r_teacher )
) AS t3 ON t3.user_id_mod = jos_comprofiler.user_id
) AS t1
) ON ( t1.user_id = jos_users.id )


 
EXISTS

Select from jos_comprofiler, related records to jos_users:
SELECT *
FROM jos_comprofiler
WHERE EXISTS (

SELECT *
FROM jos_users
WHERE jos_comprofiler.user_id = jos_users.id
)

Slow query:
SELECT *
FROM t_geo_countryinfo right join t_geo_alternatename_ru ON t_geo_countryinfo.geonameid = t_geo_alternatename_ru.geonameid
WHERE EXISTS (

SELECT *
FROM t_geo_post_available
WHERE t_geo_countryinfo.iso = t_geo_post_available.country_code

)

Million times faster:

SELECT *
FROM t_geo_alternatename_ru
RIGHT JOIN (
(

SELECT *
FROM t_geo_countryinfo, t_geo_post_available
WHERE t_geo_countryinfo.iso = t_geo_post_available.country_code
) AS t1
) ON ( t1.geonameid = t_geo_alternatename_ru.geonameid )
 
Previous articlePage topNext article  ALL TOPICS


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

CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`surname` varchar(30) NOT NULL,
`email` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `users` 
(`id`, `name`, `surname`, `email`) 
VALUES
(1, 'ajona', 'aaaa', '[email protected] '),
(2, 'Janina', 'bbb', ' [email protected]'),
(3, 'Egle', 'Karalyte', 'c#c'),
(4, 'Petras', '', 'd[at]d'),
(5, 'Simas', 'ss', 'ss'),
(6, 'Jurgita', 'Jonaityte', ' [email protected]'),
(8, 'Angele', 'Angele', '[email protected]'),
(9, 'Jonas', 'Jonab', '');

CREATE TABLE IF NOT EXISTS `jobs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`place` varchar(30) NOT NULL,
`date` date NOT NULL,
`id_parent` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `jobs` (`id`, `name`, `place`, `date`, `id_parent`) VALUES
(1, 'Digging', 'Garden', '2013-11-19', 1),
(2, 'Playing', 'At home', '2013-11-20', 1),
(3, 'Cuting', 'Garden', '2013-11-04', 2),
(4, 'Ruling', 'Garden', '2013-11-19', 30);

CREATE TABLE IF NOT EXISTS `tools` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`id_parent` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `tools` 
(`id`, `name`, `id_parent`) 
VALUES
(5, 'showel', 1),
(6, 'tool 1', 1),
(7, 'tool 2', 3),
(8, 'tool 3', 3);



2016-05-09 02:59:37
Guest:
SELECT * FROM `users` LEFT JOIN `jobs` on `users`.`id` = `jobs`.`id_parent`

SELECT * FROM `users` RIGHT JOIN `jobs` on `users`.`id` = `jobs`.`id_parent`

SELECT * FROM `users` INNER JOIN `jobs` on `users`.`id` = `jobs`.`id_parent`


2016-05-09 03:00:42
Guest:
SELECT * FROM `users`
RIGHT JOIN `jobs` on `users`.`id` = `jobs`.`id_parent`
WHERE `users`.`id` IS NULL OR `users`.`id` = ""

SELECT `jobs`.`id_parent` FROM `users`
RIGHT JOIN `jobs` on `users`.`id` = `jobs`.`id_parent`
WHERE `users`.`id` IS NULL OR `users`.`id` = ""

SELECT CONCAT (`users`.`name`, " " , `users`.`surname`) as `worker`, `jobs`.`name` as `title`
FROM `users`
INNER JOIN `jobs` on `users`.`id` = `jobs`.`id_parent`

SELECT `users`.`name`, `jobs`.`id_parent` FROM `users`
RIGHT JOIN `jobs` on `users`.`id` = `jobs`.`id_parent`
WHERE `jobs`.`date` > '2013-01-01'


2016-05-09 03:05:48
Guest:
SELECT
`t0`.`id`,
`t0`.`name`,
`t0`.`surname`,
`t1`.*

FROM `users` `t0`
INNER JOIN
(
SELECT

`jobs`.`id_parent`,
`jobs`.`id`, `jobs`.`name` as darbas,
`tools`.`name` as irankis

FROM `jobs` LEFT JOIN `tools`
ON`jobs`.`id` = `tools`.`id_parent`
) as `t1`
ON `t0`.`id` = `t1`.`id_parent`


2016-05-16 03:31:56
Guest:
SELECT `id_parent`, count(`jobs`.`id`) as `amount`
FROM `jobs` GROUP BY `jobs`.`id_parent`

SELECT `t0`.`name`, `t0`.`surname`, `t1`.`amount`
FROM `users` `t0`
INNER JOIN
(SELECT `id_parent`, count(`jobs`.`id`) as `amount`
FROM `jobs` GROUP BY `jobs`.`id_parent`)   as `t1`
ON `t0`.`id` = `t1`.`id_parent`

###
SELECT * FROM `jobs`
LEFT JOIN `tools` on `jobs`.`id` = `tools`.`id_parent`


SELECT `t0`.`name`, `t0`.`surname`, `t1`.`*` FROM `users` `t0`
INNER JOIN
(
SELECT * FROM `jobs`
LEFT JOIN `tools` on `jobs`.`id` = `tools`.`id_parent`
) as `t1`
ON `t0`.`id` = `t1`.`id_parent`

###

SELECT

`jobs`.`id_parent`,
`jobs`.`id`,
`jobs`.`name` as `jobname`,
`tools`.`name` as `toolname`

FROM `jobs`
LEFT JOIN `tools` ON`jobs`.`id` = `tools`.`id_parent`


SELECT `t0`.`name`, `t0`.`surname`, `t1`.`*` FROM `users` `t0`
INNER JOIN
(
  SELECT

  `jobs`.`id_parent`,
  `jobs`.`id`,
  `jobs`.`name` as `jobname`,
  `tools`.`name` as `toolname`

  FROM `jobs`
  LEFT JOIN `tools` ON`jobs`.`id` = `tools`.`id_parent`

) as `t1`
ON `t0`.`id` = `t1`.`id_parent`


###

SELECT
`t0`.`id`,
`t0`.`name`,
`t0`.`surname`,
`t1`.*

FROM `users` `t0`
INNER JOIN
(
SELECT

`jobs`.`id_parent`,
`jobs`.`id`, `jobs`.`name` as darbas,
`tools`.`name` as irankis

FROM `jobs` LEFT JOIN `tools`
ON`jobs`.`id` = `tools`.`id_parent`
) as `t1`
ON `t0`.`id` = `t1`.`id_parent`

2016-05-16 03:32:32

 Use username: Guest, Anonymous, Programmer






QUOTES:
Show me a frigid women and, nine times out of ten, I'll show you a little man.
Julie Burchill
There is no knowledge, no light, no wisdom that you are in possession of, but what you have received it from some source.
Brigham Young
No culture can live, if it attempts to be exclusive.
Mohandas Karamchand Gandhi