Den 01-08-07 14.57, skrev "Anders Alstrin" <[EMAIL PROTECTED]>:
> 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
>
>
Sorry! I figure it out by my self.
SELECT media.media_id,media.filename
FROM media,category_locks
LEFT JOIN categories ON (media.media_id=category_locks.media_id)
WHERE categories.dynamic_media = 'true'
AND category_locks.category_id != '0';
/Anders
---------------------------------------------------------------------
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
