SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    query information from two tables

    Hello everyone!

    Assuming I have a users table and an articles table with the following structure

    Code:
    users (id, name)
    articles (id, title, created_by)
    created_by contains the id of the user that created the article, and I want to create a table that contains all articles so I can display

    article title, article id, created by name

    For the first two I can just query the articles table and will have the records to do whatever I want with them, for the created by name I have to query the database with the ids of the users I need the names and then do whatever I want with the data, at least the way I am doing it right now is query the database twice, once for the articles and the second time for the needed users' names, if there is an easier way can someone please point me in the right direction by giving me a sample?
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tlacaelelrl View Post
    ...I want to create a table that contains all articles
    you don't want to create a table -- that would be storing redundant data, a real headache in the making

    you just want to retrieve the data

    may i introduce you to the concept of a join query
    Code:
    SELECT articles.title
         , articles.id
         , users.name
      FROM articles 
    INNER 
      JOIN users 
        ON users.id = articles.created_by
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you don't want to create a table -- that would be storing redundant data, a real headache in the making

    you just want to retrieve the data
    I should have clarified, when I said I want to create a table I meant display the records in a table and not create an additional table in the database, the actual reason I was asking about this is to avoid redundant data

    may i introduce you to the concept of a join query
    Code:
    SELECT articles.title
         , articles.id
         , users.name
      FROM articles 
    INNER 
      JOIN users 
        ON users.id = articles.created_by
    Thank you I will try this
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  4. #4
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I just ran the query and do get the correct results, however when I want to use the results how could I differentiate one from another one, since users have a column called id and the same goes for the articles, then I get a result with two columns called id?
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tlacaelelrl View Post
    ... I get a result with two columns called id?
    not from the query i gave you

    however, to solve this problem, you would use column aliases
    Code:
    SELECT articles.title
         , articles.id AS articles_id
         , users.id    AS users_id
         , users.name
      FROM articles 
    INNER 
      JOIN users 
        ON users.id = articles.created_by
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    not from the query i gave you

    however, to solve this problem, you would use column aliases
    Code:
    SELECT articles.title
         , articles.id AS articles_id
         , users.id    AS users_id
         , users.name
      FROM articles 
    INNER 
      JOIN users 
        ON users.id = articles.created_by
    Thank you so much I was using AS but the wrong way, I was doing this:

    Code:
    SELECT * 
    FROM e70n2_tlakcalendarcategories
    INNER 
      JOIN e70n2_users AS user_name
        ON e70n2_users.id  = e70n2_tlakcalendarcategories.created_by
    So "AS" should be used before the INNER JOIN right?
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  7. #7
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This is the query I am trying to run but I am getting an error,

    Code:
    SELECT * 
    FROM e70n2_tlakcalendarcategories,
    e70n2_users.id AS user_id,
    e70n2_users.username AS user_name,
    INNER 
      JOIN e70n2_users
        ON e70n2_users.id  = e70n2_tlakcalendarcategories.created_by
    basically I am trying to get all from the table e70n2_tlakcalendarcategories and match the username from the table e70n2_users using the e70n2_tlakcalendarcategories.created_by
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  8. #8
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I finally got it, I just need to practice more doing this kind of thing to better understand it, in my previous query I had an extra comma, thank you for all your help
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tlacaelelrl View Post
    in my previous query I had an extra comma
    that wasn't all that was wrong with it, but i'm glad you were able to fix it yourself

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    inner join with the same table

    Earlier I asked how I could join two tables to match records from two tables and was able to pull the records that I wanted however I have a list of categories and each category can belong to another category, so lets say category 1 can have many subcategories so I need to get the name of the parent category, if I have a table with the structure:

    Code:
    categories ( id=the id of the category, name=the name of the category, parentcategory=the id of the parent category stored in this same table )
    SO I want to run a query that gives me id, name, parentcategoryid and parentcategoryname, I ran this query

    Code:
    SELECT e70n2_tlakcalendarcategories . * , e70n2_users.id AS user_id, e70n2_users.username AS created_un
    FROM e70n2_tlakcalendarcategories
    INNER JOIN e70n2_users ON e70n2_users.id = e70n2_tlakcalendarcategories.created_by
    INNER JOIN e70n2_tlakcalendarcategories ON e70n2_tlakcalendarcategories.id = e70n2_tlakcalendarcategories.catid
    but I get: Not unique table/alias: 'e70n2_tlakcalendarcategories'

    I am trying to get the parent's category name with an alias so I can then identify it properly
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  11. #11
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I have some progress on it but I am still not getting the results so I am still missing something, I now don't get an error but I am not getting the category's name, this is the query so far:

    Code:
    SELECT 
    	e70n2_tlakcalendarcategories.*,
    	e70n2_users.id AS user_id,
    	e70n2_users.username as created_un
    FROM e70n2_tlakcalendarcategories 
    INNER JOIN e70n2_users ON e70n2_users.id = e70n2_tlakcalendarcategories.created_by
    INNER JOIN e70n2_tlakcalendarcategories catname ON e70n2_tlakcalendarcategories.id = e70n2_tlakcalendarcategories.catid
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  12. #12
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Okay, I finally got it, bellow is the query in case someone comes looking for the same thing

    Code:
    SELECT 
    maintable . * , 
    secondarytable.id AS user_id, 
    secondarytable.username AS created_un, 
    maintablealias.title AS catname
    FROM maintable
    INNER JOIN 
    secondarytable ON secondarytable.id = maintable.created_by
    INNER JOIN 
    maintable maintablealias ON maintablealias.id = maintable.catid
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.


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
  •