MySQL - spajanje dva reda ako imaju isti datum

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?

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

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

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

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;

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.

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?

OK, mislim da sam pohvatao konce…

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

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š?

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

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

Hm hm… idem pogledati zašto

Č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

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)?

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

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

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

Jesi li barem uhvatila koncept mojeg upita?

Loš dizajn baze.


Copyright © 2022 WM Forum - AboutContact