[ simor @ 08.11.2004. 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...
[ Dejan Topalovic @ 08.11.2004. 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.
[ simor @ 08.11.2004. 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?
[ Dejan Topalovic @ 08.11.2004. 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.
[ simor @ 09.11.2004. 18:55 ] @
Sada radi pošto sam skinuo noviju verziju...

Hvala!