CODEWITHSUNDEEP
sqlmysqljoin
user391986
user391986

Reputation: 30956

SQL Join with Null Values

My Query does not work when I have not set the ProcessorID in myComputer table How can I make a query that will show me the ProcessorName when I have a value assigned in ProcessorID or NULL when I don't?

CREATE TABLE myProcessor
(    
 ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
 Name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;  

INSERT INTO myProcessor (Name) VALUES ("xeon");

CREATE TABLE myComputer 
(
 ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
 Name VARCHAR(255) NOT NULL,
 ProcessorID INT DEFAULT NULL,
 FOREIGN KEY (ProcessorID) REFERENCES myProcessor(ID) ON DELETE CASCADE
) ENGINE=InnoDB;  

INSERT INTO myComputer (Name) VALUES ("Newton");

SELECT p.Name as ProcessorName, c.Name as ComputerName
FROM myComputer c, myProcessor p 
WHERE c.ProcessorID = p.ID
AND c.Name = "Newton";

The above select query currently returns null if the processorID has not been set.

Upvotes: 2

Views: 2657

Answers (2)

Eton B.
Eton B.

Reputation: 6291

SELECT p.Name as ProcessorName, m.Name as ComputerName FROM myComputer m
  LEFT JOIN  myProcessor p ON (m.ProcessorID = p.ID)
   WHERE m.Name = "Newton"

Upvotes: 2

Daniel Vassallo
Daniel Vassallo

Reputation: 344571

Your current query returns no rows if a computer is not assigned a processor, as in the example you provided.

You may want to use a left outer join, instead of the implicit inner join you are using:

SELECT     p.Name as ProcessorName, c.Name as ComputerName
FROM       myComputer c
LEFT JOIN  myProcessor p ON (c.ProcessorID = p.ID)
WHERE      c.Name = "Newton";

Returns:

+---------------+--------------+
| ProcessorName | ComputerName |
+---------------+--------------+
| NULL          | Newton       |
+---------------+--------------+
1 row in set (0.00 sec)

Upvotes: 7

Related Questions