Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with query

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-04-06 : 10:43:40

Here what i need to do!
I am doing application in .net and Sql server
The appliaction is simple there a treeview with checkbox.
There a parant node "Region" and child node "Ecole"

Ecole = School

Each school is link to a region
When the user submit the form all the school that are check is inserted in the tblRegionEcole

Here my table

The user check all the check box he want. He can check a single school or if he check the parent node it will check all the school for him.
When he click submit all the school are inserted in the database.
Then the part i want to do is sent email with the info

So my query look somthing like this

SELECT		tt.intTempeteId,tt.datDateTempete,te.strNom,reg.strNom
FROM		tblTempete as tt
INNER JOIN	tblRegionEcole as re ON tt.intTempeteId = re.intTempeteId
INNER JOIN	tblEcole as te ON re.intEcoleId = te.intEcoleId
INNER JOIN  tblRegion as reg ON reg.intRegionId = te.intRegionId
WHERE		tt.datDateTempete = '2011-04-05'

So now here what i looking to do!

Instead of using the query above, i want to create a query that will check if
All the "te.strNom" school from a "reg.strNom
" region is in the result

Ex:

If all the School from a "Région de Shédiac" region is in the result show me the region
is close.
But if only 3 school out 6 is in the query show me all the school in the query.
Number 6 can vary depenging from the region

So if you look at the second picture it will return something like this
Because in "Region de Shediac" there a total of 6 school in the table
And for "Région de Saint-Louis de Kent" the user only check 2 school so the query will return

The result i want

Region de shediac
École Soleil Levant
École La Marée Montante

Tks for your help in advance
Do you think it possible!

Luc

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-06 : 12:08:01

Hi Luc,

i just want to clarify:

- if user clicks on one school, you want to return all the schools from region? or
- if user clicks on one region and there is vacancy in one of the schools from this region, return name of this region?

sorry, i got confused :(

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-04-06 : 12:22:41

Sorry i did't explain my self correctly!

The goal of my apps is to send an email to tell if the certains school are close.
The email will show all the school that are close.
But we have a lot school.
Each shool is link to a region

-In the email if all the school from a region are close "check" then in the message will say that this region is close
-But if only 2 or 3 school is close from region it will list all the school

In the treeview each school is link the unique ID
That the id that is inserted in the databse

So the region is only there because it faster for the user
If the user click on a region all the school selected
But in the database it will insert the unique id for the school

I need to find a way to

Do understand more what i am trying to do?

Tks for your help!

I hope i explain my self the right way! :)

quote:
Originally posted by slimt_slimt

Hi Luc,

i just want to clarify:

- if user clicks on one school, you want to return all the schools from region? or
- if user clicks on one region and there is vacancy in one of the schools from this region, return name of this region?

sorry, i got confused :(

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-04-06 : 12:28:08

Here i hope it help!

As you can see
Ex:

Region de Shediac is all check
And
Region de Bouctouche there only 2 schools that are check

All the id from the school that are check are inserted in th DB

So want a query that will return

If it not possible in one query is it possible to do 2?
One that show if a region is close
One if some school are close?

Any idea?

Region Shdeiac
Ecole Camille-Vaoutour
Ecole Mont-Carmel

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-04-06 : 12:54:56

Any idea how to start my query?
Any help would be appreciate
Tks in advance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources