Displaying 1-10 of 17 results.
Not Resolved: MySQL count where columns fields have same value
posted by admin on November 1, 2016
I have rows, and I want count my select with criteria IN and want to count rows, which is having same values for all rows
Example:

id name lastname age
1 Abc defghij 12
2 Def asdasd 15
3 Ghi qwewqeq 18
4 Abc zzzzz 12
5 Abc yyy 12
6 Abc uuuu 12

IN array

1, 4, 5, 6

Same fields:

`name`, `age`

I want to see RESULTS LIKE
Result SELECT:

id name lastname age
1 Abc defghij 12
4 Abc zzzzz 12
5 Abc yyy 12

Result SELECT COUNT(*):

3
Read more
Resolved: Уникальные пары MySQL из одной таблицы. Distinct pairs in MySQL join (same table)
posted by admin on March 21, 2016
Есть таблица товаров.

CREATE TABLE `goods` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8


Она содержит следующие значения.

`id` `name` 1 Яблоки 2 Яблоки 3 Груши 4 Яблоки 5 Апельсины 6 Груши


Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:

(1,2), (4,1), (2,4), (6,3)
Read more
Wiki: Задача MySQL. Выбрать из таблицы те книги, у которых два и более автораwiki
posted by admin on August 24, 2016
Таблица авторов:

CREATE TABLE `tbl_author` (
`id` int(10) NOT NULL,
`name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `tbl_author`
ADD PRIMARY KEY (`id`);


Таблица книг

CREATE TABLE `tbl_book` (
`id` int(10) NOT NULL,
`name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `tbl_book`
ADD PRIMARY KEY (`id`);


Таблица связей межу таблицей книг и авторов

CREATE TABLE `tbl_book_author` (
`id` int(10) NOT NULL,
`book_id` int(10) NOT NULL,
`author_id` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `tbl_book_author`
ADD PRIMARY KEY (`id`),
ADD KEY `book_id` (`book_id`),
ADD KEY `author_id` (`author_id`);


Решение

SELECT book.id, book.name, COUNT(ba.author_id) AS cnt FROM `tbl_book_author` ba
LEFT JOIN tbl_book book
ON book.id = ba.book_id
GROUP BY ba.book_id
HAVING cnt > 1
Read more
Wiki: Joining Three or More Tables SYNTAXwiki
posted by admin on June 18, 2016
Although each join specification joins only two tables, FROM clauses can contain multiple join specifications. This allows many tables to be joined for a single query.

The ProductVendor table of the AdventureWorks2008R2 database offers a good example of a situation in which joining more than two tables is helpful. The following Transact-SQL query finds the names of all products of a particular subcategory and the names of their vendors:

SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;
Read more
Resolved: MySQL Like string in multiple value by one script
posted by admin on June 18, 2016
I always thought that you could use OR in a LIKE statment to query things in MySQL. So, if I wanted to compare multiple fields in a row to 1 keyword or term:

SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword%';

and if I had an array of words to compare:

SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword1%'
AND Column1 OR Column2 LIKE '%keyword2%';

I don't believe that syntax is correct, however. Is there an efficient method of writing this aside from something like:

SELECT * FROM MyTable WHERE Column1 LIKE '%keyword1%' OR Column2 LIKE
'%keyword1%' AND Column1 LIKE '%keyword2%' OR Column2 LIKE '%keyword2%';
Read more
Resolved: Select last row in MySQL
posted by admin on May 21, 2016
How can I SELECT the last row in a MySQL table?Read more
Resolved: MySQL - Count Number of Unique Values
posted by admin on May 21, 2016
If I have three columns:

orderNumber, name, email

and I would like to count how many unique emails are in the table how would I go about doing so?

A statement like:

SELECT count(email) FROM orders

gives me the total count.
I tried

SELECT DISTINCT count(email) FROM orders

but that does not seem to be giving me the numbers I am expecting.Read more
Resolved: How to get the latest record in each group using GROUP BY?
posted by admin on May 21, 2016
Let's say I have a table called messages with the columns:

id | from_id | to_id | subject | message | timestamp


I want to get the latest message from each user only, like you would see in your FaceBook inbox before you drill down into the actual thread.

This query seems to get me close to the result I need:

SELECT * FROM messages GROUP BY from_id


However the query is giving me the oldest message from each user and not the newest.

I can't figure this one out.Read more
Resolved: Get data from table from a list of strings on MySQL
posted by admin on May 6, 2016
How can I query data from a table available on a list of strings?

I only want to get the data from the list of strings.

Example:
Table
ID Name
1 Big
2 Small
3 Extra
4 Orange
5 Drink



List of Strings:
Big
Small
Extra
Read more
Resolved: MySQL IN for all values
posted by admin on May 6, 2016
I have MySQL query, which contain IN clause

select ItemID
from ItemCategory
where CategoryID in (5,6,7,8)
group by ItemID


I need it to work like an AND across multiple rows.Read more