January 13, 2014 at 10:40 am
Hi All,
I have a question as below :
i have a table "teams" from where home and away are being linked. I also have a column called event which is not linked and is an char value.
i tried to write a query to extract data from event when the values in home and away are null, which is exactly how the table would exist. however it returns a empty set.
Please note that if we check any one value for null it is goog as both the column will have null values when event has any text.
Select
case when home_team.name=null then to_char(to_date(date,'DY Mon DD YYYY'),'MM-DD')||' '||event|| else
to_char(to_date(date,'DY Mon DD YYYY'),'MM-DD')||' '||Away_team.name||' '||'@'||' '||home_team.name end as "Title",
from v_jobs
join teams as Home_team on v_jobs.home_team_id = home_team.id
join teams as Away_team on v_jobs.away_team_id = Away_team.id
Where v_jobs.id = 150000
group by
v_jobs.id
January 13, 2014 at 10:44 am
Hi and welcome to the forums. It is very difficult to provide much of an answer because unfortunately you didn't much in the way of details.
What happens if you change your inner joins to left joins?
If that doesn't do it, please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 13, 2014 at 11:02 am
...home_team.name=null...
This always evaluates to false. You can test it for null by using
...home_team.name IS NULL...
Probably a fairly common trap!
Also, you are joining on a column with a potentially null value, so when that column IS null, the join will not return rows.
Just thought I'd post a bit of feedback, you'll get more (and better) feedback by considering Sean's advice!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply