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

On Thu, Mar 4, 2010 at 1:44 PM, Terry <td3201@gmail.com> wrote:
> 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
>

Sadly, I solved this by examining my data more closely.  In short, I
couldn't tie everything together with the tables I was using.  By
including another table, I was able to construct my joins
appropriately.  It ended up being a join statement such as:

SELECT dsclient_logs.ev_id,dsclient_logs.type,to_timestamp(dsclient_logs.ev_time)
as
timestamp,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,backup_sets.set_name,customer.company_name,customer.account_num
FROM dsclient_logs
INNER JOIN connection_log ON dsclient_logs.ex_long =
connection_log.session_id AND dsclient_logs.userid =
connection_log.dsbox_snum
INNER JOIN backup_sets ON connection_log.set_id = backup_sets.set_id
INNER JOIN customer ON connection_log.account_num = customer.account_num
ORDER BY dsclient_logs.ev_id desc LIMIT 100

Thanks for the replies.