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

Nightfire · 06-17-2011, 02:29 PM

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.

guelphdad · 06-17-2011, 05:26 PM

Two separate queries using a UNION ALL to join the queries.

Nightfire · 06-17-2011, 08:31 PM

cheers for that

bazz · 06-18-2011, 03:25 AM

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

Nightfire · 06-18-2011, 05:38 PM

Cheers for that, didn't know about the alias thing