CODEWITHSUNDEEP
mysql
Simba
Simba

Reputation: 41

MYSQL Additional select on same table

I have a table called words:

word(text) team(number)

a          1
b          1
c          2
d          3
e          1
f          1

When i am doing mysql query i know only the word, so it looks like

SELECT word FROM words WHERE word = 'e'

However, i would like to append other words in same team

so my query would return 4 rows:

  1. e
  2. a
  3. b
  4. f

same word could be in the result set twice.

Could anybody help me which would be best method to use? UNION SELECT , INNER JOIN or what?

Thank you

UPDATE: After testing i realized that i need to append other records, that is, originally selected word be first. Also renamed "group" to "team" to avoid confusion.

For testing:

create table words(word text, team int);
insert into words (word, team) values('a', 1),('b', 1),('c', 2),('d', 2),('e',1),('f',1);

Upvotes: 1

Views: 88

Answers (3)

Egg Vans
Egg Vans

Reputation: 944

I think you want something like

SELECT b.word FROM WORDS a LEFT JOIN WORDS b ON a.team = b.team WHERE a.word = 'a'

sqlfiddle

ugly but does what you want in SQL

SELECT word FROM words WHERE word = 'e' UNION SELECT b.word FROM WORDS a LEFT JOIN WORDS b ON a.team = b.team WHERE a.word = 'e' AND b.word != 'e'

fiddle

Upvotes: 1

ScottS
ScottS

Reputation: 8543

Your question is getting more complex.

Here is a potential solution that uses a union and a sub-query to get the results.

SQL Fiddle

MySQL 5.5.32 Schema Setup:

create table words(word text, team int);
insert into words (word, team) values('a', 1),('b', 1),('c', 2),('d', 2),('e',1),('f',1);

Query 1:

SELECT word FROM words WHERE word = "e"

UNION

(SELECT word FROM words
  WHERE team = (SELECT team FROM words WHERE word = "e")
  AND word != "e"
  ORDER BY word)

Results:

| WORD |
--------
|    e |
|    a |
|    b |
|    f |

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

you can use OR 

    SELECT  word  FROM words 
    where `group` in (select `group` from words where word = 'a' )

JUST to let you know that you have column group which is reserved keyword , dont forget to use backticks when selecting it.

Upvotes: 0

Related Questions