Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,266
    Thanks
    6
    Thanked 48 Times in 48 Posts

    Query 2 tables, limit results of 1 table. How?

    First time I've ever done anything like querying 2 tables at a time, and I'm struggling. I have the following query
    Code:
    SELECT
    categories.id,
    categories.listing,
    categories.catname,
    subcategories.catid,
    subcategories.subcat,
    subcategories.listings,
    subcategories.url
    	FROM 
    categories
    	LEFT JOIN
    subcategories
    	ON
    categories.id=subcategories.catid 
    	ORDER BY 
    categories.catname,subcategories.subcat 
    	ASC
    I am wanting to only show 3 results at a time from subcategories table. I tried adding LIMIT to the end of the query, but that just limited both tables.
    Last edited by Nightfire; 06-17-2011 at 08:33 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Two separate queries using a UNION ALL to join the queries.

  • Users who have thanked guelphdad for this post:

    Nightfire (06-18-2011)

  • #3
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,266
    Thanks
    6
    Thanked 48 Times in 48 Posts
    cheers for that

  • #4
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    you may prefer to write the query in that sort of format but you can save typing time by using an 'alias'

    eg

    Code:
    SELECT
    c.id,
    c.listing,
    c.catname,
    s.catid,
    s.subcat,
    s.listings,
    s.url
    	FROM 
    categories AS c
    	LEFT JOIN
    subcategories AS s
    	ON
    c.id=s.catid 
    	ORDER BY 
    c.catname,s.subcat 
    	ASC
    and I find this helps with 'seeing' where commas have run away and hidden somewhere.

    Code:
    SELECT 
      c.id
    , c.listing
    , c.catname
    , s.catid
    , s.subcat
    , s.listings
    , s.url
     FROM 
    categories AS c
     LEFT JOIN
    subcategories AS s
     ON
    c.id=s.catid 
     ORDER BY 
    c.catname,s.subcat 
     ASC
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • Users who have thanked bazz for this post:

    Nightfire (06-18-2011)

  • #5
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,266
    Thanks
    6
    Thanked 48 Times in 48 Posts
    Cheers for that, didn't know about the alias thing


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •