CODEWITHSUNDEEP
mysqllimit
anvd
anvd

Reputation: 4047

limit - mysql query

I would like to know how can i limit the output by the number of id_offers and not the number of rows. For example

 SELECT  A.id_offer, T.tags  
            FROM offer A
            INNER JOIN offer_has_tags Z
            ON A.id_offer = Z.offer_id_offer

            INNER JOIN tags T
            ON Z.tags_id_tags = T.id_tags

            WHERE state = 0 
            ORDER BY date
            DESC LIMIT 0, 10

output:

  id_offer  tags
    77       xx 
    76       xx 
    76       xx 
    75       xx 
    75       xx 
    74       xx 
    74       xx
    73       xx
    73       xx
    72       xx

Edit: In this case only should be count as 6 offers.

Upvotes: 3

Views: 230

Answers (3)

Richk
Richk

Reputation: 131

You simply have to use DISTINCT:

SELECT DISTINCT A.id_offer, T.tags
FROM offer A INNER JOIN offer_has_tags Z ON A.id_offer = Z.offer_id_offer

        INNER JOIN tags T
        ON Z.tags_id_tags = T.id_tags

        WHERE state = 0 
        ORDER BY date
        DESC LIMIT 0, 10

Upvotes: 0

bhamby
bhamby

Reputation: 15440

I'm not sure if this is exactly what you want, but I think it is:

SELECT  A.id_offer, T.tags  
FROM offer A
JOIN offer_has_tags Z
  ON A.id_offer = Z.offer_id_offer
JOIN tags T
  ON Z.tags_id_tags = T.id_tags
JOIN (
    SELECT DISTINCT id_offer
    FROM offer
    WHERE state = 0
    ORDER BY date DESC
    LIMIT 10
) L
  ON A.id_offer = L.id_offer

or the more simple:

SELECT  A.id_offer, T.tags  
FROM 
  ( SELECT *
    FROM offer
    WHERE state = 0
    ORDER BY date DESC
    LIMIT 10
  ) A
JOIN offer_has_tags Z
  ON A.id_offer = Z.offer_id_offer
JOIN tags T
  ON Z.tags_id_tags = T.id_tags

Upvotes: 2

John Woo
John Woo

Reputation: 263693

you can try this:

SELECT  A.id_offer, T.tags  
FROM offer A
    INNER JOIN offer_has_tags Z
        ON A.id_offer = Z.offer_id_offer
    INNER JOIN tags T
        ON Z.tags_id_tags = T.id_tags

WHERE (state = 0) AND
      (A.id_offer >= 72 AND A.id_offer <= 77)
ORDER BY date

Upvotes: 0

Related Questions