SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding all entries in table 1 that appears in table 2

    I have a table called offers, this is a list of holiday offers, each with a destination name.

    I have another table called alternatives. This is a list of alternative spellings for each destination.

    Each alternative is linked to a country table. A user selects a country and this numeric is used to filter results.

    The idea is to find all the alternative spellings for each destination to commit a query upon.

    IE:

    ; alternative spellings/resorts
    ID Country Resort Name
    --------------------------
    4 4 0 BARBADOS
    5 4 0 DOVER BEACH
    6 4 0 GRENADINES
    In this example all three alternative spellings/resorts are linked to country 4.

    The idea is to search the offers table with any/all of the alternative spellings.

    I've been able to do the following;

    Code:
    SELECT 
      p.alt_name FROM `resort_alt` as p 
    WHERE 
      p.alt_name 
    IN (SELECT DISTINCT(ph.DestinationName) FROM `offers` as ph) 
    AND p.alt_name != "" AND p.country_id =4
    But this only ever returns 1 alternative, when I need all of them, in addition if I try to add more fields (such as price, departuredate, etc) to the offers part of the query it craps out and complains.

    If I could do a select * on the offers part, that'd be really helpful.

    The concept is to build a list of alternatives so that I can do a holiday search.

    If this isn't clear enough, please let me know.

    Any help on this would be great.

  2. #2
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A bit more detail on this.

    If a specific country_id and resort_id is declared, then it will retrieve that particular, single alternative name is returned.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your example is really confusing, because you mention 3 tables, then show data for only one of them, then show a query which mentions an additional, different table

    finally, you say "the concept is to build a list of alternatives so that I can do a holiday search"

    why not just do the search all in one query rather than in two steps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry about the confusion.

    What I'm trying to do is the following.

    1. A user selects a country via a drop down.
    2. A user selects a resort (a child of country), including the phrase "all" via a drop down.
    3. This resort pulls a list of "alternative names" for the resort to search inside the Offers table.

    The phrase "All" means -- A user wants to search all resorts within a given country. In the DB this means grabbing all the alternative names for a given resort.

    I'll try to explain a little more about what is going on here...

    1. There is an offers table. There are over 40 fields in this table, rather than posting all of them, we can summarize it as:

    Code:
    id, DestinationName
    There is no relationship between this table and any of the following tables.

    2. There is a country table.

    This is a list of countries, from which a user will choose from. This is used later on.

    Code:
    id | country_name
    1  , SPAIN
    The relationship here is: ONE country has MANY resorts.

    3. There is a resorts table.

    This is a list of resorts to a country and has the following relationship.

    ONE resort has MANY alternative names.

    Code:
    ID | country_id | resort_name
    1 , 1, COSTA DA BRAVA
    4. Alternatives.

    Every resort could potentially have an alternative spelling, and is linked to a resort, in addition an alternative has a parent country.

    IE:
    Code:
    ID | country_id | resort_id | alt_name
    1  | 1  | 1 | Some alternative name
    1  | 1  | 0 | Another alternative
    Note in this example there are two rows that have the same country parent, and that one of them has a resort_id of 0 (zero).

    This is because there may be an alternative name with no specific resort parent.

    If the user chooses "All" in the resorts drop down, it will pick up both of the rows shown above.

    why not just do the search all in one query rather than in two steps?
    I cannot do the query in one step because of the following.

    1. The offers table is truncated and updated daily. I have no control over this.
    2. A resort may have an alternative spelling, and I need to grab all of these if a user searches "ALL"
    3. If a user selects a particular resort, then it grabs a specific spelling (row).

    I hope I've explained it a bit better, if you have further questions about what I'm trying to do, or perhaps I'm just confusing you - please let me know.

    Thanks.

  5. #5
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've figured out a solution, which seems to be retrieving accurate data and will be testing this solution.

    Thanks.


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
  •