join from multiple tables

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

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_
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

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.