March 21, 2003 at 12:49 pm
Is there a way to show null if there are no records that match the query key?
for example:
I am looking for 5 cities
so I query my table looking for people that match those cities
Lets say that there are only 2 cities that match with people
so my result is
city1|person1
city2|person2
but what I would like is
city1|person1
city2|person2
city3|null
city4|null
city5|null
is there some special way to accomplish this?
March 21, 2003 at 12:51 pm
You can do a LEFT OUTER JOIN onto Persons.
SQL returns all matched on the left and any possibles or null on the right.
CP
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 21, 2003 at 12:56 pm
Here's what i've got so far
SELECT dbo.calendar.weekend, dbo.RepWeeklyApp.RepID
FROM dbo.calendar LEFT OUTER JOIN
dbo.RepWeeklyApp ON dbo.calendar.weekend = dbo.RepWeeklyApp.WeekEnd
WHERE (dbo.RepWeeklyApp.RepID = 'CB909B54-7C0F-4908-B059-52B4027326AC') AND (dbo.calendar.weekend BETWEEN CONVERT(DATETIME,
'2003-01-10 00:00:00', 102) AND CONVERT(DATETIME, '2003-02-07 00:00:00', 102))
yet this only returns
2 records when there should be 5 because there are 5 weekends between the two dates
March 21, 2003 at 1:10 pm
If you leave out the second part of your where, does it work then?
As far as I can see, that is what's causing the select to return the two rows.
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 21, 2003 at 1:15 pm
nope. Because in the RepWeeklyApp table there are only 2 records that filter with the where (ie. only 2 records with that repID.) This has been driving me crazy all morning and I still can't figure this out
March 21, 2003 at 1:29 pm
hmmm, Not sure then.
Possible to backup the db (one with one two tables) and email to me?
CP
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 21, 2003 at 1:32 pm
You need to change the first part of your WHERE clause (before the AND) to this:
(dbo.RepWeeklyApp.RepID = 'CB909B54-7C0F-4908-B059-52B4027326AC' OR dbo.RepWeeklyApp.RepID IS NULL)
Jay Madren
Jay Madren
March 21, 2003 at 1:43 pm
quote:
You need to change the first part of your WHERE clause (before the AND) to this:(dbo.RepWeeklyApp.RepID = 'CB909B54-7C0F-4908-B059-52B4027326AC' OR dbo.RepWeeklyApp.RepID IS NULL)
Jay Madren
Thanx Jay, that worked ....sorta.
my only problem with this is because the calendar table has the weekend (friday) date of everymonth this year I kinda get more than records than I want. I tried leaving in the date comparison and I still get the same two records.
March 21, 2003 at 1:54 pm
You need the date comparison to limit the rows to those weekends you want. I suspect that you have a logic error in your where clause - probably parenthesis grouping. Whenever you use OR logic you have to be careful of that. Post your code and we'll try to figure it out.
Jay Madren
Jay Madren
March 21, 2003 at 2:05 pm
quote:
You need the date comparison to limit the rows to those weekends you want. I suspect that you have a logic error in your where clause - probably parenthesis grouping. Whenever you use OR logic you have to be careful of that. Post your code and we'll try to figure it out.Jay Madren
SELECT dbo.calendar.weekend, dbo.RepWeeklyApp.RepID
FROM dbo.calendar LEFT OUTER JOIN
dbo.RepWeeklyApp ON dbo.calendar.weekend = dbo.RepWeeklyApp.WeekEnd
WHERE (dbo.RepWeeklyApp.RepID = 'CB909B54-7C0F-4908-B059-52B4027326AC'OR dbo.RepWeeklyApp.RepID IS NULL) AND (dbo.calendar.weekend BETWEEN CONVERT(DATETIME,'2003-01-10 00:00:00', 102) AND CONVERT(DATETIME, '2003-02-07 00:00:00', 102))
the above code only returns 2 rows
this code
SELECT dbo.calendar.weekend, dbo.RepWeeklyApp.RepID
FROM dbo.calendar LEFT OUTER JOIN
dbo.RepWeeklyApp ON dbo.calendar.weekend = dbo.RepWeeklyApp.WeekEnd
WHERE (dbo.RepWeeklyApp.RepID = 'CB909B54-7C0F-4908-B059-52B4027326AC'OR dbo.RepWeeklyApp.RepID IS NULL) AND (dbo.calendar.weekend BETWEEN CONVERT(DATETIME,
'2003-01-10 00:00:00', 102) AND CONVERT(DATETIME, '2003-02-07 00:00:00', 102) or repweeklyapp.weekend is null)
returns almost what i'm looking for, but the date comparison isn't delimiting the number of rows. I tried this with and without the date comparison and "or repweeklyapp.weekend is null" neither gave me what I was looking for.
March 21, 2003 at 2:56 pm
Ok, you must have a lot of actual records where RepWeeklyApp.RepID is indeed null. I assumed that it was a PK or FK that would never contain null.
So try this:
SELECT dbo.calendar.weekend, dbo.RepWeeklyApp.RepID
FROM dbo.calendar LEFT OUTER JOIN
dbo.RepWeeklyApp ON dbo.calendar.weekend = dbo.RepWeeklyApp.WeekEnd
WHERE (dbo.RepWeeklyApp.RepID = 'CB909B54-7C0F-4908-B059-52B4027326AC' OR dbo.RepWeeklyApp.WeekEnd IS NULL) AND (dbo.calendar.weekend BETWEEN CONVERT(DATETIME,'2003-01-10 00:00:00', 102) AND CONVERT(DATETIME, '2003-02-07 00:00:00', 102))
By the way, is there a specific reason that you're using the CONVERT function? Simply '2003-01-10' is always interpreted by SQL Server as 'Jan 10 2003 00:00' no matter what the locale or language.
Jay Madren
Jay Madren
March 24, 2003 at 6:39 am
quote:
Ok, you must have a lot of actual records where RepWeeklyApp.RepID is indeed null. I assumed that it was a PK or FK that would never contain null.So try this:
SELECT dbo.calendar.weekend, dbo.RepWeeklyApp.RepID
FROM dbo.calendar LEFT OUTER JOIN
dbo.RepWeeklyApp ON dbo.calendar.weekend = dbo.RepWeeklyApp.WeekEnd
WHERE (dbo.RepWeeklyApp.RepID = 'CB909B54-7C0F-4908-B059-52B4027326AC' OR dbo.RepWeeklyApp.WeekEnd IS NULL) AND (dbo.calendar.weekend BETWEEN CONVERT(DATETIME,'2003-01-10 00:00:00', 102) AND CONVERT(DATETIME, '2003-02-07 00:00:00', 102))
By the way, is there a specific reason that you're using the CONVERT function? Simply '2003-01-10' is always interpreted by SQL Server as 'Jan 10 2003 00:00' no matter what the locale or language.
Jay Madren
Actually yes, there are a quite a number of records that would be null. This query that i'm trying to write will basically generate a report that will show those reps who do and do not have any appointments, thus the reason for the nulls. And as far as the convert, yes I know it's not needed. I was playing around in the querybuilder and it added the convert to my between.
Unfortunately the sql that you posted still only returns 2 rows. At this point i'm starting to wonder if the results i'm looking for are actually possible. Thanx for your help. I really appreciate it.
March 24, 2003 at 6:52 am
I know this is going to look weird but is there any to join both of these statements together
SELECT (dbo.Reps.[Last] + ', ' + dbo.Reps.[First]) as repname, dbo.RepWeeklyApp.Made1st, dbo.RepWeeklyApp.Kept1st, dbo.RepWeeklyApp.PGs, dbo.RepWeeklyApp.MS,
dbo.RepWeeklyApp.Made2nd, dbo.RepWeeklyApp.Kept2nd, dbo.RepWeeklyApp.Closed2nd, dbo.RepWeeklyApp.LastEditDate,
dbo.RepWeeklyApp.RegManager, @weekend as 'weekend'
FROM dbo.Reps LEFT OUTER JOIN
dbo.RepWeeklyApp ON dbo.Reps.ID = dbo.RepWeeklyApp.RepID
where reps.regmanager=@mgr and (@weekend = weekend or weekend is null) and dateterminated is null
order by last[\i]
and
select * from calendar where weekend between @date1 and date2[\i]
both of these statements sorta give me what i'm looking for and if I could sort of use the results from statement 2 in statement 1 then i'd be home free! Any ideas?
March 24, 2003 at 6:55 am
How about using a derived table to obtain the weekends, then an outer join to return the rows that you want?
SELECT calendar.weekend, RepiD
FROM
(SELECT Weekend
FROM dbo.calendar
WHERE weekend BETWEEN '2003-01-10' AND '2003-02-07') t1
LEFT JOIN dbo.RepWeeklyApp
ON t1.weekend = RepWeeklyApp.weekend
WHERE RepWeeklyApp.RepID = 'CB909B54-7C0F-4908-B059-52B4027326AC'
March 24, 2003 at 10:24 am
I haven't looked in detail at all the postings, but from past experience I've found when doing outer joins and trying to limit the results with a WHERE clause, you won't get what you think. Try moving the WHERE criteria into the FROM/ON clause:
ON dbo.calendar.weekend = dbo.RepWeeklyApp.WeekEnd
AND
(dbo.RepWeeklyApp.RepID = 'CB909B54-7C0F-4908-B059-52B4027326AC') AND (dbo.calendar.weekend BETWEEN CONVERT(DATETIME,
'2003-01-10 00:00:00', 102) AND CONVERT(DATETIME, '2003-02-07 00:00:00', 102))
-Dan
-Dan
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply