SQL Help! - 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 2000 Forums

Transact-SQL (2000)

SQL Help!

Author

Topic

malhyp
Starting Member

21 Posts

Posted - 2006-05-11 : 05:11:09

Hey there, can anyone tell me if the following SQL will retreive the text version of the result Country (Australia) and Location (Victoria).

SELECT tblSuppliers.*, tblCountry.CountryName
FROM tblSuppliers S
INNER JOIN tblLocation L
ON (S.idLocation = L.idLocation)
INNER JOIN tblCountry C
ON (L.idCountry = c.idCountry)
WHERE UserName = 'MMColParam'

Tables.

.......tblSupplier
(A) idSupplier
(N) iLocation
(N) iCountry

.......tblLocation
(A) idLocation
(T) Location

.......tblCountry
(A) idCountry
(T) Country

(A) Auto Number
(N) Number
(T) Text

Location = Rome
Country = Italy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-11 : 05:16:13

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

malhyp
Starting Member

21 Posts

Posted - 2006-05-11 : 05:18:18

Sorry what sort of sample data?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-11 : 05:20:03

It will give an error.
suspect you want
SELECT S.idSupplier, C.Country, L.Location

Where do you get UserName from - it's not in any of the tables you have given.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 05:20:22

refer to here http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

malhyp
Starting Member

21 Posts

Posted - 2006-05-11 : 05:23:23

Username is a text column in tblSuppliers

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 05:23:51

somthing like this

Select idSupplier,Location,Country From tblSupplier Inner Join
tblLocation On iLocation = LocationID Inner join tblCountry On
CountryID = iCountry
Where
WHERE UserName = 'MMColParam

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

malhyp
Starting Member

21 Posts

Posted - 2006-05-11 : 05:28:28

This is the result that it gives me.

SELECT S.idSupplier, C.Country, L.Location
FROM tblSuppliers S INNER JOIN tblLocation L ON (S.idLocation = L.idLocation) INNER JOIN tblCountry C ON (L.idCountry = c.idCountry)
WHERE UserName = 'MMColParam'

Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression '(S.idLocation = L.idLocation) INNER JOIN tblCountry C ON (L.idCountry = c.idCountry)'.
/html/advertiser-basic-main.asp, line 62

<%
Dim rsUsers__MMColParam
rsUsers__MMColParam = "1"
If (Session("MM_UserName") <> "") Then
rsUsers__MMColParam = Session("MM_UserName")
End If
%>
<%
Dim rsUsers
Dim rsUsers_numRows
Set rsUsers = Server.CreateObject("ADODB.Recordset")
rsUsers.ActiveConnection = MM_connSeek_STRING
rsUsers.Source = "SELECT S.idSupplier, C.Country, L.Location FROM tblSuppliers S INNER JOIN tblLocation L ON (S.idLocation = L.idLocation) INNER JOIN tblCountry C ON (L.idCountry = c.idCountry) WHERE UserName = '" + Replace(rsUsers__MMColParam, "'", "''") + "'"
rsUsers.CursorType = 0
rsUsers.CursorLocation = 2
rsUsers.LockType = 1
rsUsers.Open() <---------------
rsUsers_numRows = 0
%>

malhyp
Starting Member

21 Posts

Posted - 2006-05-11 : 05:34:20

The main reason for this SQL is to draw the text name of the file rather than the numeric verions being (1). The result should be, Australia, Victoria.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 07:09:06

try runing the query from Query Analyser.. if you are getting error over there then its related to SQL Server else there is some formating problem in ur VB code..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources