Join between tables that do not relate directly

Asked

Viewed 860 times

0

I have three tables:

  1. Candidate who has an ID as pk (which has been invented)
  2. Registration that has FK for Candidate
  3. ENEM that has no id representing the candidate, only his registration and information as color/ race, municipality

inserir a descrição da imagem aqui inserir a descrição da imagem aqui

I need to bring this Pk_candidate in the table Registration making a JOIN with the table Enem, but how to do it if Candidate and Enem are not directly related by a unique code, only by information that repeat as sex?

I tried to relate Candidate and Enem by another table that both have in common, that would be Municipality, but also did not get the expected result, that would be the number of registration without repetition followed by the desired data.

sql database sql-server join relational-model

  • The tables must be related in some way. What is the relational model of the database? Has ERI?

  • Has a MER that is in db designer (I can only share by email). The only table that would relate the two would be the Municipality, because the table Enem has cod_municipio and the Candidate has FK for municipality. Apart from this table, they have data in common, but that repeats between records...

  • You should then make an Inner Join with the three tables then.

  • http://firebase.com.br/pipermail/lista_firebase.com.br/2004-December/004057.html I believe that Join implicito solves candidate->incricao--->Enem

  • @RORSCHACH So I’m trying to do a JOIN between these three tables.. but the point is I need the candidate’s pk to fill the Registration table.. But this pk is an invented datum.. There is no table in ENEM or other, only in the candidate..

  • Here is the schema done in dbdesigner -> http://i63.tinypic.com/2mmh9hc.png

  • I’m starting to think there’s something wrong with modeling.. Just so you guys understand, the Eeven table is in a separate bank.. It is a "linguistics" of Enem data, containing the candidate data and the registration data.. But the pk_candidate is not there.. Only the registration number

  • @Miuinwonderland So the DB was not well designed, you will have many problems

  • Thanks for the help, guys! I had to change the modeling and put the fk application inside candidate, no matter how wrong... It was the way I found to work. =/

Show 4 more comments

1 answer

1

It would be interesting to also put the information of the ENEM table, as you did with the CANDIDATE and REGISTRATION. But as it says that in the ENEM table contains the registration field, for this we can do the Join with REGISTRATION and then make a Join with CANDIDATE.

Example:

Select 
    c.PK_CANDIDATO 
FROM 
    ENEM e
    INNER JOIN INSCRIÇÃO i ON i.ID_INSCRIÇÃO = e.<CAMPO_DE_INSCRIÇÃO>
    INNER JOIN CANDIDATO c ON c.PK_CANDIDATO = i.FK_CANDIDATO

;

Never name objects with special characters like "Ç", "Ã".

Browser other questions tagged sql database sql-server join relational-model

You are not signed in. Login or sign up in order to post.