SQL and Inner Join

[softboy12]

Hi all

i have a MS SQL Customer DB's which includes 3 Relations
(created with the help of the Diagram Wizard in the Enterprise Manager)


The relations are configured as follow:

Field "Pointing" in the table "Kunden" is linked to "ID_point" in the table "Pointings"

Field "Subdomain" in the table "Kunden" is also linked
to "ID_Sub" in the table "Subdomains"

and last but not least

Field "Synonym" in the table "Kunden" is also linked
to "ID_Synonym" in the table "Synonyme"

after that i use the SQL Statement:
-----------------------------------------------------------

"SELECT * FROM [Kunden] INNER JOIN [Pointings].[ID_point] WHERE [ID]=" & tkey
----------------------------------------------------------

and if i open the Page in the browser the following error
occurs:
------------------------------------------------------------
Microsoft OLE DB Provider for SQL Server- Fehler '80040e14'

Wrong Syntax near of Where Keyword

/Kundenview.asp, line 88
------------------------------------------------------------

can somebody help me please?

thank you

E.Altherr

 

[amorous]

Try this:

"SELECT * FROM [Kunden] INNER JOIN [Pointings].[ID_point]
ON [Kunden].[Pointing]=[Pointings].[ID_point]
WHERE [ID]=" & tkey

-VJ

 

[softboy12]

Thanks

but this doesn't work

the following error in the browser occurs
-------------------------------------------

Invalid Objekname 'Pointings.ID_point'.
-------------------------------------------

 

[amorous]

OOPS small typo

Try this:

"SELECT * FROM [Kunden] INNER JOIN [Pointings]
ON [Kunden].[Pointing]=[Pointings].[ID_point]
WHERE [ID]=" & tkey

-VJ

 

[softboy12]

Sorry it dosen't work

see code below

-----------------------------------------------------------

tkey = "" & key & ""
strsql = "SELECT * FROM [Kunden] INNER JOIN[Pointings] ON [Kunden].[Pointing]=[Pointings].[ID_point] WHERE [ID]=" & tkey
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn
If rs.Eof Then
Response.Clear
Response.Redirect "Kundenlist.asp"
Else
rs.MoveFirst
End If

-----------------------------------------------------------

if i use INNER JOIN it produces a endless loop --> causes to call the Response.Redirect "Kundenlist.asp"

the only thing which works is

SELECT * FROM [Kunden] LEFT JOIN...." but then it shows no entrys?

many thanks for your help

 

[hmckillop]

Have you tried executing the code
SELECT * FROM [Kunden] INNER JOIN[Pointings] ON [Kunden].[Pointing]=[Pointings].[ID_point] WHERE [ID]=" & tkey

in Query analyser, obviously replacing the WHERE [ID] = " & tkey with an actual value.

I dont see how it creates an endless loop unless your page kundenlist.asp redirects to kundenview.asp (I assum kundenview.asp is the page with the above code on it.)


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[softboy12]

in Query Analyzer INNER JOIN produces an emtpy result

----------------------------------------------------------

SELECT * FROM [data].[dbo].[Kunden] INNER JOIN [data].[dbo].[Pointings] ON [data].[dbo].[Kunden].[Pointing]=[data].[dbo].[Pointings].[ID_point]


results in:



(0 row(s) affected)

and after i changed it to:

[Kunden] RIGHT JOIN


it seem's to work

(849 row(s) affected)

 

[hmckillop]

Not to state the obvious but Right join will give you all the data in [Pointings] table and where there are matches for the [kunden] table it return them also, otherwise nulls.
is this what you require?


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.