开发者

MySQL - How to get NULL if equality does not exist

CREATE TABLE Customer(  
customer_id INT NOT NULL,  
first_name VARCHAR(20),  
last_name VARCHAR(20),  
PRIMARY KEY (customer_id)  
);  
CREATE TABLE Payment(  
customer_id NOT NULL,  
year YEAR,  
payment_amount INT,  
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)  
);  

Here is the query I am using:

SELECT Customer.first_name, Payment.year  
FROM Customer  
LEFT JOIN Payment  
ON Customer.customer_id = Payment.customer_id  
WHERE Payment.year = 2008;  

Now, let's say I had three customer John, Bob, and Anne. John and Anne made payments in 2008 but Bob did not so the result from this query is :

first_name year

John 2008

Anne 2008

but what I want is:

first_name year

John 2008

Anne 2008

Bob NULL

开发者_StackOverflow

So I know I need a line that says something like for a tenant_id if there is no such equality payment.year = 2008 than return NULL, but I am not sure how to do that. Thanks!


WHERE Payment.year = 2008;   

should be

AND Payment.year = 2008;  

WHERE is applied after the JOIN so it filters out the NULL

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