SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Determining number of rows in a category from another table

    I'm using the following code to get the number of rows from a table that are in a specific category. For some reason it keeps saying there's one row when there's only two rows in the category. There's one a different category (not 6) and the menu status is set to 0, but it's still returning a result of 1. Can anybody find an error in my statement?

    Code:
    	SELECT count(category_id)
              FROM customer
        LEFT OUTER
        	  JOIN menu
    	    ON menu.customer_id = customer.id
    	   AND menu.status = '1'
        LEFT OUTER
    	  JOIN category
    	    ON category.id = customer.category_id
    	 WHERE customer.category_id = '6'
    Also, is there anyway to visualize what it's doing when running statements? Like seeing the tables how they look when they are joined?
    John Saunders

  2. #2
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does getting rid of "left" and leaving it as "outer join" work?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JohnSaunders
    Also, is there anyway to visualize what it's doing when running statements? Like seeing the tables how they look when they are joined?
    none that i know of, would love to hear otherwise

    meanwhile, all we can do is attempt to understand the data by looking at the data

    do me a favour and show a few relevant sample rows from each table

    unless menus are related to categories, you probably have cross join effects happening
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •