Re: join from multiple tables

On Thu, Mar 4, 2010 at 11:33 AM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> 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
>

Thank you for the reply. It is returning a row for each match on
backup_sets for some reason:

ev_id | type | ev_time | category | error |
ev_text
| userid | ex_long | client_ex_long | ex
_text | timestamp |
set_name | company_name |
account_num
----------+------+------------+----------+------------+----------------------------------------------------------------------------------------------+--------------+---------+----------------+---------------
-------------------+---------------------+----------------------------------------------------------+----------------------------------+-------------
23580885 | 0 | 1267722095 | 2 | 1073741928 | Established
socket connection
| DSC000100188 | 1097902 | 170202 | narf |
2010-03-04 11:01:35 | red | FOO | BAR001
23580885 | 0 | 1267722095 | 2 | 1073741928 | Established
socket connection
| DSC000100188 | 1097902 | 170202 | narf |
2010-03-04 11:01:35 | blue | FOO | BAR001

It should have only returned 1 row above. It is duplicating each
ev_id for each backup_set that matches.