On Thu, Mar 4, 2010 at 11:43 AM, Terry <td3201@xxxxxxxxx> wrote: > On Thu, Mar 4, 2010 at 11:33 AM, Thom Brown <thombrown@xxxxxxxxx> wrote: >> On 4 March 2010 17:26, Terry <td3201@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
