Beginner: Searching DB Question

Results 1 to 2 of 2

Thread: Beginner: Searching DB Question

  1. #1
    Join Date
    Dec 1969

    Default Beginner: Searching DB Question

    What is the best way to search all tables/records of a relational data base for a user defined string?<BR><BR>Platform is MS Access. Data base is about 6 tables.<BR><BR>My real problem is searching for foreign key descriptions in a table. For example, I want to search for all books with an author name of Smith. However, the book table includes an AUTHORID, not the AUTHORNAME. The AUTHORNAME is stored in an authors table.<BR><BR>So, SELECT * from BOOKS where BOOKS.AUTHORID = &#039;Smith&#039; is wrong. But, how should I do this for a data base that contains, say 6 look up tables for the BOOKS table (AUTHORID, CATEGORYID, etc.).<BR><BR>Thanks.

  2. #2
    Join Date
    Dec 1969

    Default Just a JOIN

    You can code this using INNER JOIN syntax, but for this many tables I&#039;d just use "implicit join" syntax. Here it is for 4 tables:<BR><BR>SELECT B.Title, A.AuthorName, C.CategoryName, P.PublisherName<BR>FROM Books AS B, Authors AS A, Categories AS C, Publishers AS P<BR>WHERE A.authorID = B.authorID<BR>AND C.categoryID = B.categoryID<BR>AND P.publisherID = B.publisherID<BR>ORDER BY Title, AuthorName, CategoryName<BR><BR>[Or any other ORDER, of course.]<BR><BR>NOW...Now what do you do if the book has a *DOZEN* authors? As many technical books do?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts