Professional Web Applications Themes

Help with SQL query.... I'm sure there is a way! - MySQL

Hi, I'm helping a friend to build a system to handle advertisments from local dealers in different regions/cities. In this system, an advertisment belongs to a region and is connected to one or more categories (in this context called searchwords). One of the things that I need to extract is a list of all categories that have 1 or more adds targeted for a specific region. These are the tables: TABLE regions - contains the regions that a specific add can belong to id name TABLE adds - contains the advertisements id regionid (corresponds to regions.id) text TABLE searchwords - ...

  1. #1

    Default Help with SQL query.... I'm sure there is a way!

    Hi,

    I'm helping a friend to build a system to handle advertisments from
    local dealers in different regions/cities. In this system, an
    advertisment belongs to a region and is connected to one or more
    categories (in this context called searchwords).

    One of the things that I need to extract is a list of all categories
    that have 1 or more adds targeted for a specific region. These are the
    tables:

    TABLE regions - contains the regions that a specific add can belong to
    id
    name

    TABLE adds - contains the advertisements
    id
    regionid (corresponds to regions.id)
    text

    TABLE searchwords - contains searchwords that each add can be
    categorized as
    id
    name

    TABLE adds_searchwords - specifies the categories that a specific add
    belongs to
    addid (corresponds to adds.id)
    searchwordid (corresponds to searchwords.id)


    So, from a known regions.id I need to get a list of all
    searchwords.name that have at least one add with a corresponding
    searchwordid-addid relationship. Is this possible in a single query?

    Anyone that can help me?

    TIA,
    Björn

    bjorn.bergstrom@jadestone.se Guest

  2. #2

    Default Re: Help with SQL query.... I'm sure there is a way!

    To preserve my sanity when thinking this through I renamed all
    occurrences of 'add' to 'ad' and 'searchword' to 'keyword'

    This is untested

    SELECT k.id,k.keyword
    FROM keywords k, regions r, adds a, ads_keywords ak
    WHERE k.keyword_id = ak.keyword_id
    AND ak.ad_id = a.ad_id
    AND a.region_id = r.region_id
    AND r.region_id = 1;


    [email]bjorn.bergstromjadestone.se[/email] wrote:
    > Hi,
    >
    > I'm helping a friend to build a system to handle advertisments from
    > local dealers in different regions/cities. In this system, an
    > advertisment belongs to a region and is connected to one or more
    > categories (in this context called searchwords).
    >
    > One of the things that I need to extract is a list of all categories
    > that have 1 or more adds targeted for a specific region. These are the
    > tables:
    >
    > TABLE regions - contains the regions that a specific add can belong to
    > id
    > name
    >
    > TABLE adds - contains the advertisements
    > id
    > regionid (corresponds to regions.id)
    > text
    >
    > TABLE searchwords - contains searchwords that each add can be
    > categorized as
    > id
    > name
    >
    > TABLE adds_searchwords - specifies the categories that a specific add
    > belongs to
    > addid (corresponds to adds.id)
    > searchwordid (corresponds to searchwords.id)
    >
    >
    > So, from a known regions.id I need to get a list of all
    > searchwords.name that have at least one add with a corresponding
    > searchwordid-addid relationship. Is this possible in a single query?
    >
    > Anyone that can help me?
    >
    > TIA,
    > Björn
    strawberry Guest

  3. #3

    Default Re: Help with SQL query.... I'm sure there is a way!

    Actually, FWIW I'd strcture the database like this:

    ads (ad_id,ad)
    regions(region_id,region)
    keywords(keyword_id,keyword)
    ads_regions(ad_id,region_id)
    ads_keywords(ad_id,keyword_id)

    and then use a query like the following bring back the results

    SELECT k.keyword_id, k.keyword
    FROM keywords k, ads_keywords ak, ads_regions ar, regions r
    WHERE k.keyword_id = ak.keyword_id
    AND ak.ad_id = ar.ad_id
    AND ar.region_id = r.region_id
    AND r.region = $region

    Notice how the ads table itself no longer forms a part of the query.
    Also an ad can belong to more than 1 region (Rome AND Italy AND Europe
    for instance).

    HIH


    strawberry wrote:
    > To preserve my sanity when thinking this through I renamed all
    > occurrences of 'add' to 'ad' and 'searchword' to 'keyword'
    >
    > This is untested
    >
    > SELECT k.id,k.keyword
    > FROM keywords k, regions r, adds a, ads_keywords ak
    > WHERE k.keyword_id = ak.keyword_id
    > AND ak.ad_id = a.ad_id
    > AND a.region_id = r.region_id
    > AND r.region_id = 1;
    >
    >
    > [email]bjorn.bergstromjadestone.se[/email] wrote:
    > > Hi,
    > >
    > > I'm helping a friend to build a system to handle advertisments from
    > > local dealers in different regions/cities. In this system, an
    > > advertisment belongs to a region and is connected to one or more
    > > categories (in this context called searchwords).
    > >
    > > One of the things that I need to extract is a list of all categories
    > > that have 1 or more adds targeted for a specific region. These are the
    > > tables:
    > >
    > > TABLE regions - contains the regions that a specific add can belong to
    > > id
    > > name
    > >
    > > TABLE adds - contains the advertisements
    > > id
    > > regionid (corresponds to regions.id)
    > > text
    > >
    > > TABLE searchwords - contains searchwords that each add can be
    > > categorized as
    > > id
    > > name
    > >
    > > TABLE adds_searchwords - specifies the categories that a specific add
    > > belongs to
    > > addid (corresponds to adds.id)
    > > searchwordid (corresponds to searchwords.id)
    > >
    > >
    > > So, from a known regions.id I need to get a list of all
    > > searchwords.name that have at least one add with a corresponding
    > > searchwordid-addid relationship. Is this possible in a single query?
    > >
    > > Anyone that can help me?
    > >
    > > TIA,
    > > Björn
    strawberry Guest

Similar Threads

  1. Query of Query to select a title first letter
    By Conti in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 18th, 11:42 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139