Обсуждение: join from multiple tables
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.
On 4 March 2010 17:26, Terry <td3201@gmail.com> wrote:
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_
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.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
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
Regards,
Thom
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.
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
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.
altho not an answer to your question, you might want to start using table name aliases, to make queries more readable.
so instead of:
SELECT dsclient_logs.ev_id,dsclient_
you would do:
SELECT dsl.*, sb.set_name, c.company_name, c.account_num
FROM dsclient_logs dsl
INNER JOIN connection_log cl ON dsl.ex_long = cl.session_id AND dsl.userid = cl.dsbox_snum
INNER JOIN backup_sets bs ON cl.set_id = bs.set_id
INNER JOIN customer c ON cl.account_num = c.account_num
ORDER BY dsl.ev_id desc LIMIT 100
etc.
so instead of:
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
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
you would do:
SELECT dsl.*, sb.set_name, c.company_name, c.account_num
FROM dsclient_logs dsl
INNER JOIN connection_log cl ON dsl.ex_long = cl.session_id AND dsl.userid = cl.dsbox_snum
INNER JOIN backup_sets bs ON cl.set_id = bs.set_id
INNER JOIN customer c ON cl.account_num = c.account_num
ORDER BY dsl.ev_id desc LIMIT 100
etc.