Mam 2 tabele w bazie:

Wiadomosci:
id_w
od_kogo
do_kogo
tekst

Userzy:
id_u
nazwa

Kiedy user wysyła wiadomość (np. user o nazwie AAA i id=1 do usera BBB o id=2) to w tabeli wiadomości zostanie dodany rekord o wartości pól: od_kogo=1 i do_kogo=2:

[SQL] pobierz, plaintext 
INSERT INTO wiadomosci VALUES( NULL , '1' , '2' , 'tekst' )
[SQL] pobierz, plaintext 

Moje pytanie brzmi jak wyświetlić te wiadomości tak aby zamiast id userów pojawiły się ich nazwy. Normalnie nie miałem z tym problemów bo robiłem to tak:

[SQL] pobierz, plaintext 
SELECT userzy.nazwa , wiadomosci.tekst FROM wiadomosci , userzy WHERE userzy.id = wiadomosci.od_kogo
[SQL] pobierz, plaintext 

Ale tutaj jest problem bo przecież pola od_kogo i do_kogo oba są wartościami id z tabeli userzy. Jak skonsrtuować zapytanie aby SQL nie zgłupiał?

Moja propozycja rozwiązania twojego problemu:

[SQL] pobierz, plaintext 
SELECT  
w.tekst,
( SELECT nazwa FROM  userzy WHERE id_u = w.od_kogo  ) AS od_kogo_nazwa,
( SELECT nazwa FROM  userzy WHERE id_u = w.do_kogo  ) AS do_kogo_nazwa
FROM wiadomosci AS w 
[SQL] pobierz, plaintext 

LEFT JOIN

[SQL] pobierz, plaintext 
SELECT w.id_w, u1.nazwa AS od, u2.nazwa AS do, w.tekst FROM wiadomosci w LEFT JOIN userzy u1 ON w.od_kogo = u1.u_id LEFT JOIN userzy u2 ON w.do_kogo = u2.u_id ORDER BY w.id_w DESC
[SQL] pobierz, plaintext 

To wyświetli Ci wszystkie wiadomości w bazie od najnowszej do najstarszej. Ale jeśli chcesz znać wszystkie kolumny z tabeli wiadomości to użyj w.* bo to poda też id userów, co może Ci się przydać gdy będziesz chciał podpiąć link z czymś co wiąże się z pisaniem odpowiedzi czy innych operacji wymagających posiadania id.

Post powyżej też daje odpowiedź, ale każde podzapytanie sprawi niepotrzebny narzut czasu. A teraz wyciągnij z bazy kilkadziesiąt choćby wiadomości, gdy do każdego wiersza wywołujesz 2 podzapytania. To staje się zwyczajnie nieoptymalne i czasochłonne.

Ale co to jest u1 i u2? Rozumiem ze user1 i user2 ale jak to ma SQL rozpoznac skoro nie odwoluje sie to do nazwy kolumny?

w, u1 i u2 to aliasy na nazwy tabel, czyli coś równoznaczne z pseudonimem. Jest to robione by unikać powtarzania długich nazw tabel lub by rozróżnić tabele, gdy dołączamy kilkukrotnie tę samą w jednym zapytaniu A ja w tym zapytaniu dwukrotnie dołączam tabelę userzy. Skąd więc mam wiedzieć bez aliasu z której z nich wyciągam jaką kolumnę? u1 jest więc równoznaczne w moim przypadku z tabelą userzy dołączaną pierwszym LEFT JOINem. Stąd u1.u_id jest niczym innym jak "pobierz kolumnę u_id z tabeli userzy dołączanej pierwszym LEFT JOINem. Używam nazwy kolumny JAWNIE, ale nazwę tabeli podaję jako alias. JOIN ma wiele możliwości: LEFT JOIN, RIGHT JOIN, INNER JOIN, JOIN, NATURAL JOIN, CROSS JOIN i każda jest dobra do konkretnych zastosowań. Poczytaj więcej dokumentacji lub tutoriali i helpów to będziesz wiedział co kiedy stosować Nieraz bowiem przy większych nieco bazach lub stronach mających wielu odwiedzających to robi kolosalną różnicę w czasie wykonania i samej stabilności strony. Bez optymalizacji error 503 będzie Twoim koszmarem
Rozpiszę Ci dokładnie "czytanie" tego selecta
Pobierz kolumny: id_wiadomości z tabeli wiadomości, nazwa z tabeli userzy o aliasie u1 dołączanej pierwszym join i nazwij ją OD, nazwa z tabeli userzy o aliasie u2 dołączanej drugim join i nazwij ją DO, tekst z tabeli wiadomosci,
Pobierz te dane z tabeli: wiadomosci o aliasie w, połączonej z userzy( o aliasie u1 ), za pomocą indeksów od_kogo z tabeli o aliasie w równego u_id z tabeli o aliasie u1, a do nich dołącz tabelę userzy o aliasie u2 za pomocą indeksu do_kogo z tabeli o aliasie w rownego u_id z tabeli o aliasie u2
Posortuj: wszystkie rekordy po id_w z tabeli o aliasie w, malejąco

Takie podejście mocno skraca długie zapytania i wpływa na czytelność kodu, gdyż wiadomo wtedy co się skąd bierze.

Pisząc:
w.w_id wiem, że to kolumna w_id z tabeli wiadomości bo sam ją przy joinie tak nazwałem
u1.nazwa wiem, że to kolumna nazwa z tabeli userzy dołączanej pierwszym LEFT JOIN bo sam ją przy joinie tak nazwałem
u2.nazwa wiem, że to kolumna nazwa z tabeli userzy dołączanej drugim LEFT JOIN bo sam ją przy joinie tak nazwałem

W zapytaniu jeszcze dla wygody kolumnę u1.nazwa nazywam sobie "od", przez co w php pisząc $row['od'] mam od razu login nadawcy wiadomości.

Ale tu już się kłaniają podstawy SQL, które są wspólne i standaryzowane, niezależnie czy używasz MySQL, MSSQL, Sybase czy Oracle bądź innej bazy danych.

Muszę przyznać, że naprawdę super mi to wytłumaczyłeś. Dzięki wielkie!! Masz podwójny 'Pomógł'

EDIT: Mam jescze tylko małe pytanie do tego. Tabela wiadomosci zawiera jeszcze kolumne 'przeczytane' i jesli user do ktorego byla wiadomosc przeczytal juz ja to wartosc = 1 jesli nie to 0. Ja chcialbym wyswietlic tylko te rekordy ktore sa nieprzeczytane i dotycza usera o nazwie $zalogowany. Więc zmodyfikowałem trochę Twoje zapytanie:

[SQL] pobierz, plaintext 
   1.
      SELECT w.id_w, u1.nazwa AS od, u2.nazwa AS do, w.tekst FROM wiadomosci w LEFT JOIN userzy u1 ON w.od_kogo = u1.u_id LEFT JOIN userzy u2 ON w.do_kogo = u2.u_id WHERE wiadomosci.przeczytane='0' ORDER BY w.id_w DESC
[SQL] pobierz, plaintext 

I wyswietla sie blad ze zle zapytanie:( co jest nie tak?

I jak zmodyfikować zapytanie aby wyświetlało tylko wiadomości skierowane do gostka o nazwie np. admin?

Kilkadziesiąt wiadomości to jest żadna ilość. Problemy się zaczynają przy większych liczbach. Podaj jakieś linki do artykułów o tym i do testów potwierdzających to co mówisz. Nie masz pojęcia o czym mówisz i zarzucasz mi niekompetencje Wykonałeś kiedyś w życiu jakieś testy bo ja tak w tej chwili i różnica jest żadna w postgresql. Zaras zamieszcze mój test
To co mówisz może być prawda tylko w odniesieniu do jakiejś bazy danej której to robi problem postgresql nią nie jest.

Twoje zapytanie w bazie 1000 wierszy:
Select Limit 10: 3 próby 63ms, 62ms, 47ms
Select Limit 100: 3 próby 219ms, 266ms, 266ms
Select Limit 1000: 3 próby 2234ms, 2235ms, 2203ms

Moje zapytanie w bazie 1000 wierszy:
Select Limit 10: 3 próby 63ms, 47ms, 31ms
Select Limit 100: 3 próby 234ms, 235ms, 250ms
Select Limit 1000: 3 próby 2282ms, 2281ms, 2202ms

Robiłem dla 10000 wierszy i 100000 z indeksami, bez indeksów, z sortowaniem, bez sortowania i razem różnice są żadne. Robiłem selecta z limitem 10, 100, 1000, 5000, 10000 i bez.

Nie wiadomości.przeczytane tylko w.przeczytane, bo aliasem jest w - nie wiadomości. Jeśli dodatkowo chcesz ograniczyć to zapytanie to wystarczy do WHERE dodać kolejny warunek. Jeśli chcesz sprawdzić jakie są nieprzeczytane wiadomości od usera X to dodajesz od LIKE '$user' lub w.od_kogo = $user_id (to da ten sam efekt), a jeśli chcesz sprawdzić jakich wiadomości user X nie przeczytał to walisz do LIKE '$user' lub w.do_kogo = '$user_id' (znów zamiennik).
Czyli ostatecznie masz w wariantach:
a) wiadomości nieprzeczytane wysłane przez użytkownika $user (gdy jako login) lub $user_id (gdy znamy tylko jego id)

[SQL] pobierz, plaintext 
SELECT w.id_w, u1.nazwa AS od, u2.nazwa AS do, w.tekst FROM wiadomosci w LEFT JOIN userzy u1 ON w.od_kogo = u1.u_id LEFT JOIN userzy u2 ON w.do_kogo = u2.u_id WHERE w.przeczytane = 0 AND od LIKE '.$user.' ORDER BY w.id_w DESC
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
 SELECT w.id_w, u1.nazwa AS od, u2.nazwa AS do, w.tekst FROM wiadomosci w LEFT JOIN userzy u1 ON w.od_kogo = u1.u_id LEFT JOIN userzy u2 ON w.do_kogo = u2.u_id WHERE w.przeczytane = 0 AND w.od_kogo = '.$user_id.' ORDER BY w.id_w DESC 
[SQL] pobierz, plaintext 

wiadomości nieprzeczytane przysłane dla użytkownika $user (gdy jako login) lub $user_id (gdy znamy tylko jego id)

[SQL] pobierz, plaintext 
 SELECT w.id_w, u1.nazwa AS od, u2.nazwa AS do, w.tekst FROM wiadomosci w LEFT JOIN userzy u1 ON w.od_kogo = u1.u_id LEFT JOIN userzy u2 ON w.do_kogo = u2.u_id WHERE w.przeczytane = 0 AND do LIKE '.$user.' ORDER BY w.id_w DESC 
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
 SELECT w.id_w, u1.nazwa AS od, u2.nazwa AS do, w.tekst FROM wiadomosci w LEFT JOIN userzy u1 ON w.do_kogo = u1.u_id LEFT JOIN userzy u2 ON w.do_kogo = u2.u_id WHERE w.przeczytane = 0 AND w.do_kogo = '.$user_id.' ORDER BY w.id_w DESC  
[SQL] pobierz, plaintext 

Oczywiście $user i $user_id są zmiennymi php, które musisz wpleść w składnię zapytania. Od, do, w.od_kogo, w.do_kogo i w.przeczytana można mieszać w WHERE tworząc odpowiednie układy. Zauważ, że używając od i do, musisz użyć pojedynczych apostrofów i LIKE, gdyż mamy do czynienia ze zmienną tekstową. Dawno nie robiłem porównań dla tekstówek więc od = '$user' może zadziałać (choć nie sądzę), ale musiałbyś sprawdzić. Na pewno przeszukiwanie po id będzie szybsze bo masz tam ustawione indeksy, więc drugie warianty powinny działać szybciej. LIKE jest wolniejszy, a tutaj dodatkowo kolumna na której działa (login) nie ma indeksu.

Co do pytania ostatniego to chyba już się wyjaśniło z kodem powyżej. W warunku dajesz

[SQL] pobierz, plaintext 
WHERE do LIKE 'admin'
[SQL] pobierz, plaintext 

Nie mam ochoty wywoływać flame'a. Tym bardziej, że nie znamy wielkości tabel zarówno wiadomości jak i userów oraz typu bazy, a chyba zauważyłeś, że podane zostały wybiórczo kolumny i całość jest bardziej rozbudowana. To także ma wpływ na szybkość skryptów naszych. To, że w zależności czy jest to Firebird, Oracle, Mysql, MSSQL czy Postgress czasy mogą być różne, jest dla mnie równie oczywiste. Zarówno jednak newsów jak i userów z każdym dniem przybywa. Nowa baza jest szybka, ale niech się zacznie zapełniać i stronę zacznie odwiedzać coraz więcej userów to wtedy takie drobiazgi mają wpływ i bez optymalizacji oraz cache'owania wyników najczęstszych zapytań daleko nie pociągnie się. Albo raczej zajedzie bazę ilością nadmiarowych operacji. Wiem, że wspomniana przez Ciebie jest szybsza w działaniu niż MySQL, ale też nie wszędzie jest ona dostępna. Dla faktycznych testów należałoby oba zapytania puścić w tych samych warunkach dla różnych stopni zapełnienia bazy danymi i dla różnych rodzajów baz. Ja pracuję w firmie, gdzie mam pod sobą kilka serwisów mających dziennie kilkadziesiąt tysięcy odsłon i około 2k real users według google analytics, czyli tych co nie blokują ich skryptów. Optymalizacja na takich ma znaczenie i dlatego staram się mieć jak najbardziej optymalne rozwiązania. Jeśli pracujesz na takich lub większych serwisach to zapewne mnie rozumiesz. Nie zaczynajmy więc niepotrzebnie flame'a i nie zaśmiecajmy niepotrzebną wojną tematu.
Moglibyśmy przecież się spierać czy gdy wiadomości jest X, userów w bazie Y, a odwiedzających jednocześnie Z to od pewnego momentu któreś z naszych rozwiązań może być szybsze o ileś od drugiego. Ja staram nie odrywać jednego zapytania od kontekstu działania całości serwera bo jest różnica w serwerze na localhoście i publicznym, do którego ma dostęp więcej niż jedna osoba i także masz tego świadomość podobnie jak ja. Dopiero po postawieniu serwisu w necie wychodzi jego optymalizacja, gdy do bazy jest jednocześnie wiele zapytań. Tutaj już różnica kilkudziesięciu czy kilkuset milisekund przy często wywoływanych funkcjach zaczyna grać rolę. Ale to już bardziej do tematu lub działu o optymalizacji powinno by było zostać przeniesione. Nie uważasz? Za dużo czynników ma wpływ ale przy jednakowym obciążeniu początkowa niewielka różnica zaczyna się powiększać. I stąd każdy niepotrzebny JOIN, każdy niepotrzebny SELECT to dodatkowy narzut czasowy, którego można uniknąć przebudowując zapytanie. I tylko o to mi chodzi. Nie ma co kruszyć kopii gdy nie jest to potrzebne. Ja testy swoich zapytań i skryptów mam codziennie idąc do pracy. Mam to szczęście, że pracuję w zawodzie wyuczonym i mam z tym kontakt przynajmniej 8 godzin dziennie jako praca, a i nieraz jeszcze po niej - hobbystycznie.

