joining 6 tables to each other by inner join

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

Development Tools

ASP.NET

joining 6 tables to each other by inner join

Author

Topic

ersenkavak
Starting Member

5 Posts

Posted - 2002-10-17 : 09:10:09

i wrote my problem at the subject.
I am trying to join 6 tables to make a general search...
i am trying for about two days..
if anybody can help, would u please write me the code?
thx

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-17 : 09:13:23


select
    <col_list>
from
    dbo.table1 a
    inner join dbo.table2 b
        on a.pk = b.fk
    inner join dbo.table3 c
        on b.pk = c.fk
    inner join dbo.table4 d
        on c.pk = d.fk
    inner join dbo.table5 e
        on d.pk = e.fk
    inner join dbo.table6 f
        on e.pk = f.fk

?

Jay White
{0}

ersenkavak
Starting Member

5 Posts

Posted - 2002-10-18 : 02:54:29

thx but
i received a syntax error by running this code...

ersenkavak
Starting Member

5 Posts

Posted - 2002-10-18 : 02:57:44

this is the error :

Syntax error (missing operator) in query expression 'a.ID = b.ID inner join connpasswords.uzman c on b.ID = c.ID inner join connpasswords.kurumsal d on c.ID = d.ID inner join connpasswords.donanim e on d.ID = e.ID inner join connpasswords.iletis f on e.ID = f.ID'.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-18 : 07:05:00

In order for anyone to debug your syntax, you will need to post your entire DML.

I'm curious to see your DDL too. Why do you have a non-dbo owned objects? Is this data somehow partitioned? Shouldn't this all be in one table?

Jay White
{0}

cbn
Starting Member

11 Posts

Posted - 2002-10-18 : 10:46:42

Here we go, you need to emulate this sql statement for youreself, i use mostly ADO so it may be just a little different, but still is a good reference:

SELECT Table1.col_list, Table2.col_list, Table3.col_list, Table4.col_list, Table4.col_list, Table6.col_list
FROM ((((Table1 LEFT JOIN Table2 ON Table1.a = Table2.b) LEFT JOIN Table3 ON Table2.b = Table3.c) LEFT JOIN Table4 ON Table3.c = Table4.d) LEFT JOIN Table5 ON Table4.d = Table5.e) LEFT JOIN Table6 ON Table5.e = Table6.f;

ersenkavak
Starting Member

5 Posts

Posted - 2002-10-18 : 11:16:33

hi,
thx for your time, jay and cbn
cbn i wrote your code, actually it is the one i have tried:
this is the complete database connection and sql and the error:
VT_YOL = SERVER.MAPPATH("veriler.mdb")
ACIL_SUSAM_ACIL = "DBQ=" & VT_YOL & ";Driver={Microsoft Access Driver (*.mdb)}"
Set ConnPasswords = Server.CreateObject("ADODB.Connection")
Set baginter = Server.CreateObject("ADODB.Recordset")
connpasswords.Open(ACIL_SUSAM_ACIL)
sql = "SELECT * from ((((inter left JOIN uzman ON inter.ID = uzman.ID) left JOIN kimlik ON inter.ID = kimlik.ID) left JOIN donanim ON inter.ID = donanim.ID) left JOIN iletis ON inter.Id = iletis.Id) left JOIN kurumsal ON inter.Id = kurumsal.Id "_

&"where kelime = '" & ilgi & "'" _
&"or kelime = '" & servis1 & "'" _
&"or kelime = '" & servis2 & "'" _
&"or kelime = '" & servis3 & "'" _
&"or kelime = '" & servis4 & "'" _
&"or kelime = '" & servis5 & "'" _
&"or kelime = '" & tel & "'" _
&"or kelime = '" & fax & "'" _
&"or kelime = '" & eposta & "'"

and error upon this sql is

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

/ara.asp, line 97

-------------------------------------------------------------
dear jay;
i don't know what ddl and dml is
but i can say that, i partitioned the columns because i believed that it would be faster. because there are somewhat 40 columns or so...
i don't know i am wrong or right now...
but it is for sure that i have to know how can i join 6 tables with inner join either if i use it in the site or not.

scottpt
Posting Yak Master

186 Posts

Posted - 2002-10-18 : 11:20:15

Access does not handle ansi inner join sytax.

cbn
Starting Member

11 Posts

Posted - 2002-10-18 : 11:35:17

Ok, here we go again: You cant use Select * when having multiple tables, relationships and Inner Join, you NEED to specify the exact fileds you are collecting from the exact tables.

Moreover, why are u using DBQ, use Jet:

Dim DatabasePath, Conn, RS

DatabasePath = Server.MapPath("./DBData/my_db.mdb")

Set Conn = server.createobject ("adodb.connection")

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabasePath & ";"

Set RS = Conn.Execute("SELECT Table1.kelime, Table2.igli, Table3.servis1, Table4.servis2, Table4.servis3, Table6.servis14
FROM ((((Table1 LEFT JOIN Table2 ON Table1.a = Table2.b) LEFT JOIN Table3 ON Table2.b = Table3.c) LEFT JOIN Table4 ON Table3.c = Table4.d) LEFT JOIN Table5 ON Table4.d = Table5.e) LEFT JOIN Table6 ON Table5.e = Table6.f where kelime LIKE '%" & request("field_from_form_1") & "%' or igli LIKE '%" & request("field_from_form_2") & "%' or servis1 LIKE '%" & request("field_from_form_2") & "%' &&& and so on &&& ORDER BY kelime ASC;")

NOW, you have all of your variables, do create the
<%do while not RS.EOF%>
RS("field")
<%RS.MoveNext
Loop
RS.Close%>

Output should be
------Table1.Field1---
cars
truck
suv's
---------------------

ETC....

Just forgot, when you need to go onto a new row, use the following syntax:

& _
NOT

_ &

Be very careful.

And Access can use Inner Join relationships.

Edited by - cbn on 10/18/2002 11:37:03

ersenkavak
Starting Member

5 Posts

Posted - 2002-10-22 : 08:33:17

hi cbn, thx again for your patience
i solve the case
when i write more than 4 columns in the from clause that is if the sql is as below :
sql = "SELECT kimlik.id,kimlik.soyad, kimlik.unvan, inter.konubir, interdis.konuiki, interdis.konuuc, inter.yontem, inter.yayin, donanim.servis1, donanim.servis2, donanim.servis3, donanim.servis4, donanim.servis5, iletis.tel, iletis.fax, iletis.url, iletis.adres, kurumsal.gorev, kurumsal.kurum, uzman.uzalan, uzman.uzalandiger, uzman.calkonubir, uzman.sektor, uzman.sektordiger from ((((inter left JOIN uzman ON inter.ID = uzman.ID) left JOIN kimlik ON inter.ID = kimlik.ID) left JOIN donanim ON inter.ID = donanim.ID) left JOIN iletis ON inter.Id = iletis.Id) left JOIN kurumsal ON inter.Id = kurumsal.Id "
sql = sql & " where kimlik.ad like '%"&kelime&"%'"
sql = sql & " or kimlik.soyad like '%"&kelime&"%'"
sql = sql &" or kimlik.unvan like '%"&kelime&"%'"
sql = sql &" or inter.interdis like '%"&kelime&"%'"
sql = sql &" or inter.konubir like '%"&kelime&"%'"
sql = sql & " or inter.konuiki like '%"&kelime&"%'"
sql = sql & " or inter.konuuc like '%"&kelime&"%'"
sql = sql & " or inter.yontem like '%"&kelime&"%'"
sql = sql & " or inter.yayin like '%"&kelime&"%'"

error comes
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.

/ara.asp, line 93
but if i rearrange the sql as :
sqldene = "SELECT uzman.sektordiger, kimlik.ad from ((((inter left JOIN uzman ON inter.ID = uzman.ID) left JOIN kimlik ON inter.ID = kimlik.ID) left JOIN donanim ON inter.ID = donanim.ID) left JOIN iletis ON inter.Id = iletis.Id) left JOIN kurumsal ON inter.Id = kurumsal.Id where uzman.sektordiger like '%" & kelime & "%'"
sqldene = sqldene & "or kimlik.ad like '%"&kelime&"%'"

then everything is ok....
a new peoblem arises...
how can i select all of the columns i need...

cbn
Starting Member

11 Posts

Posted - 2002-10-25 : 10:48:37

You need to specify all the columns by { , }

lets say:

Select Tbl1.col1, tbl1.col2 .. tbl1col{n}, tbl2.col1 .. tbl{n}col{n}
then your JOIN SQL Script

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources