0

I have 2 tables:

  • category(inter_archi_cat table) and their linking with other
  • entities(inter_archi table )

I want to select only categories which linked to any entity.

SELECT * 
  FROM inter_archi_cat 
 WHERE id IN (SELECT GROUP_CONCAT(DISTINCT sub_cat) as allcat 
                FROM inter_archi)

If I ran the subquery individually, I'm giving correct results (apx 40 records). But while running with main query its giving only 1 record.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502

2 Answers2

0

I have done it:

here is changed query:

SELECT distinct cat.id,cat.name
  FROM inter_archi_cat  as cat
  join   inter_archi as inter on (cat.id in (inter.sub_cat))

got idea from

MySQL - How to use subquery into IN statement by value

Community
  • 1
  • 1
0

You don't have to use IN and subquery. You can achieve the same result using simple inner join:

SELECT ic.* FROM inter_archi_cat ic 
JOIN inter_archi i 
ON i.sub_cat = ic.id 
GROUP BY ic.id

which would be a lot faster.

piotrm
  • 12,038
  • 4
  • 31
  • 28
  • I have multiple categories separated by comma under each row under sub_cat column –  May 02 '11 at 06:11
  • I see, then you really need IN, so your own answer is good, but you should change your question to have correct info, so others can learn from it in the future. – piotrm May 02 '11 at 06:16