zapytanie do kilku tabel z pozostawieniem wartości NULL, SQL Opcje

[n1edw1ed]

Witam,

Bardzo proszę o pomoc w stworzeniu zapytania SQL mając poniższe dane:

Kod

tabela1
id_p  | nazwa  |
1     | tab1a  |
2     | tab1b  |
3     | tab1c  |
4     | tab1d  |
5     | tab1e  |

Kod

tabela2
id | id_p  | id_u  | data       |
1  | 1     | 1     | 2013-05-01 |
2  | 2     | 1     | 2013-05-02 |
3  | 1     | 2     | 2013-05-03 |
4  | 3     | 4     | 2013-05-04 |
5  | 3     | 7     | 2013-05-05 |
6  | 4     | 1     | 2013-05-06 |

znana jest zmienna id_u = 1

tablica wynikowa ma mieć postać (gdy id_u = 1)

Kod

id_p  | nazwa  | data
1     | tab1a  | 2013-05-01
2     | tab1b  | 2013-05-02
3     | tab1c  | NULL
4     | tab1d  | 2013-05-06
5     | tab1e  | NULL

Siedzę już nad tym pół dnia i nie potrafię nic konstruktywnego wymyślić.

Do tej pory doszedłem do takiego zapytania:
SELECT
tab1.id_p, tab1.nazwa AS naz,
tab2.id, tab2.id_p, tab2.id_u AS osoba, tab2.data AS data
FROM tab1 left join tab2 on tab1.id_p = tab2.id_p"

ale takie zapytanie dawało mi niepoprawny wynik, gdyż wyświetlało wszystkie wartości z tab2.

Następnie dodałem do zapytania "WHERE tab2.id_u = 2"
ale to zapytanie nie wyświetla rekordów NULL - zresztą słusznie...

Myślałem, aby pobrać z bazy całą tab1, następnie całą tabelę 2 i porównując za pomocą IF wyświetlać to co powinno być wyświetlane.

Lecz podejrzewam, że da się to zrobić jednym zapytaniem SQL do bazy, więc proszę o pomoc. Ja się już (przynajmniej na tą chwilę) poddaję.


Dziękuję za poświęcony czas


PS.
Jeżeli jest to niemożliwe, lub są jakieś duże przeciwwskazania (nie wiem jakie) aby takie zapytanie działało, to również proszę o taką odpowiedź.

[mmmmmmm]

[SQL] pobierz, plaintext 
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.id_p=tab2.id_p AND tab2.id_u=1
[SQL] pobierz, plaintext 

Warunek w JOIN, nie we WHERE

Ten post edytował mmmmmmm 16.06.2013, 17:41:50

[n1edw1ed]

Bardzo dziękuję

Działa idealnie.

Witam ponownie,

Postanowiłem odgrzebać ten topić, aby nie zaśmiecać kolejnego, gdyż problem dotyczy praktycznie tego samego.

Mam 3 tabele:

CODE

-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Czas wygenerowania: 25 Lip 2013, 16:27
-- Wersja serwera: 5.5.24-log
-- Wersja PHP: 5.4.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Baza danych: `test`
--

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `u`
--

CREATE TABLE IF NOT EXISTS `u` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`g_id` mediumint(8) unsigned NOT NULL DEFAULT '3',
`un` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=135 ;

--
-- Zrzut danych tabeli `u`
--

INSERT INTO `u` (`id`, `g_id`, `un`) VALUES
(1, 1, 'Aaa'),
(2, 8, 'aab'),
(54, 8, 'aac'),
(55, 8, 'aad'),
(56, 8, 'aae'),
(57, 8, 'aaf'),
(77, 9, 'aag'),
(127, 9, 'aah'),
(58, 9, 'aai'),
(59, 13, 'aaj'),
(60, 9, 'aak'),
(61, 9, 'aal'),
(62, 2, 'aam'),
(63, 9, 'aan'),
(64, 9, 'aao'),
(65, 2, 'aap'),
(73, 8, 'aar'),
(95, 9, 'aas'),
(79, 9, 'aat'),
(66, 8, 'aau'),
(67, 9, 'aaw'),
(72, 9, 'aay'),
(68, 13, 'aaz'),
(69, 9, 'bba'),
(70, 9, 'bbb'),
(71, 10, 'bbc'),
(74, 9, 'bbd'),
(75, 9, 'bbe'),
(76, 10, 'bbf'),
(91, 9, 'bbg'),
(78, 9, 'bbh'),
(80, 9, 'bbi'),
(81, 13, 'bbj'),
(82, 9, 'bbk'),
(86, 13, 'bbl'),
(83, 9, 'bbm'),
(84, 9, 'bbn'),
(88, 13, 'bbo'),
(87, 9, 'bbp'),
(85, 9, 'bbr'),
(98, 2, 'bbs'),
(99, 10, 'bbt'),
(94, 13, 'bbw'),
(89, 10, 'bby'),
(90, 9, 'bbz'),
(92, 9, 'cca'),
(93, 9, 'ccb'),
(96, 9, 'ccd'),
(97, 10, 'cce');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Druga tabela:

CODE

-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Czas wygenerowania: 25 Lip 2013, 16:27
-- Wersja serwera: 5.5.24-log
-- Wersja PHP: 5.4.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Baza danych: `test`
--

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `w`
--

CREATE TABLE IF NOT EXISTS `w` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`id_z` mediumint(8) unsigned NOT NULL,
`id_u` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `id_zadania` (`id_z`,`id_u`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=332 ;

--
-- Zrzut danych tabeli `w`
--

INSERT INTO `w` (`id`, `id_z`, `id_u`) VALUES
(2, 1, 58),
(4, 1, 67),
(5, 1, 83),
(7, 1, 66),
(8, 1, 82),
(9, 1, 63),
(10, 1, 2),
(11, 1, 74),
(14, 1, 92),
(15, 1, 60),
(18, 1, 90),
(33, 1, 85),
(36, 3, 82),
(47, 3, 92),
(48, 3, 2),
(49, 3, 70),
(56, 3, 79),
(73, 3, 74),
(74, 3, 56),
(75, 3, 58),
(76, 3, 67),
(77, 3, 63),
(78, 3, 73),
(83, 3, 72),
(85, 3, 87),
(86, 3, 97),
(92, 3, 55),
(93, 3, 93),
(97, 3, 71);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Trzecia tabela:

CODE

-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Czas wygenerowania: 25 Lip 2013, 16:28
-- Wersja serwera: 5.5.24-log
-- Wersja PHP: 5.4.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Baza danych: `test`
--

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `z`
--

CREATE TABLE IF NOT EXISTS `z` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`nz` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Zrzut danych tabeli `z`
--

INSERT INTO `z` (`id`, `nz`) VALUES
(1, 'z1'),
(3, 'z3');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

oraz zapytanie, które w końcu udało mi się sklecić, troszkę metodą prób i błędów...

[SQL] pobierz, plaintext 
SELECT u.id, u.un, u.g_id, w.id_z
FROM u
LEFT JOIN w
ON u.id = w.id_u AND w.id_z='1'
WHERE u.g_id=8 OR u.g_id=9 OR u.g_id=10
ORDER BY u.un
[SQL] pobierz, plaintext 

Celem zapytanie było pokazanie wszystkich z tabeli "u" (nazw użytkowników - pole u.un) oraz powiązanych danych z tabeli "w" i "z" - razem z wartościami NULL w zależności od u.g_id (id grupy, w której znajduje się user) oraz w.id_z (np "1"). Wszystko działa jak należy.

Mam jeszcze dwa problemy, z którymi muszę sobie poradzić i szczerze - nie idzie mi to, choć próbuję.

Po pierwsze potrzebuję zrobić zapytanie, które pokaże (tak jak zapytanie powyższe) wszystkich użytkowników z tabeli "u" (ich nazwy) dla których nie ma dopasowania wg w.id = np 1 - same wartości NULL.
Innymi słowy wynik powinien wyglądać tak:
id użytkownika, nazwa użytkownika, grupa w której jest użytkownik, wartość dopasowania z tabelą "w" - wartość NULL (brak dopasowania). - np dla wartości w.id_z równej "1" powinno znaleźć wszystkich użytkowników, dla których nie ma dopasowania w.id_z równego "1". Albo dla wartości w.id_z równej "3" wszystkich użytkowników dla których nie ma dopasowania w.id_z = 3.

Mam nadzieję, że nie zagmatwałem za bardzo - w skrócie - wynikiem powinno być to, co w daje wynik powyższego zapytania - z tym, że tylko same wartości NULL.


I drugi problem - dużo gorszy.
Mam kilka koncepcji, na jego rozwiązanie, ale może od początku...
Normalnie tabela "u" zawiera około 100 użytkowników będących w tych trzech grupach, tabela "w" zawiera około 70 wpisów dla każdego id_z - a samych id_z przybywa około 6,7 na tydzień.

W skrócie potrzebuję sprawdzić np ostatnie 5, 10, 20 id_z - znaleźć użytkowników dla których nie ma dopasowania w tabeli "w" dla tych ostatnich id_z (id z tabeli z).

Myślałem aby zrobić to na dwa sposoby.
Pomysł pierwszy:
Zrobić kilka zapytań:
1. zapytanie, które pobierze wszystkich użytkowników
2. zapytanie, które pobierze ostatnie 5, 10, 20 id z tabeli "z"
3. pętla, która przejdzie przez wszystkie id i... pobierze przez zapytanie wszystkie wpisy z tabeli "w" dla każdego id (z tabeli "z") i wstawi do tablicy tymczasowej (tylko 5, 10 lub 20 zapytań)
4. pętla, która przejdzie przez wszystkich użytkowników i będzie sprawdzać, czy w tablicy tymczasowej znajduje się dane id_z przy danych u.id - niestety nie wiem jak to zrobić (jak sprawdzić czy te dwie dane znajdują się w jednej "podtablicy" - jeżeli mogę się tak nieprofesjonalnie wyrazić)

Pomysł pierwszy upadł właśnie na tym przeszukiwaniu.

Pomysł drugi,
1. zapytanie, które pobierze wszystkich użytkowników
2. zapytanie, które pobierze ostatnie 5, 10, 20 id (z tabeli z)
3. pętla, która przejdzie przez wszystkich użytkowników
4. w niej pętla która będzie przechodzić przez wszystkie id z tabeli z
5. w niej zapytanie, które sprawdzi, czy istnieje w bazie powiązanie użytkownika z id (z tabeli z) w tabeli "w".

To rozwiązanie niesie za sobą niestety bardzo dużo zapytań, np przy sprawdzaniu ostatnich 20 id (z tabeli "z").

Pomysł trzeci:
jedno zapytanie, które wyciągnie z bazy te braki powiązań dla ostatnich 5, 10, 20 id z tabeli z.
Niestety nie potrafię sobie poradzić nawet ze sprawdzaniem jednego id z...


Niestety proszę o pomoc w poradzeniu sobie z tym problemem oraz doradzenie najlepszej opcji - takiej, która będzie najmniej obciążać serwer...

Ogólnie potrzebuję wyświetlić na stronce wynik, którym będzie brak dopasowania dla ostatnich id (z tabeli z) do tabeli "u" w tabeli "w".

Na przykład
NU | Id_z5| id_z4| id_z3| id_z2| id_z1 |
user1 | NULL | | | | NULL |
user3 | | NULL | NULL | | |
user4 | | NULL | | | |
user7 | NULL | NULL | NULL | | NULL |
wystarczy brak dopasowania dla jednego id (z tabeli "z") dla danego użytkownika, aby znalazł się on w tej tabeli.
użytkownicy, dla których znajdują się dopasowania dla wszystkich sprawdzanych id (z tabeli "z") (np 5 ostatnich) nie powinni się znaleźć w tej tabeli.

Wybaczcie, że się tak rozpisałem... być może przez to bardziej zagmatwałem problemy nad którymi siedzę już od ponad tygodnia, no ale chciałem, aby było najczytelniejsze... (IMG:style_emoticons/default/smile.gif)

Z góry dziękuję za pomoc.

PS

Jeżeli stworzenie któregoś z w/w zapytań nie jest możliwe, to bardzo proszę o taką informację, gdyż tydzień już minął i nikt nie zechciał pomóc...

[mmmmmmm]

Jak dla mnie za dużo treści - ciężko odnaleźć zdefiniowany problem... Ale się postaram.

Po pierwsze potrzebuję zrobić zapytanie, które pokaże (tak jak zapytanie powyższe) wszystkich użytkowników z tabeli "u" (ich nazwy) dla których nie ma dopasowania wg w.id = np 1 - same wartości NULL.

[SQL] pobierz, plaintext 
SELECT u.* FROM u LEFT JOIN w ON u.id=w.id_u WHERE w.id_u IS NULL
[SQL] pobierz, plaintext 

W skrócie potrzebuję sprawdzić np ostatnie 5, 10, 20 id_z - znaleźć użytkowników dla których nie ma dopasowania w tabeli "w" dla tych ostatnich id_z (id z tabeli z).

[SQL] pobierz, plaintext 
SELECT u.* FROM u LEFT JOIN (SELECT * FROM z LIMIT 20) x ON x.id_z=u.id_z WHERE x.id_z IS NULL LIMIT 20
[SQL] pobierz, plaintext 

To jest pewnie źle, ale idea pozostaje taka sama.

[n1edw1ed]

Dziękuję za zainteresowanie się moim tematem.

[SQL] pobierz, plaintext 
SELECT u.* FROM u LEFT JOIN w ON u.id=w.id_u WHERE w.id_u IS NULL
[SQL] pobierz, plaintext 

Niestety to zapytanie nie rozwiązuje problemu, gdyż pokaże ono wszystkich użytkowników, dla których nie ma dopasowania w tabeli "w".
Nie potrafię natomiast stworzyć zapytania, które, tak jak powyższe, pokaże wszystkich userów dla których nie ma dopasowania w tabeli "w" ale dla w.id_z = np 1, albo 2, albo...

Ogólnie stronka jest powiązania z forum (tabela "u"), każdy użytkownik musi wykonywać pewne rzeczy, które są odzwierciedlone w tabeli "w", a same konkretne czynności do wykonania w tabeli "z".
W zapytaniu chodzi o to, aby sprawdzi, który z użytkowników nie wykonał danej konkretnej czynności (z tabeli "z") - nie ma takiego wpisu w tabeli "w".

[SQL] pobierz, plaintext 
SELECT u.* FROM u LEFT JOIN (SELECT * FROM z LIMIT 20) x ON x.id_z=u.id_z WHERE x.id_z IS NULL LIMIT 20
[SQL] pobierz, plaintext 

To jest pewnie źle, ale idea pozostaje taka sama.

Szczerze, nie potrafię aż tak dobrze czytać zapytania, w którym jest zagnieżdżone inne zapytanie, więc niestety nie wiem, jaka jest idea tego zapytania. A niestety prześledzić jego wykonywania też nie jestem w stanie, gdyż po prostu nie działa.

To zapytanie natomiast ma pokazać, jak użytkownicy wykonali (a raczej jak nie wykonywali) ostatnich 5, 10, 20 czynności...

[mmmmmmm]

1.

[SQL] pobierz, plaintext 
SELECT u.* FROM u LEFT JOIN w ON u.id=w.id_u
AND w.id_z=1
WHERE w.id_u IS NULL
[SQL] pobierz, plaintext 

2. nie mam czasu na tłumaczenia. Idea jest taka, że nie bierzesz jako źródło danych całej tabeli (z), tylko np ostatnie 20 rekordow (SELECT * FROM z ORDER BY id DESC LIMIT 20)

[n1edw1ed]

Zapytanie 1 działa idealnie - dodałem tylko sprawdzanie grup.

Ideę zapytania nr 2 rozumiem, nie potrafię jej jednak przenieść na samo zapytanie.
Jeżeli pobieramy dane z tabeli "u" i porównujemy je z tabelą "w", to gdzie tam jest miejsce na tabelę "z" ?

Czytam właśnie o podzapytaniach (skierowany Twoim drugim zapytaniem) i gdy próbuję np coś takiego:

[SQL] pobierz, plaintext 
SELECT * FROM w
WHERE id_z IN (SELECT id FROM z ORDER BY id DESC LIMIT 20)
[SQL] pobierz, plaintext 

wyskakuje mi komunikat
"#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"
Pewnie robię coś źle w tym zapytaniu, no ale... uczę się... i testuję...

Tak czy inaczej, wiem, że wymagam dość sporo, jednak nie przebrnę przez to zapytanie - mój umysł po prostu, póki co, tego nie ogarnia. (IMG:style_emoticons/default/sad.gif)
Bardzo proszę więc o podanie treści również tego (drugiego) zapytania.

[mmmmmmm]

Staraj się nie stosować IN na MySQLu. Dużo wydajniesze są JOINy. CHoć JOINy z podzapytaniami na MySQL to również masakra. Ale tam chociaż nie ma ograniczenia co do LIMIT.
Mniej więcej tak:

[SQL] pobierz, plaintext 
SELECT * FROM w LEFT JOIN (SELECT id FROM z ORDER BY id DESC LIMIT 20) x ON x.id=w.id_z
[SQL] pobierz, plaintext 

[n1edw1ed]

Staraj się nie stosować IN na MySQLu. Dużo wydajniesze są JOINy. CHoć JOINy z podzapytaniami na MySQL to również masakra. Ale tam chociaż nie ma ograniczenia co do LIMIT.
Mniej więcej tak:

[SQL] pobierz, plaintext 
SELECT * FROM w LEFT JOIN (SELECT id FROM z ORDER BY id DESC LIMIT 20) x ON x.id=w.id_z
[SQL] pobierz, plaintext 

Jeszcze raz dziękuję za chęć pomocy.

Rozumiem ideę tego zapytania...
Pobieramy wszystko z tabeli "w", robimy złączenie z tabelą wirtualną "x", która jest wynikiem limitującym ilość id zadań.
Jednak nie działa ono prawidłowo, gdyż jak limituję np do jednej wartości id, to i tak pokazuje z kilku z.id


Wymodziłem po wielu próbach coś takiego:

[SQL] pobierz, plaintext 
SELECT * FROM u
LEFT JOIN (SELECT id AS w_id, id_z AS w_id_z, id_u AS w_id_u FROM w 
INNER JOIN (SELECT id AS z_id FROM z ORDER BY z_id DESC LIMIT 5) x ON x.z_id=w.id_z) y
ON u.id = y.w_id_u
WHERE u.g_id = 8 OR u.g_id = 9 OR u.g_id = 10
ORDER BY u.id
[SQL] pobierz, plaintext 

To zapytanie limituje już poprawnie, lecz nie do końca działa tak jak bym chciał...
Mianowicie pokazuje wszystkich użytkowników oraz ich powiązania z tabelą "w" wg limitu, a jeżeli nie ma takiego powiązania dla danego limitu pokazuje NULL.

Jednak powinno ono działać tak: limit = 5
uzytkownik1 - id. zadania 5 = ok
uzytkownik1 - id. zadania 4 = ok
uzytkownik1 - id. zadania 3 = ok
uzytkownik1 - id. zadania 2 = NULL
uzytkownik1 - id. zadania 1 = ok
uzytkownik2 - id. zadania 5 = ok
uzytkownik2 - id. zadania 4 = ok
uzytkownik2 - id. zadania 3 = NULL
uzytkownik2 - id. zadania 2 = NULL
uzytkownik2 - id. zadania 1 = ok
uzytkownik3 - id. zadania 5 = NULL
uzytkownik3 - id. zadania 4 = NULL
uzytkownik3 - id. zadania 3 = NULL
uzytkownik3 - id. zadania 2 = ok
uzytkownik3 - id. zadania 1 = ok
etc
wówczas przechodząc pętlą przez ten wynik mógłbym ładnie wyświetlić wykonywanie tych czynności dla danego limitu...

ehhhh, wymięknę przy tym... :/