Should I use a join instead of loop

Question

I have two tables.

User table has user_id and order_num

Orders table has order_num, dd, mm, yyyy, order_detail.

When a user wants to see all orders for a certain month I usually select order_num from user table where user_id matches a cookie, then while loop and within the loop do a select on orders table matching order_num and displaying if month matches month selected.

Is there a more efficient way to do this? With a join of some sort? Quite basic sql knowledge but keen to expand

So, I currently do (i know its not good code but logical sql most important part of this question, i know how to code correctly)

select order_num from user where user_id = $cookie

while (results) {

    select order_detail, mm, dd, yyyy where mm = $month

    echo order_detail mm dd yyyy

    }

Yes, use a join. Learn about [SQL joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html). — eggyal, May 21 '12 at 00:32
The reason a join would be better is because you don't take repeated trips to the database with open/close each time, save network, improve performance and try to get the data you need once. — xQbert, May 21 '12 at 00:41

score 2 · Accepted Answer · answered May 21 '12 at 00:38

2

Sure, you can do something like:

SELECT * FROM users u
JOIN orders o ON u.order_num = o.order_num AND o.mm = ###
WHERE u.user_id = ###

answered May 21 '12 at 00:38

djdy

6,779
6
38
62

Many thanks - am now reading about JOINs, this is great learning for me – StudioTime May 21 '12 at 14:25

score 0 · Answer 2 · answered May 21 '12 at 00:36

0

The simplest form of join, to get you started:

SELECT u.order_num, o.order_detail, o.mm, o.dd, o.yyyy
FROM user AS u, order AS o
WHERE u.order_num = o.order_num AND o.mm = $month

answered May 21 '12 at 00:36

Sp4cecat

991
1
8
18

Totally, it was the only kind of join I did for a few years. It's essentially the same as the other answer. – Sp4cecat May 21 '12 at 00:43
3

This is the old, very old syntax for Joins. The explicit `JOIN` syntax has several advantages over this. – ypercubeᵀᴹ May 21 '12 at 00:44
1

Discussion of the syntax: http://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line – djdy May 21 '12 at 00:47
Thanks djdy; I would not recommend doing it the way I suggest with any more than two tables, best to go with INNER, OUTER, LEFT and RIGHT JOIN syntax to get a specific result. – Sp4cecat May 21 '12 at 00:55

Should I use a join instead of loop

2 Answers2