Posts Tagged with mysql joins

Displaying 1-3 of 3 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
Resolved: MySQL get most popular tags ids combination with their counts
posted by admin on April 18, 2016
I have three tables
1.tbl_tag

id int NOT NULL AI PK
name VARCHAR(50) NOT NULL

id name
1 lifestyle
2 store
3 car
4 luxury
5 classy
6 boy
7 girl
8 man
9 woman
10 strong
etc ...


2.tbl_media

id int NOT NULL AI PK
name VARCHAR(50) NOT NULL

id name
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
etc...


3. tbl_media_tag

id int NOT NULL AI PK
media_id int NOT NULL -> relationship with tbl_media
tag_id int NOT NULL -> relationship with tbl_tag

id media_id tag_id
1 1 4
2 1 3
3 4 3
4 5 2
5 6 8
6 4 4
7 8 7
8 7 4
9 7 3
10 6 2
11 8 4
12 8 3
etc...


I want to get most popular tags(tag_id) combinations with LIMIT, for example most popular top ten tags combination

I want to get result like this

4, 7, 3 (75)
4, 8 (42)
1, 7, 9, 10 (28)
7, 8, 9 (10)
7, 2 (2)
Read more