How to create Join table with itself with multiple where

Question

I'm trying to join table with itself but with no luck.

my tables contains:

ID    Name                         Position
1     Jefferson Roxas              president
2     Carlson Zafiro               president
3     Andrew Anderson              vice president
9     Jayson Dela Cruz             representative
10    Arille Villanueva Valdez     representative
11    Arnold Baltazar              representative

The result of query that I want is:

ID   Name             Position     ID Name             Position        ID Name                      Position
1    Jefferson Roxas  president    3  Andrew Anderson  vice president  9  Jayson Dela Cruz          Representative
2    Carlson Zafiro   president                                        10 Arille Villanueva Valdez  Representative
                                                                       11 Arnold Baltazar           Representative

I am using this query:

Select a.idcandidates, a.position, b.idcandidates, b.position , c.idcandidates, c.position
from tbl_candidates a, tbl_candidates b, tbl_candidates c
where a.position='president' and b.position='vice president' and c.position='representative'

but the result was

ID   Name             Position     ID Name             Position        ID Name                      Position
1    Jefferson Roxas  president    3  Andrew Anderson  vice president  9  Jayson Dela Cruz          Representative
2    Carlson Zafiro   president    3  Andrew Anderson  vice president  10 Arille Villanueva Valdez  Representative
1    Jefferson Roxas  president    3  Andrew Anderson  vice president  11 Arnold Baltazar           Representative
2    Carlson Zafiro   president    3  Andrew Anderson  vice president  9  Jayson Dela Cruz          Representative
1    Jefferson Roxas  president    3  Andrew Anderson  vice president  10 Arille Villanueva Valdez  Representative
2    Carlson Zafiro   president    3  Andrew Anderson  vice president  11 Arnold Baltazar           Representative

modify your schema, add columns such as session, rank – Drew Sep 26 '15 at 04:50 — Drew, Sep 26 '15 at 04:50

mfredy · Answer 1 · 2015-09-27T09:26:41.323

0

Why dont you try doing a UNION statement bu with three separate queries. for example.

Select Distinct a.idcandidates, a.position, b.idcandidates, b.position, c.idcandidates, c.position
from tbl_candidates a, tbl_candidates b, tbl_candidates c
where a.position='chairman'

UNION

Select Distinct a.idcandidates, a.position, b.idcandidates, b.position , c.idcandidates, c.position
from tbl_candidates a, tbl_candidates b, tbl_candidates c
where  and b.position='vice chairman'

UNION

Select Distinct a.idcandidates, a.position, b.idcandidates, b.position , c.idcandidates, c.position
from tbl_candidates a, tbl_candidates b, tbl_candidates c
where  c.position='representative'

I dont know if this an alternate to your solution. But itws worth a try, to see if you are getting the results you want.

edited Sep 27 '15 at 09:26

answered Sep 26 '15 at 09:24

mfredy

597
1
8
16

you query does the trick. however it repeats all the entries position per column. – Zero Serum Sep 26 '15 at 16:06
Hi @ZeroSerum I added a distinct after SELECT. Please try this – mfredy Sep 27 '15 at 09:27
I have 7 entries here. with your query. it 449 rows. – Zero Serum Sep 27 '15 at 12:25
Can you please post a sample of the results you are receiving? I am trying to help you @ZeroSerum – mfredy Sep 27 '15 at 13:28

score 0 · Answer 2 · answered Sep 26 '15 at 10:18

0

There needs to be a parentID for each row to denote hierarchy and then your query will be easier to write and dynamic. Once done if you provide the table I can provide an answer

answered Sep 26 '15 at 10:18

user3262640

27
6

score 0 · Answer 3 · edited May 23 '17 at 12:06

Your desired output misses some fundamental ideas of relational queries. I think this is pretty much what other answers are alluding to here. Also, your query doesn't align with the data you presented. Assuming you actually meant this,

Select a.id, a.name, a.position, b.id, b.name, b.position , c.id, c.name, c.position
from tbl_candidates a, tbl_candidates b, tbl_candidates c
where a.position='president' and b.position='vice president' and c.position='representative'

Then your desired output is really just these three separate queries that happen to be joined on their ordinal values. If that's the case, then let's explicitly do this and put the queries back together. I'm not a MySQL guy, so here's how I'd do it in SQL Server.

select 
  prs.id
  ,prs.name
  ,prs.position
  ,vcp.id
  ,vcp.name
  ,vcp.position
  ,rep.id
  ,rep.name
  ,rep.position
from (
    select 
      id
      ,name
      ,position
      ,row_number() over (order by id) rwn
    from 
      tbl_candidates
    where 
      position = 'president'
  ) prs 
  full outer join (
    select 
      id
      ,name
      ,position
      ,row_number() over (order by id) rwn
    from 
      tbl_candidates
    where 
      position = 'vice president'
  ) vcp on prs.rwn = vcp.rwn
  full outer join (
    select 
      id
      ,name
      ,position
      ,row_number() over (order by id) rwn
    from 
      tbl_candidates
    where 
      position = 'representative'
  ) rep on 
    rep.rwn = prs.rwn
    or rep.rwn = vcp.rwn

Unfortunately, there is no row_number() for mysql. You could try to incorporate varables to build a row number instead.

This is incredibly ugly, so I'd really think about what your use case is and trying to change that rather than this solution. It does provide what you want, though.

I'm just trying to join three query: Select * from tbl_candidates where position='president'; Select * from tbl_candidates where position='vice president'; Select * from tbl_candidates where position='representative'; how will i be able this 3 queries join table? — Zero Serum, Sep 26 '15 at 14:52
That's what the query that I posed above does. It queries from the table three times and then joins them together based on an arbitrary ordering of `id`. You'll need to create that ordering column, though, which I did above with `row_number()`. In MySQL, you'll need to use another method (like the one I liked above). If you're talking about what you *should* do, you should simply return the three separately and display them alongside each other. In this case, row 1 of one query has nothing to do with row 1 of another, so they should not be part of the same set. — Jeremy Fortune, Sep 26 '15 at 15:20

How to create Join table with itself with multiple where

3 Answers3