0

) I wish to select two rows from the same table ontop of a bunch of joins, but i'm not sure what exactly to do. This is my current select statement:

SELECT m_table.*, t_people.name as boxer_name, t_people.class, t_people.age, t_people.sex, m_time.time, m_time.rounds, t_age.name as age_name, teams.name as team_name
FROM m_table
INNER JOIN t_people ON t_people.id = m_table.red_id
INNER JOIN m_time ON (m_time.age = t_people.age AND m_time.sex = t_people.sex OR m_time.age = t_people.age AND m_time.sex = 'u')
INNER JOIN t_age ON t_age.id = t_people.age
INNER JOIN teams ON teams.id = t_people.team_id

I wish to extract two people from the t_people table.

EDIT As you can see above i'm extracting one with the use of m_table.red_id And i wish to select another row with the use of m_table.blue_id EDIT

How should approach this?

Mathias
  • 119
  • 1
  • 2
  • 16
  • do you want to select the first 2 rows? – RafaSashi Oct 18 '13 at 16:00
  • You could use Limit 2 –  Oct 18 '13 at 16:01
  • Nope i wish to select the rows from t_people, that have the id of m_table.red_id and m_table.blue_id – Mathias Oct 18 '13 at 16:01
  • select top 2, *.......rest of your query. if you want to first rows. if you want random or something different let us know. – johnny Oct 18 '13 at 16:01
  • http://www.w3schools.com/sql/sql_top.asp Will only display the first two results of your query list –  Oct 18 '13 at 16:02
  • I just edited the post :-) – Mathias Oct 18 '13 at 16:02
  • I don't see m_table.blue_id in your inner join. way don't you do `INNER JOIN t_people ON (t_people.id=m_table.red_id OR t_people.id=m_table.blue_id)` ? – RafaSashi Oct 18 '13 at 16:08
  • I need to select the t_people both from red_id and blue_id. Just as Elon Than tried to do below, but it returns and error. – Mathias Oct 18 '13 at 16:11
  • @Mathias It returns error because you didn't read last sentence from my answer ;) – Elon Than Oct 18 '13 at 16:13
  • if t_people.id=m_table.red_id AND t_people.id=m_table.blue_id we can say tat m_table.red_id = m_table.blue_id right ?? – RafaSashi Oct 18 '13 at 16:14
  • But how could an OR tag do the charm, when i need both ? :D No offence, i'm just trying to connect the dots. – Mathias Oct 18 '13 at 16:14
  • And your most recent comment would result in it only selecting one row with the id of blue_id and red_id – Mathias Oct 18 '13 at 16:15
  • Sorry I am not sure to understand. But if the column red_id and the column blue_id belong to the same table and you want them to be equal you can do `WHERE t_people.red_id=t_people.blue_id` after you joins – RafaSashi Oct 18 '13 at 16:20

1 Answers1

2

Just add another JOIN with another alias

INNER JOIN t_people people1 ON people1.id = m_table.red_id
INNER JOIN t_people people2 ON people2.id = m_table.blue_id

And then select values from it using this aliases, eg. people1.name.

Elon Than
  • 9,603
  • 4
  • 27
  • 37
  • It seems like a good approach, however it leaves me with the following error: Unknown column 't_people.name' in 'field list' – Mathias Oct 18 '13 at 16:07
  • 1
    @Mathias Because there is no `t_people` when you're using aliases. Use one of them instead. – Elon Than Oct 18 '13 at 16:10
  • I tried using only one alias at the blue_id, but that results in not outputting anything from blue. Would i need to assign another selection in the select statement? – Mathias Oct 18 '13 at 16:14
  • @Mathias You have to list all fields to select, eg. if you want `name`, then you have to write `people1.name, people2.name`. – Elon Than Oct 18 '13 at 16:14
  • Yea thats what i thought :-) I'll try it out soonish, and get back to you. Thanks a lot so far.' – Mathias Oct 18 '13 at 16:39