August 5, 2015 at 9:55 am
Hi Folks
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table
Make sense to anyone?
August 5, 2015 at 10:06 am
Start with this and see where you might go from there.
Select
Person,
ProposedEvent,
DayField,
TimeField
from
MyOptions
where
Person = 'me'
intersect
Select
Person,
ExistingEvent,
DayField,
TimeField
from
MyTimetable
where
Person = 'me';
August 5, 2015 at 10:25 am
To get rows from another table that may or may not have a match, you use an OUTER JOIN rather than a "regular" (INNER) JOIN. The columns from the mt table will return NULL if no matching existing event was found. Thus, you can replace mt.Person with ISNULL(mt.Person, '<string_here>') to replace NULL with something like '(No match found)' if you want to do that instead of seeing a NULL column.
Select mo.ID, mo.Person, mo.ProposedEvent, mo.DayField, mo.TimeField,
mt.Person AS ExistingPerson, mt.ExistingEvent
from MyOptions mo
left outer join MyTimetable mt on
mt.person = mo.person and
mt.DayField = mo.DayField and
mt.TimeField = mo.TimeField
where mo.person = 'me'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 6, 2015 at 2:06 am
Thank you both for your help - I'll try and setup a test with real data 🙂
August 6, 2015 at 8:10 am
ldanks (8/6/2015)
Thank you both for your help - I'll try and setup a test with real data 🙂
Depending on how you need to make this decision, try something along the lines of this:
DECLARE @ROWS AS int = (
SELECT COUNT(*)
FROM (
SELECT MO.ProposedEvent
FROM MyOptions AS MO
INNER JOIN MyTimetable AS MT
ON MO.person = MT.person
AND MO.DayField = MT.DayField
AND MO.TimeField = MT.TimeField
WHERE MO.person = 'me'
) AS X
);
IF @ROWS > 0
BEGIN
PRINT 'HOUSTON... We have a problem...';
END
ELSE
BEGIN
PRINT 'GOOD TO GO';
END
The IF statement might need to be in application code somewhere, as opposed to being in T-SQL, but I think you get the idea...
You might just need to connect to SQL Server to get the COUNT(*) being selected, and not need the variable declaration at all,
and using that returned value, make the decision in your application.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 6, 2015 at 9:51 am
It's preferred to use IF EXISTS instead of COUNT(*) to prevent unnecessary reads. EXISTS will stop on the first occurrence, while COUNT(*) will read all the needed rows.
IF EXISTS(SELECT MO.ProposedEvent
FROM MyOptions AS MO
INNER JOIN MyTimetable AS MT
ON MO.person = MT.person
AND MO.DayField = MT.DayField
AND MO.TimeField = MT.TimeField
WHERE MO.person = 'me')
BEGIN
PRINT 'HOUSTON... We have a problem...';
END
ELSE
BEGIN
PRINT 'GOOD TO GO';
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply