Best way to obtain data from multiple tables?

[college_amy]

What is the most efficient way to obtain data from multiple tables on one page?

 

[kahlua001]

Database tables? html tables? datatables? What are you trying to do?

 

[college_amy]

I suppose I should have clarified that... I have a database with 4 tables in it. I want to display related results from the db tables.
Example. All the tables are joined through Access on an "ID" field.

 

[kahlua001]

Use Joins in your sql statement.

 

[college_amy]

I know the proper way to join 2 tables, but how do you join more than two in one sql statement? Can you show me an example?

 

[samsmithnz]

select * from table1 inner join table2 
on table1.id=table2.id
inner join table3 on table1.id=table3.id

 

[college_amy]

Does that only obtain the information from table1 or will that obtain ALL the information from ALL the tables?

 

[samsmithnz]

all tables. If you want only specific information, change the select part to

select table1.field1, table2.field1 from ...

 

[college_amy]

Thanks!! I will try it now.

 

[college_amy]

Do I need to reference the item in the page as:
<%# Container.DataItem(DBReader("table.field")) %> OR
<%# Container.DataItem("field") %> OR
<%# Container.DataItem("table.field") %>
Or some other way?

 

[samsmithnz]

it depends on whether or not the same field appears in the same table. For example if you want to display just you're ID's you could use as statements:

SELECT table1.id as Table1_ID, table2.id as Table2_ID FROM ...

 

[college_amy]

the only field in all 4 tables that has the same name is the "ID" field.
here is how my tables are set up:
table1:
ID, Loginname, Password, Email
Table2:
ID, address, city, state, website
Table3:
ID, services, affiliations, photo
Table4:
ID, memoblock1, memo2, memo3
(the memoblock areas are large text fields where the person in the database can store details about themselves or their services)

What I want to do is allow a user who searches and finds a service provider to click on the service providers name and get more detailed information (which is where the 4 tables joined comes in).
On the detailed information page I want to have all the information from all the tables be displayed on that one page.

So .. how do I do that?

 

[samsmithnz]

Its not that hard. build it up. First join all the tables togeather. Then show the items you need to show.

use a Where statement to filter data, and a order by to sort it.

 

[college_amy]

I am getting the following error:
Syntax error (missing operator) in query expression 'Login.LoginID = billing.LoginID INNER JOIN info ON Login.LoginID = info.LoginID INNER JOIN description ON Login.LoginID = description.LoginID'.

Here is my code:
SQLString = "SELECT * FROM Login INNER JOIN billing ON Login.LoginID = billing.LoginID INNER JOIN info ON Login.LoginID = info.LoginID INNER JOIN description ON Login.LoginID = description.LoginID"
DBCommand = New OleDbCommand(SQLString, DBConnection)

'-- Create a recordset of selected records from the database
DBReader = DBCommand.ExecuteReader()

The error points to the above line...
Do you see anything that I am missing?

 

[samsmithnz]

I forgot, you have to use brackets

so

SELECT * FROM (((Login INNER JOIN billing ON Login.LoginID = billing.LoginID) INNER JOIN info ON Login.LoginID = info.LoginID) INNER JOIN description ON Login.LoginID = description.LoginID)

or somethingl ike that.

 

[college_amy]

That fixed the problem, but now I have a new one... geesh...
Some fields don't have any info in them and when I try to retrieve them through the reader I get a Cast from type 'DBNull' to type 'String' is not valid.
How do I get it to work even though some fields are null?

 

[samsmithnz]

use the isdbnull() function to check if they are null before you read them.

 

[college_amy]

Can you point me to an online tutorial or info on the isdbnull() function to learn about it? I have ASP.NET unleashed and it isn't referenced in there....
Thanks for ALL you help!! I appreciate it greatly.

 

[samsmithnz]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnull.asp

 

[college_amy]

Great! Thanks!!