join help

[ifm1989]

This is my query:

 

SELECT buddy_id

FROM buddy

LEFT JOIN (SELECT `user_id`,`user_name`,`user_lastvisit`)

FROM users

ON buddy.buddy_id = users.user_id

ORDER BY `order`, `id`

 

This is my error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM users ON buddy.buddy_id = users.user_id ORDER BY' at line 4

 

Help please 

[Wolphie]

You need to specify which way to order it by, ascending or desceding.

[btherl]

Try:

 

SELECT buddy_id
FROM buddy
LEFT JOIN users
ON buddy.buddy_id = users.user_id
ORDER BY `order`, `id`

 

I don't know if that order by will work unless you put those columns in the select list though.

 

Wolphie, the default ordering is ascending.  There's no need to specify.

[Wolphie]

Oh ok, i've never really used JOIN before, so it's also a learning experience for me - Pure instinct 

[ifm1989]

Try:

 

SELECT buddy_id
FROM buddy
LEFT JOIN users
ON buddy.buddy_id = users.user_id
ORDER BY `order`, `id`

 

I don't know if that order by will work unless you put those columns in the select list though.

 

Wolphie, the default ordering is ascending.  There's no need to specify.

Problem is, I only want `user_id`,`user_name`,`user_lastvisit` from the users field (the whole point of this join is to reduce server strain).

[btherl]

I don't think that will help.  SQL optimizers are very good, and can do that kind of optimization just by examining the select list, and seeing that you don't need all those extra rows.

 

I'll suggest this thread be moved to the mysql forum so the experts can take a look.  I may be wrong.

[ifm1989]

This is a working script, now I just need help making it so I can select user_name and user_lastvisit from it:

 

SELECT `buddy_id`,`buddy_order`

FROM buddy

JOIN users (this is where I try to put the select statement, but it doesn't work...)

ON buddy.buddy_id = users.user_id

WHERE users.user_id = buddy.buddy_id

GROUP BY `buddy_id`

ORDER BY `buddy_order`, `buddy_id`

 

[ifm1989]

Got it to work! I didn't know I could SELECT columns from two different tables

 

SELECT `buddy_id`,`buddy_order`, `user_name`, `user_name`, `user_lastvisit`

FROM buddy

JOIN users

ON buddy.buddy_id = users.user_id

WHERE users.user_id = buddy.buddy_id

GROUP BY `buddy_id`

ORDER BY `buddy_order`, `buddy_id`

[btherl]

Is buddy_id and user_id unique?  If so you can drop the group by.  You can also drop the where condition, it doesn't do anything.  So:

 

SELECT `buddy_id`,`buddy_order`, `user_name`, `user_lastvisit`
FROM buddy
JOIN users
ON buddy.buddy_id = users.user_id
ORDER BY `buddy_order`, `buddy_id`

 

If you need to specify which table to select a column from, you can do it like this:

 

SELECT `buddy`.`buddy_id` ... 

[aschk]

For a little more clarity on your SQL statements it's worth using aliases on your tables (just in case someone else picks up your SQL statement and doesn't know which tables provide which columns). Thus I give you the following:

SELECT b.`buddy_id` as 'id'
      ,b.`buddy_order` as 'order'
      , u.`user_name` as 'name'
      , u.`user_lastvisit` as 'lastvisit'
FROM buddy b
JOIN users u ON b.buddy_id = u.user_id
ORDER BY b.`buddy_order`, b.`buddy_id`

 

This will also help to identify any ambiguous column names (i.e. ones that exist in both tables).