On Thu, Mar 4, 2010 at 1:44 PM, Terry <td3201@xxxxxxxxx> wrote: > 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 > 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
