SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with where in join

    I have a query that is as follows:
    SELECT inst.id, inst.name, inst.full_path, inst.db_name, sr.repo_path FROM (ws_instances inst) JOIN ws_snapshot_repos sr ON sr.id = inst.repository_id

    Which returns:
    id name full_path db_name repo_path
    13 sugar52ace c:\\xampp\\htdocs\\sugar52ace sugar52ace C:\\xampp\\htdocs\\wapsnap\\snapshots
    14 sugar52bce c:\\xampp\\htdocs\\sugar52bce sugar52bc3 C:\\xampp\\htdocs\\wapsnap\\snapshots

    It joins two tables which is pretty straight forward. The problem lies when I add the where clause.

    If I use the following then I get zero rows:

    SELECT inst.id, inst.name, inst.full_path, inst.db_name, sr.repo_path FROM (ws_instances inst) JOIN ws_snapshot_repos sr ON sr.id = inst.repository_id WHERE `inst`.`id` = '12'

    Any ideas what is happneing?

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, since neither id in your above example is 12 then neither of those rows will be returned.

    by the way lose the ( ) around your table name.

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I canonly add that setting out your query as below is much easier to read, especially if it is a larger one.

    Code mysql:
    SELECT inst.id
         , inst.name
         , inst.full_path
         , inst.db_name
         , sr.repo_path 
         FROM ws_instances inst
         JOIN ws_snapshot_repos sr 
           ON sr.id = inst.repository_id

    much easier to see if commas have been missed or aliases left out, for example.
    bazz

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    I canonly add that setting out your query as below is much easier to read, especially if it is a larger one.
    Teacher's pet!

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    - I've never been called that before!!

    bazz


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
  •