Jump to content


Photo

MySQL subquery help/alternative


  • Please log in to reply
1 reply to this topic

#1 embsupafly

embsupafly
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 30 March 2006 - 04:01 AM

Trying to run the following MySQL query/subquery:

SELECT products_id FROM products_attributes WHERE products_id = (SELECT products_id FROM products WHERE products_model = 'V100');

I am doing this in phpMyAdmin and I am getting back a syntax error.

I am running MySQL version 4.0.25, and thought I heard something a while back that subqueries were not supported until 4.1, but then again, I am not sure, but I don't want to rack my brain out if this isn't going to work wity my version of MySQL.

Should this work in pre 4.1 MySQL? Do I have the wrong syntax? Is there an alternative way to do this.

What I am trying to do with the query is select all products_id from the products_attributes table that match products_ids that are in the products table that correspond to a certain products_model.
Eric

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 March 2006 - 07:33 AM

You're correct -- no subqueries until v4.1.

Luckily, in your case, you can rewrite this a JOIN -- which is usually better to do anyway, if possible (UNTESTED):

SELECT p.products_id FROM products AS p LEFT JOIN products_attributes AS pa ON ( p.products_id = pa.products_id ) WHERE p.products_model = 'V100';

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users