By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,203 Members | 1,638 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,203 IT Pros & Developers. It's quick & easy.

limit on results with a "search criteria"

P: 11
hi experts,
the topic is rather misleading but i couldn't find a better way to phrase it.. here's my scenario:

Table A: Products
Table B: Attributes (with a product_id to show which product it belongs to)

i need to join the 2 tables to get what i require, which is a html like so:

Expand|Select|Wrap|Line Numbers
  1. Product A       Attribute 1
  2.                 Attribute 2
  3.                 Attribute 3
  4. Product B       Attribute 5
  5. Product C       Attribute 6
  6.                 Attribute 7
  7.  
what i did was.....
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (SELECT * FROM Attributes where Attribute_Name LIKE '%...%') as A
  3. join
  4. (SELECT * FROM Products limit 0,10) as P
  5. on (P.Id=A.Product_Id)
  6.  
the problem is when i require the user to select his search criteria, i cannot append my sql to match the desired results. I want to allow the user to list only Products with Attributes that matches his, and at the same time limit it to 10..

As with my statement, it gets the 1st 10 products, and joins with attributes that contain the search... which doesnt mean i will have 10 unique products based on the attributes...

hope my scenario was described well enough.. thanks
Sep 7 '07 #1
Share this Question
Share on Google+
7 Replies


Atli
Expert 5K+
P: 5,058
Hi, and welcome to TSDN!

You could try something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Products
  2. WHERE Products.id IN(
  3.     SELECT Product_ID 
  4.     FROM Attributes 
  5.     WHERE Attributes LIKE '%...%'
  6.   )
  7. LIMIT 10
  8.  
Which would return the first 10 products that contain an attribute that matches the keyword.

If you would rather have the first 10 attributes along with their product info, you could simply add a join to this query.
Sep 7 '07 #2

P: 11
if i do it that way, i will only get the set of products which contain the attributes matching my criteria

that is to say... 5 unique products if only 5 match....
so.. does it mean i can join the Attributes table so that i can get the Attributes that are associated with these 5 products too?
Sep 7 '07 #3

Atli
Expert 5K+
P: 5,058
The LIMIT at the end of the query limits the amount of rows it returns to 10. So if you were to join the Attributes table with this query it would return the first 10 Attributes that match your search terms.
So if 6 Attributes in each of the first two Products matched the search terms, you would get the first 10 Attributes along with their Product info and nothing more.

If you want 10 Unique Products and all the Attributes associated with them you will have to do something different.
For example:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Products
  2. INNER JOIN (
  3.     SELECT Product_ID 
  4.     FROM Attributes 
  5.     WHERE Attributes LIKE '%...%'
  6.     GROUP BY Product_ID
  7.     LIMIT 10
  8.   ) AS lt
  9.   ON lt.Product_ID = Products.id
  10. LEFT JOIN Attributes
  11.   ON Products.id = Attributes.Product_ID
  12.  
Sep 7 '07 #4

P: 11
Hi Atli~

that worked for me yea~
wow i couldn't have thought of it by myself seriously..
i'm quite inexperienced when coming up with queries

thanks alot by the way~
Sep 8 '07 #5

Atli
Expert 5K+
P: 5,058
Glad I could help :)
Good luck with your project! And don't hesitate to start a new thread if you have any more questions or problems we can help with.
Sep 8 '07 #6

P: 11
as a continuation, if i needed a count of the selected products, how am i able to do so?

where do i place my COUNT syntax ?
Sep 9 '07 #7

10K+
P: 13,264
as a continuation, if i needed a count of the selected products, how am i able to do so?

where do i place my COUNT syntax ?
Expand|Select|Wrap|Line Numbers
  1. select count (*) from selectedProducts
Sep 10 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.