StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are several things you should know.</p> <p>The first is that the default SQL JOIN construct is essentially a set cross product, restricted by the WHERE clause. This means it's multiplicative - you get duplicate results out which you then prune down. You also have to be careful in the presence of NULL fields.</p> <p>The second is that there is a 'DISTINCT' keyword. When you prefix a column in the selection with this, you'll get at most one instance of a certain value for that column in the results. So, as per your query, 'SELECT DISTINCT user.id FROM' will eliminate the redundancies on the server side.</p> <p>The third is that the correct way to solve this problem is likely not using the <code>*</code> operator. I suggest:</p> <pre><code>SELECT user.id,username,email,subject FROM message m,user WHERE m.user_id=user.id AND user.status=1 </code></pre> <p>This uses the simple, easy-to-understand implicit-join syntax and should be valid SQL on whatever server. I can vouch for it working with MySQL, at least. It also aliases the 'message' table to 'm' as shorthand.</p> <p>As you surmise, this will reduce the traffic from the SQL server to your database.</p> <p>edit: if you want to eliminate the "redundant" email information, you can't - you must make two distinct queries. SQL results are tables and must be rectangular, with all known values filled. There's no 'ditto' entry.</p> <p>edit 2: You only have to make two queries. For instance:</p> <pre><code>SELECT subject FROM message WHERE message.id IN (SELECT user.id FROM user WHERE status=1) </code></pre> <p>This is one query that contains a nested query, so it's really making two database hits. But it doesn't have any programmatic loops.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload