Sql store procedure for searching two tables

hi my friends
i must create store procedure that search two table at the same time and show the answer with this discription:
on table have start date ,end date and code field and table2 have many fields but the most important one is Room field.
i must search to find any answer that show me all start date and end date values that the code field of table 2 not be the same of room field of table 2
for example i want all answers exept the answer that room=code
i write this:
.
.
.
where startdate<=@sdate and enddate>=edate and room<>code

but in this way it show me all the rows that have this condition and again all he row of table 2(all of code value)
i want to all my conditions used when show me the answers...
thanks

I'm not sure I understand your question.

You have 2 tables. Table 1 has the fields:
Start Date
End Date
Code

Table 2 has the field:
Room

I don't understand what you are trying to get out of the 2 tables. Can you give a small example of data in the 2 tables and what you want the query results to be?

room,code is a numeric field and startdate and enddate is a bigint field.
i want to have a result that show me all of table2 content exept that room of this table equal to code of table 1
let me show you an example:
table1:
start date ** end date ** code
123456 1265487 102
254687 545475 205
57787 545447 104


table2:
*** *** *** *** room
xxx xxxx xxx xxx 101
xxx xxxx xxx xxx 102
xxx xxxx xxx xxx 103
xxx xxxx xxx xxx 104
xxx xxxx xxx xxx 201
xxx xxxx xxx xxx 202
xxx xxxx xxx xxx 203
xxx xxxx xxx xxx 204
xxx xxxx xxx xxx 205

now i want to have this answer:
*** *** *** *** room
xxx xxxx xxx xxx 101
xxx xxxx xxx xxx 103
xxx xxxx xxx xxx 201
xxx xxxx xxx xxx 202
xxx xxxx xxx xxx 203
xxx xxxx xxx xxx 204

102,104,205 that exist in table2 was not shown.

plz answer me as soon as possible
thanks

OK Now I get it. Thanx.

Not a problem

SELECT <field1>, <field2>, ..., Room
FROM <table2> Left Outer Join (SELECT Code FROM <table1> WHERE startdate<=@sdate and enddate>=edate) As CdTbl
ON <table2>.Room = CdTbl.Code
WHERE CdTbl.Code Is Null

Enjoy!

thanks a lot !
the result is true but all of the fields like startdate and enddate is empty in gridview.
gridview just show me a code field .
i try to bind startdate and enddate for gridview and again the result is empty field.
how can i fix this?

They are blank becuase they are not part of the SELECT clause. In order to have them returned as part of the recordset you'll need to add them to the SELECT clause like so:

SELECT <field1>, <field2>, ..., Room, @sdate, edate
FROM <table2> Left Outer Join (SELECT Code, @sdate, edate FROM <table1> WHERE startdate<=@sdate and enddate>=edate) As CdTbl
ON <table2>.Room = CdTbl.Code
WHERE CdTbl.Code Is Null


Since sdate is a parameter you might be able to get away with just adding them to the first SELECT (the one with Room) you might want to simplify the expression by trying it.

i try this but when i add @sdate to the first select an error appear that say must declare the variable "@sdate"
im adding without @ and i have no error but again the fields is empty
...?