Dzięki thek Jesteś moim guru

Wykonałem testy dla bazy zapełnionej 1000, 10000, 100000 wierszami tak ja napisałem w poprzednim poście nie ma żadnej różnicy. Ja nie zaczynam żadnej wojny stwierdzam fakty. Pisałeś że moje zapytanie wykonuje się dłużej a ja ci tylko udowadniam że to nie jest prawda w tym konkretnym wypadku. Nie będę się roztrząsał nad setkami innych przypadków bo każdy trzeba potraktować indywidualnie.
Zasada optymalizujemy zapytania nie stosując podzapytań też nie jest prawdziwa.
Następnym razem nie pisz że czyjeś zapytanie lub kod jest gorszy lub wykonuje się dłużej jeżeli nie jesteś pewien, nie sprawdziłeś tego tylko tak sobie piszesz, albo poprzyj to dowodami i testami.

// EDIT Ja to wszystko wiem co piszesz poniżej. Nie dodaje już odpowiedzi bo nie lubię sam czytać tematów które schodzą z tematu. Ja tylko bronie swojego zarzuciłeś mi coś ja ci tylko udowodniłem że się mylisz. Moja wersja nie różni się niczym od twojej prócz zapisem.

To może jako ciekawostkę dodam, że w systemach DB2 by przyspieszyć wykonywanie zapytań, niejawnie SELECTy w podzapytaniach są konwertowane do JOINa odpowiedniego. Jeśli coś takiego robi IBM to jak myślisz, czy inni twórcy silników bazodanowych nie robią podobnie by osiągnąć lepszą wydajność? Może się więc okazać, że choć nasze zapytania są od strony usera różne, to po stronie bazy mogą być albo niemal identyczne, albo identyczne wręcz i nasza debata jest tak naprawdę mało istotna. Oczywiście na pewno dzieje się to w określonych przypadkach, a nie zawsze. Ale daje to ciekawą podstawę pod dalszą dyskusję na temat sensowności optymalizacji Lepiej pisać jawnie JOIN, czy zdać się na silnik i konwersję niejawną między subquery a JOIN. O tej ciekawostce przeczytałem w necie i byla ona ponić efektem korespondencji z inżynierem IBM, gdy zapytano go o wydajność pomiędzy subquery i join w DB2 i powiedzial on tam o znikomej różnicy, ze względu właśnie na niejawna konwersję zapytania.
Od momentu rejestracji w tematach widziałem bowiem wiele nieoptymalizowanych zapytań, gdzie ludzie używali ewidentnie niewłaściwych, stworzonych zapewne na podstawie tutoriali netowych bez zrozumienia ich sensu. Sam nieraz bylem zmuszony używać subqueries, bo nie zawsze jest możliwe zastosowanie join, choćby w sytuacjach gdy musimy użyć odpowiedniego WHERE pole IN. Jeśli jednak silniki bazodanowe w dużej mierze mają zaimplementowaną konwersję podzapytań do join, to może się okazać, że w większości przypadków spór "subqueries vs join" jest podobny do wyższości wielkiej nocy nad bożym narodzeniem A narzut mały i niemal brak różnic jest związany właśnie z niejawną konwersją :] Może jednak to się wydzieli jako osobny wątek w optymalizacji?