...

View Full Version : Resolved Query 2 tables, limit results of 1 table. How?



Nightfire
06-17-2011, 03: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


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, 06:26 PM
Two separate queries using a UNION ALL to join the queries.

Nightfire
06-17-2011, 09:31 PM
cheers for that :)

bazz
06-18-2011, 04: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



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.



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, 06:38 PM
Cheers for that, didn't know about the alias thing



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum