Join not working properly - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Join not working properly

Author

Topic

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-10-24 : 11:50:49

hello forum: i have the following statement joining two tables but it is not retreiving the correct info sometimes, some records do not show when search from the application (asp form) eventhough they are in the database.
I am using session values depending on user selection.

Recordset1.Source = "SELECT 7CMaster.DistDesc,7CMaster.Abbr,7CMaster.GNo,7CMaster.Key,7CMaster.Desc,7CMaster.Total,7CMaster.StartDesign,7CMaster.StartConstruction,tblSite.SKey,tblSite.Location1,tblSite.Zip FROM tblSite JOIN 7CMaster ON tblSite.SKey = 7CMaster.Key where 1=1 "

if session("zip") <> "" then
Recordset1.Source = Recordset1.source & "AND Zip='" & Session("zip") & "' "
end if

if session("project") <> "" then
Recordset1.Source = Recordset1.source & "AND GNo='" & Session("project") & "' "
end if

if Session("site") <> "" then
Recordset1.Source = Recordset1.source & "AND Key='" & Session("site") & "' "
end if

if Session("dateselectStart") <> "" then
Recordset1.Source = Recordset1.source & "AND StartConstruction>='" & Session("dateselectStart") & "' "
end if

if session("datedesignStart") <> "" then
Recordset1.Source = Recordset1.source & "AND StartDesign>='" & session("datedesignStart") & "' "
end if

if session("plusMillion") <> "" then
Recordset1.Source = Recordset1.source & "AND Total >=" & CCur(Session("plusMillion")) & " "
end if

if session("lessMillion") <> "" then
Recordset1.Source = Recordset1.source & "AND Total <=" & CCur(Session("lessMillion")) & " "
end if

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 11:56:21

As always when dealing with column names or table names starting with numeric characters, enclose them in brackets.

Recordset1.Source = "SELECT [7CMaster].DistDesc,[7CMaster].Abbr,[7CMaster].GNo,[7CMaster].Key,[7CMaster].Desc,[7CMaster].Total,[7CMaster].StartDesign,[7CMaster].StartConstruction,tblSite.SKey,tblSite.Location1,tblSite.Zip FROM tblSite JOIN [7CMaster] ON tblSite.SKey = [7CMaster].Key where 1=1 "

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 12:01:45

And you also need to format the critieras properly

if session("zip") <> "" then
Recordset1.Source = Recordset1.source & "AND Zip = '" & replace(Session("zip"), "'", "''") & "' "
end if 

if session("project") <> "" then
Recordset1.Source = Recordset1.source & "AND GNo = '" & replace(Session("project"), "'", "''") & "' "
end if

if Session("site") <> "" then
Recordset1.Source = Recordset1.source & "AND Key = '" & replace(Session("site"), "'", "''") & "' "
end if

if Session("dateselectStart") <> "" then
Recordset1.Source = Recordset1.source & "AND StartConstruction >= '" & FORMAT(Session("dateselectStart"),"yyyymmdd") & "' "
end if

if session("datedesignStart") <> "" then
Recordset1.Source = Recordset1.source & "AND StartDesign >= '" & format(session("datedesignStart"), "yyyymmdd") & "' "
end if

if session("plusMillion") <> "" then
Recordset1.Source = Recordset1.source & "AND Total >= " & replace(CCur(Session("plusMillion")), ",", ".") & " "
end if

if session("lessMillion") <> "" then
Recordset1.Source = Recordset1.source & "AND Total <= " & replace(CCur(Session("lessMillion")), ",", ".") & " "
end if

E 12°55'05.25"
N 56°04'39.16"

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-10-24 : 12:23:21

I did what you suggested but I believe that do not fixed the problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 12:43:42

Are you using a CASE SENSITIVE collation?

E 12°55'05.25"
N 56°04'39.16"

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-10-24 : 12:50:02

i am not familiar with that, i believe the problem is on the join and the condition where 1=1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 12:51:36

No.
What do you believe? Have you tested the suggestions yet?

quote:
Originally posted by alxtech

I did what you suggested but I believe that do not fixed the problem.

E 12°55'05.25"
N 56°04'39.16"

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-10-24 : 12:57:15

yes, i did you suggestion, but still search does not display all records.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 13:12:06

Have you noticed anything special about the records you expect to be delivered but are not?

E 12°55'05.25"
N 56°04'39.16"

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-10-24 : 13:18:14

no, if i search for all record from the asp form, all records show, but if i search for record with 7CMaster.GNo = 235 then results shows are record not found.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 13:32:19

Well, then there are no matching records in the tblSite table!
You are aware that you are using INNER JOIN?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 13:33:16

All these IF's, to which table are the filter belonging?
PLEASE ADD TABLE PREFIX!

We can't help you otherwise...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 13:35:39

If all the filtering belongs to the [7CMaster] table, change this statement...

...FROM tblSite JOIN [7CMaster] ON tblSite.SKey = [7CMaster].Key where 1 = 1 "
to
...FROM [7CMaster] LEFT JOIN tblSite ON tblSite.SKey = [7CMaster.Key] WHERE 1 = 1 "

If all the filtering belongs to the tblSite table, change this statement...

...FROM tblSite JOIN [7CMaster] ON tblSite.SKey = [7CMaster].Key where 1 = 1 "
to
...FROM tblSite LEFT JOIN [7CMaster] ON [7CMaster].Key = tblSite.SKey WHERE 1 = 1 "

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-24 : 13:51:21

And, please, make your code simpler, more accurate, safer and more secure and use parameters. NEVER concatenate data into a SQL string like that.

please read:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-10-24 : 15:19:55

I am still getting the same error, something strange is happening some records are not showing, but they are in the same table.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-24 : 16:12:33

Are you setting rowcount anywhere? Again, are any of the fields you are using in all CAPS?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-24 : 16:31:43

maybe if you should us the actual code you are using and the actual error message (if any) we could help you out. And, again, you should REALLY should use parameters.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources