CODEWITHSUNDEEP
mysql
OFBrc
OFBrc

Reputation: 67

select rows with same id but with different column values in mysql

I would like to select data with different rows but with same id.

| Codenum | Letter | description |
|     001 | A      | APPLES      |
|     002 | A      | BANANA      |
|     001 | B      | RED         |
|     001 | B      | GREEN       |
|     002 | B      | BLUE        |
|     002 | B      | YELLOW      |

What i would like is to get the rows that are classified as LETTER B but also the description of LETTER A with the same Codenum.

The incomplete query is SELECT description FROM table where letter = Band i want to add the description of letter A that corresponds with the Codenum desired result :

001    B   RED     APPLES
001    B   GREEN   APPLES
002    B   BLUE    BANANA
002    B   YELLOW  BANANA

thanks a lot in advance

Upvotes: 1

Views: 3842

Answers (4)

Florian Heer
Florian Heer

Reputation: 714

You could do it with a subselect:

SELECT Codenum, Letter, description, (SELECT description FROM mytable x where x.Codenum = second.Codenum AND Letter = 'A') AS adescription
FROM mytable second
WHERE mytable.Letter = 'B'

Upvotes: 1

Vijaya Vignesh Kumar
Vijaya Vignesh Kumar

Reputation: 444

Exact Query :

select a.codenum, a.letter, a.description,b.description
from tbl_data a join tbl_data b on b.codenum = a.codenum 
and b.letter = 'A' where a.letter = 'B'

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146603

Try this

select a.codenum, a.letter, a.description, b.description
from table a join table b 
   on b.codenum = a.codenum 
      and b.letter = 'A'
where a.letter = 'B'

Upvotes: 0

Vasil Rashkov
Vasil Rashkov

Reputation: 1834

I think what you are looking for is group_concat()

Try out this query :

SELECT
    GROUP_CONCAT(`description`) as description_concat
FROM
    `table_name`
WHERE
    `letter` = 'B'
GROUP BY
    `letter`

Upvotes: 0

Related Questions