changing sql query to suit mysql inabilty to perform subqueries

[msd]

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

[nicc777]

Try this:

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;

Notes :

1) I found that when you use JOIN's propperly, you never need nested SQL statements. The moment you do nested statements - something's wrong.

2) Since you specified a.format = 'CD' from the above sample, you actually don't need to eliminate the '12' format at all - this will happen automatically, as 'CD' is never equal to '12' and we only want 'CD'.

3) You might want to consider returning the productid as well, as you might need it in your application - this is just a guess.

4) I did not test/validate the above SQL statement - you may need to play around a bit

Hope that helps

[msd]

If i were to use your code then titles would be selected on CD that are also available on 12 inche record. if you took time to read the table you will see that 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??

[nicc777]

You are right - I don't understand your logic or why you would want to do this kind of query. Just a thought: what happens if the song title is the same, but it's actually different songs - or different versions of the same song? How do you plan to compensate for this then?

Anyway:

SELECT DISTINCT a.product_name, COUNT( a.product_name ) AS pcount, a.artist, b.price, a.format AS rformat
FROM products AS a, inventory AS b
WHERE a.product_id = b.productid
GROUP BY 1
HAVING pcount < 2 AND rformat <> '12'

Cheers