Wyświetlenie rekordów spełniających warunek, 2 miejscowości w tym samym transporcie Opcje

[pionas]

Cześć,

mam pewien problem.
Otóż zaprojektowałem sobie tak tablicę (wydawało mi się że tak najlepiej):

BusWay:
ID
Name

City:
ID
Name

CityToBusWay
ID_BusWay
ID_City

jak zrobić zapytanie które wyświetli tylko te połączenia BusWay w których będą dwie przeze mnie podane miejscowości.
Np. Chcę jechać Radomia do Warszawy.
Zrobiłem sobie coś takiego:

[SQL] pobierz, plaintext 
SELECT * FROM BusWay a, CityToBusWay b WHERE a.ID=b.ID_BusWay AND b.ID_City IN (SELECT ID FROM `city` WHERE name IN("Radom","Warszawa"))
[SQL] pobierz, plaintext 

Jednak to wyświetla wszystkie wpisy które zawierają w połączeniu jedno z tych miast, jak zrobić aby wyświetlało tylko te gdzie są podane oba miasta?

[maly_swd]

ogolnie musisz zrobic podzapytanie z GROUP BY i HAVING COUNT(*)=2 (czyli maja wystapic te dwa bustopy)

[pionas]

Jednak dalej mam problem, otóż zapomniałem że Radom->Warszawa to nie to samo co Warszawa->Radom także muszę dodać jeszcze jakiś warunek do tabeli CityToBusWay dodać pole ID (autonumerowanie) tak aby sprawdzić czy Radom ma mniejsze ID od ID Warszawy i jeśli tak to wyświetli to połączenie - da się coś takiego wykombinować?

