Search query from multiple tables - FROM clause

Results 1 to 2 of 2

Thread: Search query from multiple tables - FROM clause

  1. #1
    Maurice PH Hendriks Guest

    Default Search query from multiple tables - FROM clause

    I have 3 tables<BR><BR>topics<BR>---------<BR>topicid<BR>topic<BR><BR>areas<BR>---------<BR>areaid<BR>topicid<BR>area<BR><BR>whatisit<BR >---------<BR>whatisitid<BR>topicid (FK)<BR>areaid (FK)<BR><BR>I want to build a simple search page that allows users to enter a string which will search either of the three tables and return:<BR><BR>Topic(with a topicid and areaid link to the whatisit page), Area, WhatisIt<BR><BR>How do I formulate the SQL SELECT Query? I tried to join the tables in the FRom clause but end up with 0 results. For the SELECT and WHERE clauses I have:<BR><BR>SELECT tblLegalTopics.LegalTopicID, tblLegalTopics.LegalTopic, tblLegalAreas.LegalAreaID, tblLegalAreas.LegalArea, tblLegalAreaWhatIsIt.WhatIsIt FROM ....................<BR><BR>WHERE tblLegalTopics.LegalTopic LIKE &#039;%" & Request.Form("searchStr") & "%&#039; OR tblLegalAreas.LegalArea LIKE &#039;%" & Request.Form("searchStr") & "%&#039; OR tblLegalAreaWhatIsIt.WhatIsIt LIKE &#039;%" & Request.Form("searchStr") & "%&#039;"

  2. #2
    Join Date
    Dec 1969

    Default JOIN needed

    You can either code an *explicit* JOIN, using an INNER JOIN clause (well, two clauses, when you join 3 tables) or you an use an *implicit* JOIN.<BR><BR>Personally, I find the implicit join easier to read and understand.<BR><BR>SELECT ...fields...<BR>FROM tblLegalTopics AS T, tblLegalAreas AS A, tblLegalAreaWhatisit AS W<BR>[hl="yellow"]WHERE W.AreaID = A.AreaID<BR>AND A.TopicID = T.TopicID[/hl]<BR>AND T.LegalTopic LIKE &#039;%...etc ...<BR><BR>You don&#039;t have to use table "aliases" (the AS clauses), of course. I just do it to make the SQL smaller and hopefully more readable.<BR><BR>I note that you have both a topicID and areaID in the "whatisit" table. That&#039;s really a mistake. If you ever changed what topic a given area belonged to, then if you forgot to change the topicID field in the whatisit table, you&#039;d have a referential integrity problem. Since you can *alway* derive the topicID (and thus the associated topic.*) from the areaID, you&#039;re probably better off leaving topicID out of the whatisit table. (If this is data you *KNOW* with certainty will never change, then it&#039;s not important, but...)<BR><BR>Do you see how the "implicit" nature of the joining works?<BR><BR> [hl="yellow"]WHERE W.AreaID = A.AreaID<BR> AND A.TopicID = T.TopicID[/hl]<BR><BR>That says, when the *rest* of the WHERE clause limits which W (whatisit) records to look at, then *also* limit which A (areas) records to get [only the one that matches that particular W record] and then, again, *also* limit which T (topics) records [again, only the one that matches the particular A record].<BR><BR>If you prefer an explicit JOIN:<BR><BR>SELECT ...fields...<BR>FROM tblLegalTopics AS T INNER JOIN <BR> (tblLegalAreas AS A INNER JOIN tblLegalAreaWhatisit AS W ON A.AreaID = W.AreaID)<BR> ON T.TopicID = A.TopicID<BR>WHERE T.LegalTopic LIKE &#039;%...etc ...<BR><BR>Find a decent SQL book/reference page and study up. Have fun.<BR><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