MySQL - spajanje dva reda ako imaju isti datum

Programiranje
1

Pozdrav,

ovdje je moj upit.

SELECT t1.id,
(SELECT CONCAT (MONTHNAME(t1.date),’ ‘,YEAR(t1.date),’.’)) AS date,
(SELECT CONCAT(t2.name,’ ‘, t2.surname) FROM tc_players AS t2 WHERE t2.id = t1.player_id AND t3.id = 1) AS round_robin,
(SELECT CONCAT(t2.name,’ ‘, t2.surname) FROM tc_players AS t2 WHERE t2.id = t1.player_id AND t3.id = 2) AS masters,
(SELECT CONCAT(t2.name,’ ', t2.surname) FROM tc_players AS t2 WHERE t2.id = t1.player_id AND t3.id = 3) AS utjesni_turnir
FROM tc_halloffame AS t1
JOIN tc_players AS t2 ON t2.id = t1.player_id
JOIN tc_halloffame_title AS t3 ON t3.id = t1.hof_id
ORDER BY t1.date DESC

Dakle, moj cilj je spojiti 2 reda ako im je isti mjesec i godina. Probala sam i DISTINCT i GROUP BY no dobivam samo jedan rezultat.

Npr. listopad 2016., ako ga osoba1 i osoba2 imaju da ih spoji pod taj datum, a ne da budu svatko u svom redu. Kako to mogu izvesti?

2

Napiši primjer zapisa u tablicama, kakav rezultat dobijaš i kakav rezultat bi željela dobiti.

3

ovo žuto je trenutno stanje,
a ja bih htjela dobiti zelenu tablicu…

4

Kakva je struktura tablica tc_halloffame i tc_halloffame_title i u kakvoj su relaciji?

5

ovo što sam napisala gore zapravo je view… a ovo je tablica na kojoj se temelji

CREATE TABLE IF NOT EXISTS tc_halloffame (
id int(11) NOT NULL AUTO_INCREMENT,
date DATE NOT NULL,
player_id int(11) NOT NULL,
hof_id int(11) NOT NULL,
FOREIGN KEY (player_id) REFERENCES tc_players (id),
FOREIGN KEY (hof_id) REFERENCES tc_halloffame_title (id),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

6

Nisi napisala strukturu tablice tc_halloffame_title i daj napiši primjer par zapisa u svakoj tablici.
Bez strukture tablica i primjera zapisa slogova se ne mogu rješavati ovakvi problemi.

7

CREATE TABLE IF NOT EXISTS tc_halloffame_title (
id int(11) NOT NULL AUTO_INCREMENT,
hof_title varchar(100) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

primjer zapisa je ono iz tablice prije… nemam dio sa INSERTOM…
samo datumi imena treba upisati…

a ovaj tablica title… je Round robin, Masters, Utješni turnir…

jel to ok ili trebam još nešto dati?

8

OK, mislim da sam pohvatao konce…

9 
SELECT 

	CONCAT (MONTHNAME(hof.date),' ',YEAR(hof.date),'.') AS datum,
	(SELECT CONCAT(p1.name,' ', p1.surname) FROM tc_halloffame AS hof1, tc_players AS p1 WHERE hof1.id = t1.player_id AND hof1.id = 1 AND CONCAT (MONTHNAME(hof1.date),' ',YEAR(hof1.date),'.')) = CONCAT (MONTHNAME(hof.date),' ',YEAR(hof.date),'.'))) AS round_robin,
	(SELECT CONCAT(p2.name,' ', p2.surname) FROM tc_halloffame AS hof2, tc_players AS p2 WHERE hof2.id = t2.player_id AND hof2.id = 2 AND CONCAT (MONTHNAME(hof2.date),' ',YEAR(hof2.date),'.')) = CONCAT (MONTHNAME(hof.date),' ',YEAR(hof.date),'.'))) AS masters,
	(SELECT CONCAT(p3.name,' ', p3.surname) FROM tc_halloffame AS hof3, tc_players AS p3 WHERE hof3.id = t3.player_id AND hof3.id = 3 AND CONCAT (MONTHNAME(hof3.date),' ',YEAR(hof3.date),'.')) = CONCAT (MONTHNAME(hof.date),' ',YEAR(hof.date),'.'))) AS utjesni_turnir
FROM tc_halloffame AS hof
GROUP BY CONCAT (MONTHNAME(hof.date),' ',YEAR(hof.date),'.')
ORDER BY 1 DESC
10