[#luq]

A ja nie rozumiem co ma zawierać tabelka BusWay.

Dlaczego nie tak:

Kod

city
cityId | name

bus_way
bysWayId | from | to

?

Tak btw. nazwa miasta nie jest dobrym identyfikatorem bo jest wiele miast o nazwie np. Psary czy jakieś tam inne. Powinno być pole z kodem pocztowym i on powinien być wykorzystywany w zapytaniach.

[pionas]

No tak, tylko że to to mniejszy problem Większym problemem jest to że jadąc z punktu A do B przez C,D,E to nie to samo co z punktu B do punktu A przez E,D,C...
Załóżmy że jadę z Zakopanego do Warszawy:
- Zakopane
- Kraków
- Kielce
- Radom
- Warszawa
jeśli szukam połączenia z Radomia do Kielc to powyższe zapytanie też mi to wyświetli a nie powinno bo Kielce są przed Radomiem (w tym połączeniu) a autobus nie zawróci

[#luq]

Ale przecież Zakopane -> Warszawa to zupełnie inna trasa niż Warszawa -> Zakopane, inny numer trasy...

Ja bym zrobił taką bazę:

Kod

city
cityId | name

way (tabelka może być pomocna)
wayId | from | to // czyli przystanek pierwszy i ostatni

route
routeId | routeNumber

stop
stopId | routeId | stopNumber | cityId

Ten post edytował #luq 14.09.2010, 07:35:53

[pionas]

A mógłbyś opisać trochę tabelę route i stop?
routeId - autonumerowanie?
routeNumber - ilość przystanków?

stopId - autonumerowanie?
routreId - route.routeId?
stopNumber??
cityId - city.cityId?


w tabeli way from i to to mają być cityId czy całe nazwy?

Ten post edytował pionas 15.09.2010, 08:57:01

[#luq]

Route - trasa
Stop - przystanek

routeId - autonumerowanie?

Tak, wszystkie pola xxxId są autoincrement.

routeNumber - ilość przystanków?

Nie, to numer trasy.

stopId - autonumerowanie?

Tak, zresztą o tym wyżej wspomniałem.

routreId - route.routeId?

Nie rozumiem o co Ci chodzi tutaj...

stopNumber??

Numer przystanku, dzięki temu wiemy, że autobus jedzie kolejno np. Katowice -> Kraków -> Łódź -> Warszawa,

w tabeli way from i to to mają być cityId czy całe nazwy?

Oczywiście, że cityId

[pionas]

Zrobiłem to trochę inaczej:

BusWay:
ID
Name // np. Połączenie Zakopane - Kraków

City:
ID
Name

CityToBusWay
ID
ID_BusWay
ID_CityStart // z jakiej miejscowosci
ID_CityEnd // do jakiej miejscowosci

I teraz mam taki problem.
Jak zrobić aby wyświetliło mi takie info: Połączenie z A do D przez B, C.

SELECT Name FROM BusWay - wyświetli mi nazwę połączenia
SELECT Name FROM city WHERE ID IN (SELECT ID_CityStart FROM CityToBusWay WHERE ID_BusWay=4 GROUP BY ID_CityStart) OR ID IN (SELECT ID_CityEnd FROM CityToBusWay WHERE ID_BusWay=4 GROUP BY ID_CityEnd) - wyświetli mi nazwy miejscowości z połączenia o ID=4

Da się to połączyć w jedno zapytanie?

Albo jak inaczej zaprojektować bazę aby wyświetlić informację o połączeniu tzn. skąd dokąd i przez co jest

[#luq]

Jakbyś zaprojektował bazę zgodnie z tym co napisałem to byś miał prosto

[SQL] pobierz, plaintext 
SELECT 
  s.stopNumber, c.name
FROM
  stop AS s
  LEFT JOIN city AS c ON s.cityId = c.cityId
WHERE
  s.routeId = {x}
ORDER BY s.stopNumber ASC
[SQL] pobierz, plaintext 

Dostajesz wszystkie przystanki trasy o id = x, a więc pierwszy to Twoje A, ostatni D, a reszta to przystanki pośrednie.

PS. w podanej przeze mnie tabelce way powinno być jeszcze pole routeId

Ten post edytował #luq 25.09.2010, 14:51:40

[pionas]

Zrobiłem coś takiego:
select ID_BusWay, group_concat(distinct ID_CityStart separator ',') as route from CityToBusWay group by ID_BusWay

Wyświetla mi ID całej trasy i przystanki od A do C (bez ostatniego) tylko jak zamienić ID miejscowości na ich nazwy?


#luq jakbym przerobił na Twoje tablice to jakby wyglądał przykład?

city
cityId | name
1 | Radom
2 | Kozienice
3 | Warszawa
4 | Wrocław
5 | Zakopane
6 | Kraków
7 | Kielce

way (tabelka może być pomocna)
wayId | routeID | from | to // czyli przystanek pierwszy i ostatni


route
routeId | routeNumber

stop
stopId | routeId | stopNumber | cityId

stopNumber to musiałbym ręcznie wprowadzać?

[netmare]

Ogólnie rzecz biorąc pogubiłem się trochę w waszym dialogu, natomiast rozwiązaniem problemu z pierwszego postu jest chyba (nie chciało mi się przetestować) zapytanie poniżej:

[SQL] pobierz, plaintext 
SELECT 
 
	BusWay.*
 
FROM
BusWay
 
 
INNER JOIN CityToBusWay AS Pomocnicze1
	ON Pomocnicze1.ID_BusWay = BusWay.ID	
INNER JOIN City AS SzukaneMiasto1
	ON SzukaneMiasto1.ID = Pomocnicze1.ID_City AND SzukaneMiato1.Name='Radom'
 
 
INNER JOIN CityToBusWay AS Pomocnicze2
	ON Pomocnicze2.ID_BusWay = BusWay.ID	
INNER JOIN City AS SzukaneMiasto2
	ON SzukaneMiasto2.ID = Pomocnicze2.ID_City AND SzukaneMiato2.Name='Warszawa'
 
 
[SQL] pobierz, plaintext 

I tutaj jeszcze mała uwaga, zapytanie nie jest odporne na to czy chcesz pojechać z Radomia do Warszawy czy odwrotnie, bo to chyba będą dwie osobne trasy w twojej bazie, ale możesz to uwzględnić dodając do CityToBusWay kolumnę np. StopOrder (int) i na końcu zapytania dokleić

[SQL] pobierz, plaintext 
WHERE Pomocnicze2.StopOrder > Pomocnicze1.StopOrder
[SQL] pobierz, plaintext 

Ilość miast możesz zwiększyć kopiując jeden z czteroliniowych ciągów związanych z miastem, zmienić numer na kolejny i nazwę miasta na poszukiwaną.

[pionas]

netmare chyba nie za bardzo.
Chodzi o to że w formularzu podaję skąd dokąd i przez co (niekoniecznie) tworzę połączenie i potem generuje mi się lista wszystkich możliwych połączeń i tam ustalam datę, godzinę i cenę i potem to zapisuję w tablicy CityToBusWay.

Tak to wygląda:


Problem tkwi w tym że nie wiem czy wyświetlać wszystkie możliwe kombinacje (Połączenie A-D, A-B, A-C, B-C, B-D, C-D) czy po prostu napisać że jest to połączenie z Punktu A do Punktu D przez Punkty B i C
Osobiście bym wolał to drugie rozwiązanie tylko nie wiem jak zaprojektować bazę aby coś takiego otrzymać (projekt bazuje na mysql+java)


Mógłbym dodać do CityToBusWay dodatkowe pole (step) w którym bym zapisywał który to krok połączenia i całość zrobić na dwóch zapytaniach:
1. pobranie wszystkich połączeń (SELECT * FROM BusWay)
2. pobranie miejscowości pośrednich (SELECT b.Name FROM CityToBusWay a, City b WHERE a.ID_BusWay={x} AND a.step>1 and a.ID_CityStart=b.ID GROUP BY a.ID_CityStart ORDER BY a.ID ASC)

No chyba że ktoś ma lepszy pomysł?

[EDIT]
Zrobiłem to w jednym zapytaniu:
SELECT c.*, GROUP_CONCAT(DISTINCT b.Name ORDER BY a.ID separator ', ') FROM CityToBusWay a, City b, BusWay c WHERE c.id=a.ID_BusWay AND a.step>1 and a.ID_CityStart=b.ID GROUP BY c.Name ORDER BY a.ID ASC)

Jednak z moim ostatnim zapytaniem jest problem:

[SQL] pobierz, plaintext 
SELECT c.*, GROUP_CONCAT(DISTINCT b.Name ORDER BY a.ID separator ', ') FROM CityToBusWay a, City b, BusWay c WHERE c.id=a.ID_BusWay AND [b]a.step>1[/b] AND a.ID_CityStart=b.ID GROUP BY c.Name ORDER BY a.ID ASC)
[SQL] pobierz, plaintext 

Nie wyświetla połączeń bez miejscowości pośrednich. Jak usunę a.step>1 to wyświetla wszystkie z tymże na początku jest miejscowość z której wyjeżdżamy, da się to obejść substringiem ale czy jest możliwość poprawienie zapytania by uzyskać taki efekt?

Ten post edytował pionas 26.09.2010, 08:13:39