Re: join from multiple tables

On Thu, Mar 4, 2010 at 11:33 AM, Thom Brown <thombrown@gmail.com> wrote:
> On 4 March 2010 17:26, Terry <td3201@gmail.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.