Re: join from multiple tables - Mailing list pgsql-general

On Thu, Mar 4, 2010 at 11:43 AM, Terry <td3201@gmail.com> wrote:
> 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.
>

I am also looking into using an INTERSECT as that behaves like what I
want but I can't intersect differing numbers of columns from multiple
tables.   For example, this limits my results to a single row but I
need to somehow get some other columns in the result:

SELECT * FROM (SELECT userid FROM dsclient_logs WHERE
dsclient_logs.ev_id > 23580900 INTERSECT SELECT dsbox_snum FROM dsbox)
as a