How to get data from two different tables? [closed]

Question

I have these two MySQL tables:

tab1:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | char(32)    | NO   | PRI | NULL    |       |
| starttime | datetime    | NO   | MUL | NULL    |       |
| endtime   | datetime    | YES  |     | NULL    |       |
| sensor    | int(4)      | NO   |     | NULL    |       |
| ip        | varchar(15) | NO   |     |         |       |
| termsize  | varchar(7)  | YES  |     | NULL    |       |
| client    | int(4)      | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

tab2:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| session   | char(32)     | NO   |     | NULL    |                |
| success   | tinyint(1)   | NO   |     | NULL    |                |
| username  | varchar(100) | NO   |     | NULL    |                |
| password  | varchar(100) | NO   |     | NULL    |                |
| timestamp | datetime     | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

When I use this select:

SELECT DISTINCT ip, COUNT(id) AS SESSION count
FROM tab1 
WHERE date_format(tab1.starttime, '%Y-%m-%d') > "2015-01-01" 
GROUP BY ip
ORDER BY COUNT(id) DESC
LIMIT 5;

I get this:

+-----------------+---------------+
| ip              | countSessions |
+-----------------+---------------+
| IP1             |        115917 |
| IP2             |         67444 |
| IP3             |         48300 |
| IP4             |         19937 |
| IP5             |         19288 |

UPDATE

If I use this select:

SELECT DISTINCT tab1.ip, COUNT(tab1.id) AS sessions, COUNT(tab2.id) AS All 
FROM tab1, tab2  
WHERE tab1.id = tab2.session AND date_format(tab1.starttime, '%Y-%m-%d') > "2015-01-01" AND tab2.success = 1  
GROUP BY tab1.ip 
ORDER BY COUNT(tab1.id) DESC 
LIMIT 5;

I get:

    +-----------------+--------------------+-----------------+
    | ip              | sessions           | successful      |
    +-----------------+--------------------+-----------------+
    | IP1             |               7333 |            7333 |
    | IP2             |               4393 |            4393 |
    | IP3             |               2359 |            2359 |
    | IP4             |               2003 |            2003 |
    | IP5             |               1666 |            1666 |

If I use this select:

SELECT DISTINCT tab1.ip, COUNT(tab1.id) AS sessions, COUNT(tab2.id) AS unsuccessful 
FROM tab1, tab2  
WHERE sessions.id = auth.session AND date_format(sessions.starttime, '%Y-%m-%d') > "2015-01-01" AND tab2.success = 0 
GROUP BY tab1.ip 
ORDER BY COUNT(tab1.id) DESC 
LIMIT 5;

I get:

+-----------------+--------------------+-----------------+
| ip              | sessions           | unsuccessful    |
+-----------------+--------------------+-----------------+
| IP6             |               7200 |            7200 |
| IP7             |               4100 |            4100 |
| IP8             |               1990 |            1990 |
| IP9             |               1732 |            1732 |
| IP10            |               1500 |            1500 |

I need (expect):

I need connect these two tables (tab1 and tab2) and after I need get count of all, successful and unsuccessful logins per IP for all time in one output (combine above two partial outputs).

                                                                            +-----------------+--------------------+-----------------+----------------+
    | ip              | ALL                | successful      | unsuccessful   |
    +-----------------+--------------------+-----------------+----------------+
    | IP6             |               14700|            7500 |            7200| 
    | IP1             |               14200|            7300 |            6900|
    | IP3             |               13900|            7600 |            6300|
    ...

Example of data:

tab1:

+----------------------------------+---------------------+---------------------+--------+----------------+----------+--------+
| id                               | starttime           | endtime             | sensor | ip             | termsize | client |
+----------------------------------+---------------------+---------------------+--------+----------------+----------+--------+
| ID1                              | 2015-05-07 18:01:20 | 2015-05-07 18:01:32 |     10 | IPX        | NULL     |      1 |
| ID2                              | 2014-08-27 10:50:46 | 2014-08-27 10:51:16 |      2 | IPY        | NULL     |   NULL |
| ID3                              | 2015-11-25 22:15:41 | 2015-11-25 22:19:40 |     10 | IPZ        | 80x24    |      8 |
+----------------------------------+---------------------+---------------------+--------+----------------+----------+--------+

tab2:

+----+----------------------------------+---------+----------+----------+---------------------+
| id | session                          | success | username | password | timestamp           |
+----+----------------------------------+---------+----------+----------+---------------------+
|  1 | ID1                              |       1 | name     | pass1    | 2014-07-13 09:24:11 |
|  2 | ID2                              |       0 | name     | pass2    | 2014-07-13 09:28:36 |
|  3 | ID3                              |       1 | name     | pass3    | 2014-07-13 10:29:10 |
+----+----------------------------------+---------+----------+----------+---------------------+

SHOW CREATE TABLE

| tab1 | CREATE TABLE `tab1` (
  `id` char(32) NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime DEFAULT NULL,
  `sensor` int(4) NOT NULL,
  `ip` varchar(15) NOT NULL DEFAULT '',
  `termsize` varchar(7) DEFAULT NULL,
  `client` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `starttime` (`starttime`,`sensor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


| tab2  | CREATE TABLE `tab2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session` char(32) NOT NULL,
  `success` tinyint(1) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4063227 DEFAULT CHARSET=latin1 |

SQLFiddle.com

Can you help me with creation of proper SQL select please?

Answer 1

This query GROUPs by Ids and uses:

• CASE WHEN to invert 0 and 1 and SUM it for Unsuccessful sessions
• SUM of all the 1 for Successful sessions.

Query:

SELECT t1.ip
    , COUNT(*) as `ALL`
    , SUM(t2.success) as Successful
    , SUM(CASE t2.success WHEN 0 THEN 1 ELSE 0 END) as Unsuccessful
FROM Tab1 t1
INNER JOIN Tab2 t2 ON t1.id = t2.session
GROUP BY t1.ip

SQL Fiddle using the small sample from your question.

Output:

IP  | ALL  | Successful | Unsuccessful
IPX | 1    | 1          | 0
IPY | 1    | 0          | 1
IPZ | 1    | 1          | 0