Select LEFT / RIGHT JOIN using WHERE and return NULL

Question

I need to convert the following two working requests into one request, but all I'm trying to just die from me for various reasons. My end result is to try to list all the software at hand and show which software is installed and which is not installed for the particular PC requested. For the installed software, list the name; otherwise, NULL is NULL. I tried some subselect operators in the where clause, which gave me the result without error, but not the correct result. Any help is appreciated.

QRY1

SELECT device_software.sw_id FROM Software_device LEFT JOIN Device ON Software_device.d_id = Device.d_id WHERE Device.d_id = 1; 

qry2

 SELECT Software.name, Software.sw_id, qry1.sw_id FROM software LEFT JOIN qry1 ON software.sw_id = qry1.sw_id; 

---

Device table

 ------------------ | name | d_id | ------------------ | PC1 | 1 | | PC2 | 2 | | PC3 | 3 | ------------------ 

Program table

 ------------------ | name | sw_id | ------------------ | SW_a | A | | SW_b | B | | SW_c | C | | SW_d | D | ------------------ 

Software_Device table (many-to-many)

 ------------------ | d_id | sw_id | ------------------ | 1 | A | | 1 | B | | 2 | A | | 2 | B | | 2 | C | ------------------ 

Result Im look for ... (Installed and removed software on PC1)

 --------------------------------- | Sotfware | pc_id | name | --------------------------------- | SW_a | 1 | PC1 | | SW_b | 1 | PC1 | | SW_c | NULL | NULL | | SW_d | NULL | NULL | --------------------------------- 

I have listed the mysql and sql tags because I do not think this is important, but just in case, I use mysql.

Answer 1

If RomanKonz's answer is definitely close, just move "where" to "on" so that the left join works appropriately.

 select software.name as Software, device.d_id as pc_id, device.name as name from software left join device_software on device_software.sw_id = software.sw_id and **device_software**.d_id = 1 left join device on device_software.d_id = device.d_id ; 

Answer 2

 SELECT s.name AS software, IF((SELECT COUNT(sw.d_id) FROM software_device sw WHERE sw.sw_id = s.sw_id AND d_id = 1) > 0, 1, NULL) AS pc_id, (SELECT d.name FROM device d INNER JOIN software_device sw ON d.d_id = sw.d_id WHERE sw.sw_id = s.sw_id AND d.d_id = 1) AS name FROM software s ORDER BY s.name 

EDIT 2: Maybe not the most efficient / beautiful, but it works

Answer 3

according to your requirements, this will give you the correct result:

 select software.name as Software, device.d_id as pc_id, device.name as name from software left join device_software on device_software.sw_id = software.sw_id left join device on device_software.d_id = device.d_id and device.d_id = 1 

just notes: I prefer to migrate primary keys as the first column in the table. I also suggest calling your table in the plural (devices)

Answer 4

The following should do what you need:

 select s.`name` as `Software`, d.`d_id`, d.`name` as `Device` from software s left outer join software_device sd on sd.`sw_id` = s.`sw_id` and sd.`d_id` = 1 left outer join device d using (`d_id`) order by s.`name`