limit on results with a "search criteria"

[veliscorin]

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:

Product A       Attribute 1
                Attribute 2
                Attribute 3
Product B       Attribute 5
Product C       Attribute 6
                Attribute 7
 

what i did was.....

SELECT *
FROM (SELECT * FROM Attributes where Attribute_Name LIKE '%...%') as A
join
(SELECT * FROM Products limit 0,10) as P
on (P.Id=A.Product_Id)
 

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

[Atli]

Hi, and welcome to TSDN!

You could try something like this:

SELECT * FROM Products
WHERE Products.id IN(
    SELECT Product_ID 
    FROM Attributes 
    WHERE Attributes LIKE '%...%'
  )
LIMIT 10
 

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.

[veliscorin]

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?

[Atli]

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:

SELECT * FROM Products
INNER JOIN (
    SELECT Product_ID 
    FROM Attributes 
    WHERE Attributes LIKE '%...%'
    GROUP BY Product_ID
    LIMIT 10
  ) AS lt
  ON lt.Product_ID = Products.id
LEFT JOIN Attributes
  ON Products.id = Attributes.Product_ID
 

[veliscorin]

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~

[Atli]

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.

[veliscorin]

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 ?

[r035198x]

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 ?

select count (*) from selectedProducts