Cześć wałczę z zapytanie do mojej bazy. Mianowicie posiadam dwie tabele wyjazdy i umowy.
Potrzebuje aby zapytanie zwracało wszystkie umowy w których wartość kolumny 'data_wyjazdu' jest ta sama co w tabeli wyjazdy, gdzie kazdy wyjazd ma 4 dni, więc posiada kolumny data, data1,data2,data3.
Tłumacząc na drugi sposób, chce aby pokazywały się wszystkie umowy które zostały spisane w danym wyjeździe, który trwał 4 dni. stąd "BETWEEN data and data3
Zrobiłem coś takiego lecz to pokazuje umowy niekoniecznie związane z danym wyjazdem
SELECT * FROM umowy p join wyjazdy t on(p.prelegent=t.prelegent) WHERE p.data_wyjazdu BETWEEN t.data AND t.data3
A tutaj struktury moich tabel
CREATE TABLE `umowy` (
`id` int(11) NOT NULL,
`data_wyjazdu` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`kod` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`miejscowosc` varchar(11) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`ulica` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`telefon` varchar(222) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`stat` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'NULL',
`forma` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'NULL',
`rodzaj` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'NULL',
`kwota` int(222) DEFAULT NULL,
`wplata` int(255) DEFAULT NULL,
`porat` int(222) DEFAULT NULL,
`prelegent` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '''NULL''',
`asystent` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '''NULL''',
`kierowca` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '''NULL''',
`mata` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`pas` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`system` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`practi` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`gold` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`silver` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`tablet` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`lozko` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`multi` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`turbo` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`wyciskarka` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`noz` varchar(100) COLLATE utf8_polish_ci DEFAULT NULL,
`query` longtext CHARACTER SET utf8 COLLATE utf8_bin,
`1` int(11) NOT NULL,
`2` int(11) NOT NULL,
`3` int(11) NOT NULL,
`4` int(11) NOT NULL,
`5` int(11) NOT NULL,
`data_rat` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL,
`data_rat1` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL,
`data_rat2` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL,
`data_rat3` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL,
`data_rat4` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL,
`pozostalo` int(11) NOT NULL,
`uwaga_rata` longtext COLLATE utf8_polish_ci NOT NULL,
`wplata_rodzaj` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`nr_rachunku` int(50) DEFAULT NULL,
`tp` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL
CREATE TABLE `wyjazdy` (
`id` int(11) NOT NULL,
`prelegent` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`asystent` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`asystent1` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`asystent2` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`asystent3` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`asystent4` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`kierowca` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`rodzaj_wyjazdu` varchar(100) DEFAULT NULL,
`miejscowosc` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`miejscowosc1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`miejscowosc2` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`miejscowosc3` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`data` varchar(10) DEFAULT NULL,
`data1` varchar(10) DEFAULT NULL,
`data2` varchar(10) DEFAULT NULL,
`data3` varchar(10) DEFAULT NULL,
`hotel` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`hotel1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`hotel2` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`hotel3` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL