Reputation: 67
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 = B
and 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
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
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
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
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