Pokušala sam… javlja ovu grešku:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)) AS round_robin, (SELECT CONCAT(p2.name,’ ', p2.surname) FROM tc_halloffame ’ at line 3

pokušavam skužiti što ne valja i nikako da skužim… jel možda ti vidiš?

11

Probaj

SELECT
CONCAT (MONTHNAME(t1.date),’ ‘,YEAR(t1.date),’.’) AS datum,
(SELECT CONCAT(p1.name,’ ‘, p1.surname) FROM tc_halloffame AS hof1, tc_players AS p1 WHERE hof1.id = t1.player_id AND hof1.id = 1 AND CONCAT (MONTHNAME(hof1.date),’ ‘,YEAR(hof1.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS round_robin,
(SELECT CONCAT(p2.name,’ ‘, p2.surname) FROM tc_halloffame AS hof2, tc_players AS p2 WHERE hof2.id = t2.player_id AND hof2.id = 2 AND CONCAT (MONTHNAME(hof2.date),’ ‘,YEAR(hof2.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS masters,
(SELECT CONCAT(p3.name,’ ‘, p3.surname) FROM tc_halloffame AS hof3, tc_players AS p3 WHERE hof3.id = t3.player_id AND hof3.id = 3 AND CONCAT (MONTHNAME(hof3.date),’ ‘,YEAR(hof3.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS utjesni_turnir
FROM tc_halloffame AS hof
GROUP BY CONCAT (MONTHNAME(t1.date),’ ‘,YEAR(t1.date),’.’)
ORDER BY 1 DESC

12

#1054 - Unknown column ‘t1.date’ in ‘field list’

Hm hm… idem pogledati zašto

13

Četvrta sreća

SELECT
CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’) AS datum,
(SELECT CONCAT(p1.name,’ ‘, p1.surname) FROM tc_halloffame AS hof1, tc_players AS p1 WHERE hof1.id = t1.player_id AND hof1.id = 1 AND CONCAT (MONTHNAME(hof1.date),’ ‘,YEAR(hof1.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS round_robin,
(SELECT CONCAT(p2.name,’ ‘, p2.surname) FROM tc_halloffame AS hof2, tc_players AS p2 WHERE hof2.id = t2.player_id AND hof2.id = 2 AND CONCAT (MONTHNAME(hof2.date),’ ‘,YEAR(hof2.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS masters,
(SELECT CONCAT(p3.name,’ ‘, p3.surname) FROM tc_halloffame AS hof3, tc_players AS p3 WHERE hof3.id = t3.player_id AND hof3.id = 3 AND CONCAT (MONTHNAME(hof3.date),’ ‘,YEAR(hof3.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS utjesni_turnir
FROM tc_halloffame AS hof
GROUP BY CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)
ORDER BY 1 DESC

14

hehe… nažalost ništa…
sad je:
#1054 - Unknown column ‘t1.player_id’ in ‘where clause’

ne treba to join sa tablicom tc_players kako sam ja napravi (prvi view)?

15

Sada bi trebalo biti ok

SELECT
CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’) AS datum,
(SELECT CONCAT(p1.name,’ ‘, p1.surname) FROM tc_halloffame AS hof1, tc_players AS p1 WHERE hof1.id = p1.player_id AND hof1.id = 1 AND CONCAT (MONTHNAME(hof1.date),’ ‘,YEAR(hof1.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS round_robin,
(SELECT CONCAT(p2.name,’ ‘, p2.surname) FROM tc_halloffame AS hof2, tc_players AS p2 WHERE hof2.id = p2.player_id AND hof2.id = 2 AND CONCAT (MONTHNAME(hof2.date),’ ‘,YEAR(hof2.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS masters,
(SELECT CONCAT(p3.name,’ ‘, p3.surname) FROM tc_halloffame AS hof3, tc_players AS p3 WHERE hof3.id = p3.player_id AND hof3.id = 3 AND CONCAT (MONTHNAME(hof3.date),’ ‘,YEAR(hof3.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS utjesni_turnir
FROM tc_halloffame AS hof
GROUP BY CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)
ORDER BY 1 DESC

16

Opet isti error #1054 - Unknown column ‘p1.player_id’ in ‘where clause’

17

Točno, napravio sam grešku, zamjenio sam tablice.
Ako nisi izgubila nadu, probaj ovo :slight_smile:

SELECT
CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’) AS datum,
(SELECT CONCAT(p1.name,’ ‘, p1.surname) FROM tc_halloffame AS hof1, tc_players AS p1 WHERE hof1.player_id = p1.id AND hof1.hof_id = 1 AND CONCAT (MONTHNAME(hof1.date),’ ‘,YEAR(hof1.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS round_robin,
(SELECT CONCAT(p2.name,’ ‘, p2.surname) FROM tc_halloffame AS hof2, tc_players AS p2 WHERE hof2.player_id = p2.id AND hof2.hof_id = 2 AND CONCAT (MONTHNAME(hof2.date),’ ‘,YEAR(hof2.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS masters,
(SELECT CONCAT(p3.name,’ ‘, p3.surname) FROM tc_halloffame AS hof3, tc_players AS p3 WHERE hof3.player_id = p3.id AND hof3.hof_id = 3 AND CONCAT (MONTHNAME(hof3.date),’ ‘,YEAR(hof3.date),’.’) = CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)) AS utjesni_turnir
FROM tc_halloffame AS hof
GROUP BY CONCAT (MONTHNAME(hof.date),’ ‘,YEAR(hof.date),’.’)
ORDER BY 1 DESC

18

Jesi li barem uhvatila koncept mojeg upita?

19

Loš dizajn baze.