Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

select u select?

[es] :: MySQL :: select u select?

[ Pregleda: 2823 | Odgovora: 4 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

simor
Simo Radjenovic
Wirecard AG
München

Član broj: 19175
Poruke: 366
*.panline.net.



Profil

icon select u select?08.11.2004. u 17:25 - pre 236 meseci
Malo sam se "igrao" u MySQL-u i imam sledeci problem:

Imam tabelu "katedra":

+------------------+-------+
| naziv_kat | s_kat |
+------------------+-------+
| Elektronika | 4 |
| Rac. nauke | 3 |
| Rac. tehnika | 2 |
| Automatika | 1 |
| Telekomunikacije | 5 |
+------------------+-------+

i tabelu "nastavnik":

+-------+--------------------+--------+-------+-------+-------+
| s_nas | prezime_ime | zvanje | s_dir | plata | s_kat |
+-------+--------------------+--------+-------+-------+-------+
| 1 | Profesor1 | Dr | NULL | 1000 | 2 |
| 2 | Profesor2 | Dr | 1 | 500 | 2 |
| 3 | Profesor3 | Mr | 1 | 500 | 1 |
| 4 | Profesor4 | Dr | NULL | 700 | 3 |
| 5 | Profesor5 | Mr | 1 | 450 | 2 |
| 6 | Profesor6 | Mr | 4 | 450 | 3 |
| 7 | Profesor7 | Dr | 4 | 200 | 3 |
| 8 | Profesor8 | Dr | NULL | 905 | 4 |
| 9 | Profesor9 | Mr | 8 | 805 | 5 |
+-------+--------------------+--------+-------+-------+-------+

Treba da prikazem ukupan broj zaposlenih na katedri sa nazivom
"Rac. tehnika". Idem preko kolone s_kat koja je u prvoj i u drugoj tabeli.
I ima dva nacina:

SELECT count(*)
FROM katedra k,nastavnik n
WHERE k.s_kat=n.s_kat AND k.naziv_kat='Rac. tehnika';

drugi:

SELECT count(*)
FROM nastavnik
WHERE s_kat=(SELECT s_kat FROM katedra WHERE naziv_kat='Rac. tehnika');

E, sad prvi nacin radi, ali drugi nece. Izgleda negde gresim u "nested"
sintaksi. Ovo radi u Access-u ali ovde mi prijavljuje gresku.

Hvala!

ps: izvinjavam se posto nisam citao prethodne postove gde je ovo mozda
vec bilo objasnjeno...
 
Odgovor na temu

Dejan Topalovic
Dejan Topalović
Senior Oracle DBA & Senior PL/SQL
Developer, Erste Sparinvest (Erste
Bank), Vienna, Austria
Vienna

Član broj: 635
Poruke: 1374
*.1.14.vie.surfer.at.

Sajt: www.baze-podataka.net


+2 Profil

icon Re: select u select?08.11.2004. u 21:30 - pre 236 meseci
Kod mene radi na verziji 4.1.
Code:

mysql> SELECT count(*)
    -> FROM nastavnik
    -> WHERE s_kat=(SELECT s_kat FROM katedra WHERE naziv_kat='Rac. tehnika');
+----------+
| count(*) |
+----------+
|       3  |
+----------+

Koju verziju MySQL-a imas? Vjerovatno tvoja verzija ne podrzava subselects.
Blog - baze podataka
---------------------
Oracle OCP DBA (9i & 10g)
Oracle Database: SQL Certified Expert
Oracle OCP Developer
Certified MySQL DBA
 
Odgovor na temu

simor
Simo Radjenovic
Wirecard AG
München

Član broj: 19175
Poruke: 366
*.panline.net.



Profil

icon Re: select u select?08.11.2004. u 22:31 - pre 236 meseci
Kad otkucam mysql, evo sta pise:
Citat:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.18-nt


Mozda treba da skinem najnoviju verziju?
 
Odgovor na temu

Dejan Topalovic
Dejan Topalović
Senior Oracle DBA & Senior PL/SQL
Developer, Erste Sparinvest (Erste
Bank), Vienna, Austria
Vienna

Član broj: 635
Poruke: 1374
*.1.14.vie.surfer.at.

Sajt: www.baze-podataka.net


+2 Profil

icon Re: select u select?08.11.2004. u 23:05 - pre 236 meseci
Da, jer kao sto pise ovdje http://dev.mysql.com/doc/mysql/en/Subqueries.html :
Citat:

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

With earlier MySQL versions, it was necessary to work around or avoid the use of subqueries, but people starting to write code now will find that subqueries are a very useful part of the MySQL toolkit.

For MySQL versions prior to 4.1, most subqueries can be successfully rewritten using joins and other methods. See section 14.1.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions.

Blog - baze podataka
---------------------
Oracle OCP DBA (9i & 10g)
Oracle Database: SQL Certified Expert
Oracle OCP Developer
Certified MySQL DBA
 
Odgovor na temu

simor
Simo Radjenovic
Wirecard AG
München

Član broj: 19175
Poruke: 366
*.panline.net.



Profil

icon Re: select u select?09.11.2004. u 18:55 - pre 235 meseci
Sada radi pošto sam skinuo noviju verziju...

Hvala!
 
Odgovor na temu

[es] :: MySQL :: select u select?

[ Pregleda: 2823 | Odgovora: 4 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.