2 Table Joins with WHERE clause

Question

I'm using Mysql and I'm having a difficult time trying to get the results from a SELECT query. I am having 2 tables. First table hardware, second table softwares_name_cache:

desc hardware;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| ID             | int(11)             | NO   | PRI | NULL    | auto_increment |
| DEVICEID       | varchar(255)        | NO   | PRI | NULL    |                |
| NAME           | varchar(255)        | YES  | MUL | NULL    |                |
| WORKGROUP      | varchar(255)        | YES  | MUL | NULL    |                |
| USERDOMAIN     | varchar(255)        | YES  |     | NULL    |                |
| OSNAME         | varchar(255)        | YES  | MUL | NULL    |                |
| OSVERSION      | varchar(255)        | YES  |     | NULL    |                |
| OSCOMMENTS     | varchar(255)        | YES  |     | NULL    |                |
| PROCESSORT     | varchar(255)        | YES  |     | NULL    |                |
| PROCESSORS     | int(11)             | YES  |     | 0       |                |
| PROCESSORN     | smallint(6)         | YES  |     | NULL    |                |
| MEMORY         | int(11)             | YES  | MUL | NULL    |                |
| SWAP           | int(11)             | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+

desc softwares_name_cache;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| ID    | int(11)      | NO   | PRI | NULL    | auto_increment |
| NAME  | varchar(255) | YES  | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

I'm trying to get the number of processors (sum(hardware.PROCESSORN) for each SQL server so I need to join (OSNAME LIKE '%Server%') condition from the table hardware and (softwares_name_cache where name like '%Microsoft SQL Server 20%') condition from the table softwares_name_cache.

Both your tables have a column called Name. Either use `table_name.Name` (`hardware.Name` or `softwares_name_cache.Name`, depending on which one you are applying the filter) or give the tables an alias and use that alias. — Rigerta, Aug 23 '17 at 07:30

score 0 · Answer 1 · edited Aug 23 '17 at 08:15

0

Since the field "Name" is common for both hardware and softwares_name_cache table. In your where condition give you are filtering with Name filed . Hence it throws error. Try like this:

SELECT sum(hardware.PROCESSORN) 
FROM hardware 
inner join softwares_name_cache on hardware.id = softwares_name_cache.ID 
WHERE (OSNAME LIKE '%Server%') AND softwares_name_cache.NAME IN  IN (select NAME from 
      softwares_name_cache where name like '%Microsoft SQL Server 20%');

edited Aug 23 '17 at 08:15

Hong Van Vit

2,884
3
18
43

answered Aug 23 '17 at 07:33

Krishnaraj Gunasekar

385
1
4
18

Same result sum(hardware.PROCESSORN) NULL which is not true – Oussema Benlagha Aug 23 '17 at 07:43

l.g.karolos · Answer 2 · 2017-08-23T07:40:46.547

0

SELECT sum(hardware.PROCESSORN) 
FROM hardware 
inner join softwares_name_cache on hardware.ID= softwares_name_cache.ID 
WHERE (OSNAME LIKE '%Server%') 
AND softwares_name_cache.NAME IN 
    (
     select softwares_name_cache.NAME
     from softwares_name_cache 
     where softwares_name_cache.NAME like '%Microsoft SQL Server 20%'
    );

edited Aug 23 '17 at 07:40

answered Aug 23 '17 at 07:35

l.g.karolos

1,131
1
10
25

result sum(hardware.PROCESSORN) NULL which is not trrue – Oussema Benlagha Aug 23 '17 at 07:38
Well there's a problem with the query itself so maybe the the inner join is not the ideal solution to get the `sum(hardware.PROCESSORN)`.. – Oussema Benlagha Aug 23 '17 at 07:46
Yes but the error is fixed right ?The logic behind the query is up to you or provide some extra info to see if I can help you – l.g.karolos Aug 23 '17 at 07:49
I'm trying to get the number of processors (sum(hardware.PROCESSORN) for each SQL server so I need to join (OSNAME LIKE '%Server%') condition from the table hardware and (softwares_name_cache where name like '%Microsoft SQL Server 20%') condition from the table softwares_name_cache. – Oussema Benlagha Aug 23 '17 at 08:04
Yes but those table dont have something in common why you join them on ID? Just join them and from the joined table get what you want – l.g.karolos Aug 23 '17 at 08:09
So `OSNAME` from `hardware` and `NAME` from `softwares_name_cache` are `Microsoft SQL Server` – l.g.karolos Aug 23 '17 at 08:49
@OussemaBenlagha if this answered your problem you can upvote it or at least give some info – l.g.karolos Aug 23 '17 at 09:34

2 Table Joins with WHERE clause

2 Answers2