you can rewrite this query to simple joins so you dont use sub selects.
select media.media_id , media.price, media.filename
from media,category_locks,categories
where media.media_id = category_locks.media_id
and category_locks.category_id = categories.category_id
and categories.dynamic_media = 'true'
-----Original Message-----
From: Anders Alstrin [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 07, 2001 2:58 PM
To: [EMAIL PROTECTED]
Subject: Sub select.
Does anyone know how to do this sub select in MySQL.
I know how to do it in Oracle!
table media
+----------+-------+----------+
| media_id | price |filename |
+----------+-------+----------+
| 3 | 0 | cal.jpg |
| 82 | 0 | Soa.jpg |
| 13 | 0 | ca.jpg |
| 71 | 0 | gt.pdf |
| 67 | 0 | low.pdf |
| 68 | 0 | 10.pdf |
| 83 | 0 | B5.pdf |
+----------+-------+----------+
table category_locks
+------------------+-------------+----------+
| category_lock_id | category_id | media_id |
+------------------+-------------+----------+
| 92 | 0 | 68 |
| 93 | 0 | 67 |
| 94 | 0 | 13 |
| 91 | 0 | 71 |
| 90 | 0 | 3 |
| 118 | 43 | 82 |
| 121 | 43 | 83 |
+------------------+-------------+----------+
table categories
+-------------+-------------+--------+---------------+--------------+
| category_id | category_up | upload | dynamic_media | name |
+-------------+-------------+--------+---------------+--------------+
| 43 | 0 | false | true | web |
| 0 | 0 | false | false | trine |
| 0 | 0 | false | false | shop |
+-------------+-------------+--------+---------------+--------------+
A Oracle way to do it:
SELECT * FROM media
WHERE media_id = (SELECT media_id FROM category_locks
WHERE category_id = (SELECT category_id FROM categories
WHERE dynamic_media = 'true'))
A MySQL way to do it?
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
