Query return a bad output

Question

table offer

id_offer  offer   state    

600         A       0
629         B       0

tags

id_tags     tags        

561         PHP
562         JAVA
589         MySQL
917         python

offer_has_tags

offer_id_offer      tags_id_tags 
 600                    561     
 600                    562
 600                    917
 629                    562
 629                    589
 629                    917

The output that i need:

600     PHP
600     JAVA
600     python
629     JAVA
629     MySQL
629     python

What i am trying (without success):

    SELECT A.id_offer, A.tags
    FROM 
      ( SELECT *
        FROM offer
        WHERE id_offer IN (600, 629)
        AND state = 0  
        ORDER BY date_post DESC
        LIMIT ?, ?
      ) A, tags A
    INNER JOIN offer_has_tags B
        ON A.id_tags = B.tags_id_tags
    INNER JOIN offer C
        ON C.id_offer = B.offer_id_offer
    GROUP BY id_tags

Any idea? thanks

codingbiz · Accepted Answer · 2012-07-22 01:45:23Z

2

try this

SELECT A.id_offer, t.tags
from 
  (Select * from offer o
    Where o.id_offer IN (600, 629)
    AND o.State=0
    ORDER BY ? DESC
    Limit ?,?) A
INNER JOIN offer_has_tags B
    ON A.id_offer = B.offer_id_offer
INNER JOIN tags t
    ON b.tags_id_tags = t.id_tags

edited Jul 22, 2012 at 1:45

answered Jul 22, 2012 at 1:13

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user455318 Over a year ago

hi, i forgot the limit clause in the subquery. Now i remember that is the reason why i use a subquery.

hmmftg · Accepted Answer · 2012-07-22 01:47:59Z

1

Using Old SQL style(but more understandable I think):

 SELECT id_offer, tags.tags
   FROM tags,offer_has_tags,offer
  WHERE offer_id_offer = offer.id_offer
    AND tags_id_tags = id_tags
    AND id_offer IN (600, 629)
    AND state = 0
  GROUP BY id_offer, tags.tags
  ORDER BY date_post DESC

with LIMIT:

 SELECT A.id_offer, tags.tags
   FROM tags,( SELECT *
                 FROM offer
                WHERE id_offer IN (600, 629)
                  AND state = 0  
                ORDER BY date_post DESC
                LIMIT ?, ?
              ) A,offer_has_tags
  WHERE offer_id_offer = A.id_offer
    AND tags_id_tags = id_tags
  GROUP BY A.id_offer, tags.tags

edited Jul 22, 2012 at 1:47

answered Jul 22, 2012 at 1:02

hmmftg

1,8501 gold badge25 silver badges38 bronze badges

3 Comments

user455318 Over a year ago

please, i added new details. i forgot the limit clause.

hmmftg Over a year ago

Can you make it clear with your limit clause? which kind of rows?

user455318 Over a year ago

,the limit should restrict the number of offers, not the number of tags. For example "i want the tags off the offers between 3-5", so the output should be none in this case, because i only have two offers.

Collectives™ on Stack Overflow

Query return a bad output

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related