mysql multiple WHERE from different tables

Question

I want to output a nice user table. But my query needs a WHERE from multiple tables.

At the moment... my query looks like:

$statsTable = "someTable";
$userTable = "someOtherTable";

$someData = "SELECT stats.* FROM $statsTable stats, $userTable user 
WHERE user.some_status = '0' 
AND (stats.some_value BETWEEN $rangeFrom AND $rangeTo) 
ORDER BY stats.some_value ASC 
LIMIT 0,10";

then mysqli_query and so on...

The output(array) has 2 times the data from $statsTable and the WHEREs are not working. I just want to select the $statsTable...

How to proceed? Thanks :)

The problem is you've not described how stats table assocaites to the user table. Describe the relationship between stats and user tables in your where clause or learn to use ANSI-92 joins as outlined in this coding horror blog or add keyword Distinct after select... though I recommend against this last approach. Understanding why you're getting the data is a better long term approach, and fixing your query... by adding and statsTable.ssomefield = usertable.somefield Though using Ansi-92 joins would help prevent later — xQbert
– xQbert, Commented Aug 18, 2015 at 18:35
Thanks for the explanation and the idea, don't solved my problem immediately, but nice to know. — Tom
– Tom, Commented Aug 18, 2015 at 18:55

AleMelo · Accepted Answer · 2015-08-18 18:58:59Z

3

$statsTable = "someTable";
$userTable  = "someOtherTable";

$someQueryForData = "SELECT stats.* 
                     FROM $statsTable stats
                     JOIN $userTable user
                     ON (user.id_stats = stats.id)
                     AND (user.some_status = '0')
                     WHERE (stats.some_value BETWEEN $rangeFrom AND $rangeTo) 
                     ORDER BY stats.some_value ASC LIMIT 0,10";

Edit: explaining you're basically need a join, building query's the way you are doing makes them not as readable and you can't really associate your tables.

Using joins after you made your "ON" statement you may just add an "AND" And use that conjunction as a where which is way faster the using the where ITSELF

edited Aug 18, 2015 at 18:58

answered Aug 18, 2015 at 18:42

AleMelo

1284 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CodeGodie Over a year ago

I agree, this works, however @Alexandre should explain why

xQbert Over a year ago

Why did AND (user.some_status = '0') need to get moved to the join? Looks like it could have been left in the where. Same results, just wondering why.

AleMelo Over a year ago

Actually i think that would be better that way: ON (user.id_stats = stats.id and user.some_status = '0') Its faster then putting into the where

Andrew Goldenberg · Accepted Answer · 2015-08-18 18:50:24Z

0

Just use a join.

Join the tables on a unique ID and then you will have the values from both tables.

W3 Schools Joins

Should look like this

SELECT stats.* as stats, user.* as user
FROM statsTable
INNER JOIN userTable
ON stats.userId=user.userId
WHERE user.some_status = 0 AND (stats.some_value BETWEEN $rangeFrom AND     $rangeTo)
LIMIT 0,10;

edited Aug 18, 2015 at 18:50

answered Aug 18, 2015 at 18:35

Andrew Goldenberg

217 bronze badges

1 Comment

CodeGodie Over a year ago

This will not work since you forgot the alias on both tables

Collectives™ on Stack Overflow

mysql multiple WHERE from different tables

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related