Can someone help me with some SQL. I have 2 related tables on an Access 2k db. I have linked them using the ID and this all works OK in Access. After setting up the SQL and testing the web page the result I get is all the records in the secondary table appearing in the main for each main record. What I mean by this is, the main table contains company profile info and the second contains Contact info for each company. The result is each contact appears to work for each company.

This is my SQL

SELECT *
FROM clientconfigs, ClientContacts
WHERE CustomerName LIKE '%MMColParam%' AND clientconfigs.ID = ClientContacts.CustomerID

Also, does anyone know of any UD tutorials on related tables.

Thanks V much

Davy


Davy

Edited by - joelmartinez on 08/13/2001 16:22:56

I think you should try to use an inner join.<pre id=code><font face=courier size=2 id=code>SELECT *
FROM clientconfigs INNER JOIN ClientContacts
ON clientconfigs.ID = ClientContacts.CustomerID
WHERE CustomerName LIKE '%MMColParam%'</font id=code></pre id=code>

Joel Martinez

----------
set rs = conn.execute("SELECT answer FROM brain WHERE question = "& forumPost &"

Joel,

Thanks very much. I guess I had to remove the database join also. I've done this and it works fine. Do you know of any introductary SQL resources on the web or books even.

Thanks again,

Davy

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>Do you know of any introductary SQL resources on the web or books even.<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
to be perfectly honest with ya, if you know the concept of a table join (master detail relationship) then you can already do about 75% percent of the work on the web.

but I can certainly understand wanting a resource...
<b><i>Teach yourself SQL in 24 hours</i></b> is a great reference book to have... gets the basics and then some.

www.sqlteam.com is another great reference to use

Joel Martinez

----------
set rs = conn.execute("SELECT answer FROM brain WHERE question = "& forumPost &"

Joel,

I have another question on this SQL query. The SQL works well with existing clients that have contacts against them.

I want to be able to allow users to add new clients and contacts. I would like to allow the user to add a new company on one web page and then selects the newly added company on another page to add a new contact.

I am using the SQL below as I would like users to add contacts to new and existing clients from the same page. The SQL below selects only those clients that have contacts against them. Can you tell me if I need to add another statement that will display all clients with or without contacts? Presently when I try and add a new contact against a new company I get "ADODB.Field error '800a0bcd' Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record"


SELECT ID, ContactID, CustomerName, BusinessUnit, ContactName, ContactNumber, ContactJob, ContactEmail, ContactFax, ContactUserAlias, ContactLogin, ContactPermissions, FormCategory
FROM clientconfigs INNER JOIN ClientContacts ON clientconfigs.ID = ClientContacts.CustomerID, clientconfigs, ClientContacts
WHERE BusinessUnit LIKE '%MMColParam%' OR CustomerName LIKE '%MMColParam%'

Davy

if you want a list of all the rows, regardless of wether there's a matching record in the contacts table, then you use <b>LEFT OUTER JOIN</b> instead of inner join... just keep in mind that the fields from the other table will be null.

but if you're just getting that error because the person just doesn't have a contact, then you need to either put a repeat region on there, or do some code like this:<pre id=code><font face=courier size=2 id=code>&lt;%<b>IF NOT recordset1.eof THEN</b>%&gt;
HTML and database display code here
&lt;%<b>ELSE</b>
response.write("There are no contacts to display"
<b>END IF</b>%&gt;</font id=code></pre id=code>Hope that helps

Joel Martinez

----------
set rs = conn.execute("SELECT answer FROM brain WHERE question = "& forumPost &"

Hi Joel,

The LEFT OUTER JOIN works in the respect that it displays the list of contacts for a company(if the user searches using the exact name of the company). However, if the user searches on a generic name, e.g. "bank", then a list of companies appear with bank in the name obviously. From that list the user can link to a contact screen that contains a list of the contacts for that organisation. Unfortunately, each "bank" company contains the same contacts whether they work for that company or not. This happens with both the INNER JOIN and LEFT OUTER JOIN statements.

I need to ensure that the correct contacts appear against the appropriate company name. Can this be done with SQL?

SELECT ID, ContactID, CustomerName, BusinessUnit, ContactName, ContactNumber, ContactJob, ContactEmail, ContactFax, ContactUserAlias, ContactLogin, ContactPermissions, FormCategory
FROM clientconfigs LEFT OUTER JOIN ClientContacts ON clientconfigs.ID = ClientContacts.CustomerID, clientconfigs, ClientContacts
WHERE BusinessUnit LIKE '%MMColParam%' OR CustomerName LIKE '%MMColParam%'

Regards,

Davy

hmm, then if I'm understanding this correctly... you need a master detail set... When the person clicks on the link of one of the bank names for example... have the bankID (or whatever key you use) in the querystring.<pre id=code><font face=courier size=2 id=code>&lt;a href="bankdetail.asp?bankID=&lt;%=recordset1.fields("bankID".value%&gt;"&gt;Detail&lt;/a&gt;</font id=code></pre id=code>Then, you can refer to that querystring value in the recordset on the recieving page, or just use request.querystring("bankID"

hope that was it <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Joel Martinez [ ]
----------
set rs = conn.execute("SELECT answer FROM brain WHERE question = "& forumPost &"
'2nd place is just 1st Loser

A Master Detail set is what I have (below) hyperlinking from the company name of the search results page to the "listofcontacts.asp" page. This contains a LEFT OUTER JOIN for the company table and contact table.

The problem is if more than one company is listed in the seach results, the "listofcontacts.asp" page displays the right company and right contacts but also those contacts that work for each company listed in the search.

I need to be able to filter the contacts so that the "listofcontacts.asp" page displays only the contacts for the selected company.

&lt;A HREF="listofcontacts.asp?&lt;%= MM_keepURL & MM_joinChar(MM_keepURL) & "ID=" & RsSearchResults.Fields.Item("ID".Value %&gt;"&gt;&lt;%=(RsSearchResults.Fields.Item("CustomerName".Value)%&gt;&lt;/A&gt;

Davy

Then the problem lies in the criteria that the detail SQL statement is using... if you're using the same criteria is the original search, then of course it's going to pull up everything that the original search did.

just study the criteria of the detail page very closely, and make sure that you are only using the unique identifier of the item that you clicked on to do the search...

you'll get it <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

Joel Martinez [ ]
----------
set rs = conn.execute("SELECT answer FROM brain WHERE question = "& forumPost &"
'2nd place is just 1st Loser