Sql does not return column name

Question

Sql does not return column name

Navigation

#1 by (0 votes)

0

I have two tables with TEAM other MATCH_DETAILS . The TEAM table has the id column and is the primary key. I made two relationships from the TEAM table to MATCH_DETAILS . In MATCH_DETAILS assumed as foreign key team_id and team_id1 .

With the following query :

SELECT  
  team_name, team_id1, Umpire_name, 
  playermatch_name, score_id
FROM 
  Match_details m, Team t, 
  Umpire u, player_match p, Score s
WHERE m.TEAM_TEAM_ID = t.TEAM_ID 
  and m.TEAM_TEAM_ID1 =  m.TEAM_TEAM_ID1 
  and m.UMPIRE_UMPIRE_ID = u.UMPIRE_ID 
  and p.Match_details_MATCH_ID = m.match_id 
  and s.Match_details_MATCH_ID = m.match_id;

Returns the following:

Chelsea has as id number 1 id number 2 in column team_id1 corresponds to liverpool . what I want is to appear Liverpool and not the id that corresponds.

sql query

asked by anonymous 05.12.2017 / 13:49

1 answer

Mount a table of products as a window in java jsp FullCalendar remember every month

score 0 · Answer 1

First, I suggested using explicit joins instead of implicit joins (ANSI-92). Although there are no performance differences, the "new" syntax is much more readable when there are many tables involved (as is the case with you).

That said, your query can be rewritten as follows.

SELECT  T1.TEAM_NAME,
        T2.TEAM_NAME, 
        U.Umpire_name, 
        playermatch_name,
        score_id
  FROM Match_details m_d, 
 INNER JOIN Team T1  -- Detalhes da primeira equipa
    ON T1.TEAM_ID = m_d.TEAM_ID
 INNER JOIN Team T2  -- Detalhes da segunda equipa
    ON T2.TEAM_ID = m_d.TEAM_ID1
 INNER JOIN Umpire U
    ON U.UMPIRE_ID = m_d.UMPIRE_UMPIRE_ID
 INNER JOIN player_match P
    ON P.Match_details_MATCH_ID = m_d.match_id 
 INNER JOIN Score S
    ON S.Match_details_MATCH_ID = m_d.match_id

Note the double join in the TEAM table: One for the first team (in your example Chelsea) and one for the second team (Liverpool). To display the name of the second team in the result, simply select the respective table name (T2) instead of the ID.