Join three tables 2

[apex82]

I have the following sql statement:

<%
Dim id
id=Request.QueryString("id")

SQL = "SELECT * FROM System WHERE System.[System Code] = '" & id & "'"
	
Set rs = obj_CN.Execute(SQL, adBoolean)

%>

I need to join four tables instead of having just one.

System
System_ID(PK) | System Code | System description | CompanyID | CoverID | I System | Notes

System_Terms
Terms_ID(PK) | Terms Text | SystemID

System_PDF
PDF_ID | PDF data | SystemID

I’ve been trying to do this for some time but haven’t succeeded.

I think normally the join would be on the WHERE but I am using this to get the Querystring value??

Can anyone help?

Thanks.

 

[r937]

i see only three tables, where's the fourth one?

SELECT System.System_ID
     , System.System description 
     , System.CompanyID 
     , System.CoverID 
     , System.[I System]
     , System.Notes
     , System_Terms.[Terms Text] 
     , System_PDF.[PDF Data] 
  FROM System 
INNER
  JOIN System_Terms
    ON System_Terms.SystemID = System.SystemID
INNER
  JOIN System_PDF
    ON System_PDF.SystemID = System.SystemID
 WHERE System.[System Code] = '" & id & "'"

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[apex82]

Many thanks r937!

Below is the statement that works when there is a matching SystemID record in each of the tables.

How would I tweak it if they didn’t have the matching records so that part of the information is displayed?

SQL = "SELECT System.System_ID, System.[System Code], System.[System description], System.CompanyID, System.CoverID, System.[Installment System], System.Notes, System_Terms.[Terms Text], System_PDF.[PDF Data] FROM System " & _
"INNER JOIN System_Terms ON System_Terms.SystemID = System.System_ID " & _
"INNER JOIN System_PDF ON System_PDF.SystemID = System.System_ID " & _
 "WHERE System.[System Code] = '" & id & "'"

When there is no match I get the following error:

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

 

[gmmastros]

First, you should read up on joins. This is critically important information to have when working with relational databases.

Second, realize that you should change the inner join to a left join.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[apex82]

Thanks for all your help.

Now working with the left join.