CODEWITHSUNDEEP
phpmysqlsql
sanchez
sanchez

Reputation: 4530

SQL query - WHERE not working

I have got this query which works fine:

SELECT p.design_id, p.product_id, t.account_id, p.name, p.width, p.height, 
 p.price, t.email 
 FROM designs AS p
 INNER JOIN ( SELECT user_id, email, account_id FROM users ) AS t
 USING(user_id) ORDER BY p.design_id LIMIT 0, 300 );

Now I am trying to trim the result:

SELECT p.design_id, p.product_id, t.account_id, p.name, p.width, 
 p.height, p.price, t.email 
 FROM designs AS p
 INNER JOIN ( SELECT user_id, email, account_id FROM users 
 WHERE account_id = 0) AS t
 USING(user_id) ORDER BY p.design_id LIMIT 0, 3 );

But the second query result is exactly the same as the first one.

Please could somebody shine some light on this?

//UPDATE

I removed the php vaiables to stop the unnecessary confusion :)

Upvotes: 1

Views: 134

Answers (3)

Rob
Rob

Reputation: 191

You have not selected column user_id from designs. Your JOIN is not working at all. From the manual:

The USING(column_list) clause names a list of columns that must exist in both tables.

Or all the users have account_id = 0 (or at least all the users that have designs).

If it's neither of those, try removing the word 'AS' before your nested select table 't'.

You could also try it without USING, use "ON t.user_id = p.user_id" instead of "USING(user_id)"

Upvotes: 2

Priya J.
Priya J.

Reputation: 61

Why don't you try using a placeholder in the where condition too as like below or was it a typo.

"SELECT p.design_id, p.product_id, t.account_id, p.name, p.width, p.height, p.price, t.email 
 FROM designs AS p
 INNER JOIN ( SELECT user_id, email, account_id FROM users WHERE account_id = **".$ind."**) AS t
 USING(user_id) ORDER BY p.design_id LIMIT ".$from.", ".$howMany );

Upvotes: 2

John Woo
John Woo

Reputation: 263813

give this a try

"SELECT   p.design_id, p.product_id, t.account_id, 
          p.name, p.width, p.height, p.price, t.email 
 FROM     designs AS p
          INNER JOIN  users AS t
              ON p.user_id = t.user_ID AND
                 t.account_id = $ind
 ORDER BY p.design_id LIMIT " . $from . ", " . $howMany

Upvotes: 2

Related Questions