SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Query not getting proper data

    Hello,

    I have this simple query and its not getting the data of the category table. Any suggestions ?

    Code:
    SELECT products.*, categories.* FROM products
            LEFT JOIN categories ON products.prod_catg_id = categories.catg_id
            WHERE categories.catg_status = '1' ORDER BY RAND(), products.prod_price LIMIT 0, 4
    Thanks.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    With that WHERE condition, the LEFT JOIN becomes an INNER JOIN. So what do you mean by 'its not getting the data of the category table' ? If it isn't getting the category table data, it shouldn't be getting any data.

    If you really need a LEFT JOIN, then try putting the condition in the ON clause instead of in the WHERE clause.

  3. #3
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    With that WHERE condition, the LEFT JOIN becomes an INNER JOIN. So what do you mean by 'its not getting the data of the category table' ? If it isn't getting the category table data, it shouldn't be getting any data.

    If you really need a LEFT JOIN, then try putting the condition in the ON clause instead of in the WHERE clause.
    Hello,

    Thanks for your response..i have modified my query:

    Code:
    SELECT products.*, categories.* FROM products
            LEFT JOIN categories ON products.prod_catg_id = categories.catg_id
            ORDER BY RAND(), products.prod_price LIMIT 0, 4
    But even after removing the WHERE clause I am still not getting the data from the categories table but in both the cases the data from products table is coming fine.

    Please suggest.

    Thanks.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Can you give some example data from both tables, the result you want and the result you are getting right now?

  5. #5
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Can you give some example data from both tables, the result you want and the result you are getting right now?
    Hi,

    LOL very sorry! My bad. I was executing the wrong query lol!

    Thanks.

  6. #6
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    Sorry to bother again. I have now a new problem with the same tables. I have now included sub-categroies also in the categories table. I have attached the picture which shows the categories table.

    I am executing the following query to get the results but its not returning any data at all.

    Code SQL:
    SELECT products.*, categories.*, categories.* FROM products
            LEFT JOIN categories ON products.prod_catg_id = categories.catg_id
            LEFT JOIN categories ON products.prod_sbcg_id = categories.catg_id
            WHERE categories.catg_status = '1' ORDER BY RAND(), products.prod_price LIMIT 0, 10

    Please help.

    Thanks.
    Attached Images Attached Images

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you want to join to the same table more than once, you ~must~ use table aliases

    e.g.
    Code:
    LEFT JOIN categories AS maincat ON maincat.catg_id = products.prod_catg_id 
    LEFT JOIN categories AS subcat ON subcat.catg_id = products.prod_sbcg_id
    please note, you are still forcing an inner join by putting a condition on one of the right tables into the WHERE clause -- it belongs in the ON clause
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if you want to join to the same table more than once, you ~must~ use table aliases

    e.g.
    Code:
    LEFT JOIN categories AS maincat ON maincat.catg_id = products.prod_catg_id 
    LEFT JOIN categories AS subcat ON subcat.catg_id = products.prod_sbcg_id
    please note, you are still forcing an inner join by putting a condition on one of the right tables into the WHERE clause -- it belongs in the ON clause
    Hi,

    I made this simple query as per your guideline but it is not returning any data.

    Code SQL:
    SELECT products.*, categories.*, categories.* FROM products
    	LEFT JOIN categories AS category ON products.prod_catg_id = category.catg_id
    	LEFT JOIN categories AS subcategory ON products.prod_sbcg_id = subcategory.catg_id

    Please advice.

    Thanks.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Tapan View Post
    Please advice.
    you missed a couple

    see the SELECT clause
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you missed a couple

    see the SELECT clause
    Hi,

    Removed the double categories but still not working

    Code:
    SELECT products.*, categories.* FROM products
    	LEFT JOIN categories AS category ON products.prod_catg_id = category.catg_id
    	LEFT JOIN categories AS subcategory ON products.prod_sbcg_id = subcategory.catg_id
    Thanks.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you are still referencing a table called categories in the SELECT clause

    you don't have that table any more, but you do have two tables called category and subcategory

    you must use the alias(es) in the SELECT clause too

    (hint: use bofadem)

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

  12. #12
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Quote Originally Posted by r937 View Post
    you are still referencing a table called categories in the SELECT clause

    you don't have that table any more, but you do have two tables called category and subcategory

    you must use the alias(es) in the SELECT clause too

    (hint: use bofadem)

    Hi,

    Sorry but I am not understanding it. When I have done like following:

    Code:
    SELECT products.*, categories.* AS maincat, categories.* AS subcat FROM products
    	LEFT JOIN categories AS maincat ON maincat.catg_id = products.prod_catg_id
    	LEFT JOIN categories AS subcat ON subcat.catg_id = products.prod_sbcg_id
    Even then i am getting error: Unknown table 'categories'

    Also what is bofadem ? Googling it just bring me back to some of your old posts at siteforum.

    Please teach.

    Thanks.

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    From the manual
    A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

    SELECT t1.name, t2.salary
    FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

    SELECT t1.name, t2.salary
    FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
    And I guess 'bofadem' means 'both of them'.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Tapan View Post
    Code:
    SELECT products.*
         , categories.* AS maincat
         , categories.* AS subcat 
      FROM ...
    you cannot assign an alias like that, using the dreaded, evil "select star"

    first of all, you should stop using that

    secondly, you have to use table aliases like this --
    Code:
    SELECT products.*
         , maincat.* 
         , subcat.* 
      FROM ...
    but even then, your troubles are not over, because both of your tables (maincat and subcat) have the same columns, so you should really assign column aliases to them as well

    do a search for threads within this forum for "join same table twice" and i'm sure you will find numerous examples
    Quote Originally Posted by Tapan View Post
    Also what is bofadem ? Googling it just bring me back to some of your old posts at siteforum.
    now ~that's~ funny!!

    it means "both of them"

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

  15. #15
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Quote Originally Posted by r937 View Post
    you cannot assign an alias like that, using the dreaded, evil "select star"

    first of all, you should stop using that

    secondly, you have to use table aliases like this --
    Code:
    SELECT products.*
         , maincat.* 
         , subcat.* 
      FROM ...
    but even then, your troubles are not over, because both of your tables (maincat and subcat) have the same columns, so you should really assign column aliases to them as well

    do a search for threads within this forum for "join same table twice" and i'm sure you will find numerous examples
    now ~that's~ funny!!

    it means "both of them"

    Hello,

    Thanks very much. After your hints and tips it has worked . I am so happy. The final query:

    Code MySQL:
    SELECT products.*,
    		maincat.catg_id AS catgid, maincat.catg_name AS catgname,
    		subcat.catg_id AS sbcgid, subcat.sbcg_name AS sbcgname FROM products
    		LEFT JOIN categories AS maincat ON maincat.catg_id = products.prod_catg_id
    		LEFT JOIN categories AS subcat ON subcat.catg_id = products.prod_sbcg_id

    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
  •