SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    United States
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting based on two tables.

    I have two tables like so (fake data):

    department
    +-----+-------+--------+
    | id | name |address |
    +-----+-------+--------+
    | 1 | d1 | add1 |
    +-----+-------+--------+
    | 2 | d2 | add2 |
    +-----+-------+--------+

    Then I also have an entry table..

    +-----+-------+-----+-------+-------------+-----------+-------+
    | id | d_id | first | last | designation | extension | pager |
    +-----+-------+-----+-------+-------------+-----------+-------+

    The d_id entry matches the ids in the department table.


    What I am attempting to do, is a search in the department name and returning the entry results that share the department id that matches.

    I really have no idea on how to go about this. I've tried something like: SELECT * FROM `entry`, `department` WHERE `department.name` LIKE '%{$_POST['depart']}%' ORDER BY `d_id` ASC

    But I'm sure I need to use a Join statement or something.. Any help that anyone can lend will be great. All I need is the query that would return the necessary info.

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Try something like this.

    Code:
    SELECT * FROM department AS dep
      INNER JOIN entry AS ent ON ent.d_id = dep.id
    WHERE dep.name LIKE '%search%'
    ORDER BY dep.id DESC
    Yours, Erik.

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    United States
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It appears to work, thank you! I've never really understood Join statements that well. I know what they do, but can you explain the above query for me?

  4. #4
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by tmapm
    [...] can you explain the above query for me?
    Sure. What it does is to search through department for rows where name matches the given expression. When such a row is found, it will fetch all the rows from entry where d_id matches the current id.

    Yours, Erik.

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    United States
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It makes much more sense now. Thank you very much!


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
  •