SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select fields from multiple tables

    Hi,

    I want to select some thing in this table and something else in that table, in one query.

    How can I do that, what the query would be like ?

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    England
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm assuming your tables are related in some way, e.g.

    table: authors
    fields: id, firstName, lastName

    table: books
    fields: id, title, authorId

    Then to return the titles and author names for all books:

    Code SQL:
    SELECT b.title, a.firstName, a.lastName
    FROM authors a, books b
    WHERE b.authorId = a.id

    You can also do this with JOIN, but the above is perhaps more obvious.

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brilliant.

    I have looked over the internet for quite an example, I found something simmilar to yours, but could not comprehend the a. b. thing and how the whole thing connects each other.

    Through your example above, I am clear now, thank you very much.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    England
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what if 2 tables have no relating ?

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    England
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It depends on what you're trying to do with your query... can you give any more details of the tables & fields?

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Like flashparry said, combining two tables together in a query is called a join, and there are several types:
    http://dev.mysql.com/doc/refman/5.1/en/join.html

    If there's no relation between the two tables you want to join, the result will be that all rows of the first table will be combined with all the rows of the second query.

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    table1 : listing
    fields: id, title, price, detail, category

    table2: category_dictionary
    fields: abbreviation, meaning

    $query1 = "SELECT
    id
    , title
    , price
    , detail
    , category
    FROM listing
    WHERE id = $number";

    $query2 = "SELECT meaning
    FROM category_dictionary
    WHERE category = abbreviation";


    I want to combine two queries into one
    Last edited by runrunforest; Dec 11, 2008 at 04:24.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You can't. Not only do the 2 tables have no relation, but the queries themselves don't have a relation either.

  10. #10
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's hard to get the feel of your table structure but I would guess that this is what you are looking for:
    Code SQL:
    SELECT id, title, price, detail, category, meaning
    FROM listing l LEFT JOIN  category_dictionary c ON l.category = c.abbreviation


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
  •