[es] - select u select?

simor
Simo Radjenovic
Beograd

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

Profil

^{08.11.2004. u 17:25}

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...

^{08.11.2004. u 17:25}

Dejan Topalovic
Dejan Topalović
Oracle DBA & PL/SQL Developer, Erste Sparinvest (..
Vienna

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

ICQ: 20142302
Sajt: www.outsourcing-it.com

Profil

Re: select u select?

^{08.11.2004. u 21:30}

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 - Database thingies
---------------------
Oracle OCP DBA
Oracle OCP Developer
Certified MySQL DBA

^{08.11.2004. u 21:30}

simor
Simo Radjenovic
Beograd

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

Profil

Re: select u select?

^{08.11.2004. u 22:31}

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?

^{08.11.2004. u 22:31}

Dejan Topalovic
Dejan Topalović
Oracle DBA & PL/SQL Developer, Erste Sparinvest (..
Vienna

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

ICQ: 20142302
Sajt: www.outsourcing-it.com

Profil

Re: select u select?

^{08.11.2004. u 23:05}

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 - Database thingies
---------------------
Oracle OCP DBA
Oracle OCP Developer
Certified MySQL DBA

^{08.11.2004. u 23:05}