Re: join from multiple tables

On 4 March 2010 17:26, Terry <td3201(at)gmail(dot)com> wrote:

> I have 4 tables: dsclient_logs,backup_sets,dsbox,customer. I want a
> query that will return all rows from dsclient_logs, insert two columns
> from the customer table, and one column from backup_sets. The
> relation is this:
>
> dsclient_logs.userid = dsbox.dsbox_snum AND backup_sets.box_id =
> dsbox.box_id AND dsbox.account_num = customer.account_num
>
> I originally had this:
>
> SELECT * FROM
> (SELECT
> dsclient_logs.ev_id,dsclient_logs.type,dsclient_logs.ev_time,dsclient_logs.category,dsclient_logs.error,dsclient_logs.ev_text,dsclient_logs.userid,dsclient_logs.ex_long,dsclient_logs.client_ex_long,dsclient_logs.ex_text,dsclient_logs.timestamp,backup_sets.set_name,customer.company_name,customer.account_num
> FROM dsclient_logs,dsbox,backup_sets,customer
> WHERE dsclient_logs.userid = dsbox.dsbox_snum AND backup_sets.box_id =
> dsbox.box_id AND dsbox.account_num = customer.account_num
> ORDER BY dsclient_logs.ev_id desc
> LIMIT 101) as a
> ORDER BY ev_id
>
> In the end, I want a single row for each ev_id that has the
> account_num, company_name, and backup_sets filled in. I have a
> feeling this needs to be done with a different type of join. Horrible
> explanation so I apologize and will gladly redefine my question upon
> some feedback.
>
>
I think you want an INNER JOIN. This won't match if any 1 table doesn't
match on the join.

SELECT dsclient_logs.ev_id,dsclient_
logs.type,dsclient_logs.ev_time,dsclient_logs.category,dsclient_logs.error,dsclient_logs.ev_text,dsclient_logs.userid,dsclient_logs.ex_long,dsclient_logs.client_ex_long,dsclient_logs.ex_text,dsclient_logs.timestamp,backup_sets.set_name,customer.company_name,customer.account_num
FROM dsclient_logs
INNER JOIN dsbox ON dsbox.dsbox_snum = dsclient_logs.userid
INNER JOIN backup_sets ON backup_sets.box_id = dsbox.box_id
INNER JOIN customer ON customer.account_num = dsbox.account_num
ORDER BY dsclient_logs.ev_id desc

If one side can be missing, you'd use a LEFT JOIN. For example, if
backup_sets is only sometimes present, and you still want to return data in
these instances, just use LEFT JOIN backup_sets.

Regards,

Thom