I have the following statement to query the table to find the music titles that only appear in CD. Below is the table (without some columns). can you see that some song titles appear more than once because they are avail on cd and record??

I used this query, but i dont think mysql supports the subquery after the NOT IN statement. The error message reads:

ERROR 1064: parse error near 'SELECT products.product_name from products WHERE format = '12')
ORDER BY product' at line 3

Please help.

SELECT products.product_name, products.artist, inventory.price
FROM products, inventory
WHERE products.product_id = inventory.productid AND format = 'CD' AND products.product_name NOT IN (SELECT products.product_name from products WHERE format = '12')
ORDER BY products.product_name DESC;


+------------+-----------------------------------+--------+
| product_id | product_name | format |
+------------+-----------------------------------+--------+
| 1 | Mothership Reconnection | CD |
| 2 | Tonight | 12 |
| 3 | The Message | 12 |
| 4 | The Violin | 12 |
| 5 | Sexy Boy | 12 |
| 6 | Lovesong | CD |
| 7 | Belo Horizonte | 12 |
| 8 | 1999 | 12 |
| 9 | 1999 | CD |
| 10 | (Hey You) Whats That Sound? | 12 |
| 11 | Paid In Full | CD |
| 12 | Mighty Hard Rocker | 12 |
| 13 | Cascade | CD |
| 14 | Can You Feel It | 12 |
| 15 | Gym Tonic (Extended Remix) | 12 |
| 16 | Get Up On It Like This | 12 |
| 17 | Music Sounds Better With You | CD |
| 18 | Music:Response | CD |
| 19 | Da Funk | 12 |
| 20 | Catalyst | 12 |
| 21 | Everything You Need (Robert Racic | 12 |
| 22 | Bug Powder Dust | 12 |
| 23 | Who Can Make Me Feel Good? | 12 |
| 24 | Guitarra G | 12 |
| 25 | Everyday | 12 |
| 26 | Beautiful (Remix) | CD |
| 27 | Remind Me | 12 |
| 28 | Nowhere | 12 |
| 29 | Never Knew Love | CD |
| 30 | Fall Into You (Soulstice Remix) | 12 |
| 31 | Fall Into You (Miguel Migs Remix) | 12 |
| 32 | Jaywalkin | 12 |
| 33 | Rapture (Deep Dish Remix) | 12 |
| 34 | Traveling | 12 |
| 35 | Into the Sun | CD |
| 36 | Sleep | 12 |
| 37 | Is You Is or Is You Aint My Baby? | CD |
| 38 | Hypnotising | 12 |
| 39 | Help Me (Rennie Pilgrem Remix) | CD |
| 40 | Uprock | 12 |
| 41 | Believe (Original Mix) | CD |
| 42 | True Love | 12 |
| 43 | Moogie | 12 |
| 44 | Life is Changing | 12 |
| 45 | Funny Break (Plump DJs Remix) | CD |
| 46 | At Night | CD |
| 47 | Wavy Gravy | 12 |
| 48 | Remind Me | CD |
| 49 | Nowhere | CD |
| 50 | Never Knew Love | 12 |
| 51 | Fall Into You (Soulstice Remix) | CD |
| 52 | Fall Into You (Miguel Migs Remix) | CD |
| 53 | Jaywalkin | CD |
| 54 | Rapture (Deep Dish Remix) | CD |
| 55 | Traveling | CD |
| 56 | Into the Sun | 12 |
| 57 | Sleep | CD |
| 58 | Is You Is or Is You Aint My Baby? | 12 |
| 59 | Hypnotising | CD |
| 60 | Help Me (Rennie Pilgrem Remix) | 12 |
| 61 | Uprock | CD |
| 62 | Believe (Original Mix) | 12 |
| 63 | True Love | CD |
| 64 | Moogie | CD |
| 65 | Life is Changing | CD |
| 66 | Funny Break (Plump DJs Remix) | 12 |
| 67 | At Night | 12 |
| 68 | Wavy Gravy | CD |
| 69 | Try Again | CD |
| 70 | Hayling | CD |
| 71 | Dont Panic | CD |
| 72 | Get Free | CD |
| 73 | Spirit Calling | CD |
| 74 | I Feel Love | CD |
| 75 | Big Bass Drum | CD |
| 76 | Renegades of Funk | CD |
| 77 | Defected Acapellas Vol. 1 | CD |
| 78 | Defected Acapellas Vol. 1 | 12 |
+------------+-----------------------------------+--------+

some music titles appear twice, identical until format where they are either CD or 12.

using
SELECT a.product_name, a.artist, b.price
FROM products AS a, inventory AS b
WHERE a.product_id = b.productid AND a.format = 'CD'
ORDER BY 1 DESC;

will simply find all cd songs. i want to find the songs that are available only in CD. thus i need to somehow query for titles THAT ONLY APPEAR ONCE.

do you know how to use a JOIN statement to connect a 'would-be" subquery to the query??