How can I view data using JOIN?

Asked

Viewed 1,607 times

1

I am making a section on my site called "My Ideal Board", in it the user inserts his data (weight, height, etc), a comparison is made in the bank (join) and is returned to the user the ideal board type according to his profile.

I have a table with reference of all sizes and types of board according to height, weight and user experience.

Tabela de Referência

I’m doing it this way:

I divided it into two tables:
- Table dados_usuario, obviously, stores user data(Experience, height and weight);
- Table prancha has the reference values (type, size, weight and litragem) on each board according to experience, weight, user height.

-> I compare the table dados_usuario with the table prancha and return to the user the ideal model. How to do this?

At first I thought to put the same fields both in the table dados_usuario how much in the table prancha, make a inner join and have the data I want.

However, the two tables would have duplicated values.

`dados_usuario`      `prancha` 
    altura2            peso2
    altura2            peso2
    altura2            peso2
    altura1            peso1
    altura1            peso1
    altura1            peso1
         comparo e exibo...

I believe that this is not good practice nor the best way to do it. I know there are other methods of doing this.

The point is, how best to compare these data?
How to identify which line is compatible with the data the user will enter?

My bank:

CREATE TABLE DADOS_USUARIO(
     usuario INT NOT NULL AUTO_INCREMENT,
     nome VARCHAR(150) not null,
     email VARCHAR(50) not null,
     estilo VARCHAR(14) not null,        
     exp VARCHAR(13) not null, 
     altura VARCHAR(12) not null,
     peso VARCHAR(9) not null,
     PRIMARY KEY(usuario)
);

CREATE TABLE PRANCHA(
    prancha_pri INT NOT NULL AUTO_INCREMENT,        
    tipo_prancha VARCHAR(9) NOT NULL,
    tamanho_prancha VARCHAR(9) not null,
    meio_prancha VARCHAR(12) not null,
    litragem_prancha VARCHAR(8) not null,
    PRIMARY KEY (prancha_pri)
);

DATA ENTRY IN DADOS_USUARIO TABLE':

INSERT INTO EXPERIENCIA VALUES (NULL, 'joao', '[email protected]', 'Surf', 'INICIANTE', '<1,60m', '>90kg');
INSERT INTO EXPERIENCIA VALUES (NULL, 'john', '[email protected]', 'StandUP Paddle', 'INTERMEDIARIO', '1,81 - 1,90m', '81 - 90kg');
INSERT INTO EXPERIENCIA VALUES (NULL, 'carl', '[email protected]', 'Surf', 'AVANÇADO', '>1,90m', '71 - 80kg');

TABLE ENTRY OF DATA PRANCHA:

INSERT INTO PRANCHA VALUES (1, 'FUN', '8', '21 polegadas', '43L');
INSERT INTO PRANCHA VALUES (2, 'FUN', '8.8', '21 polegadas', '43L');
INSERT INTO PRANCHA VALUES (3, 'LONGBOARD', '9.2', '21 polegadas', '55L');
INSERT INTO PRANCHA VALUES (4,  'PRANCHA', '5.5 a 5.8', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (5,  'PRANCHA', '5.5 a 5.10', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (6,  'PRANCHA', '5.9 a 6.0', '21 polegadas', '32L');
INSERT INTO PRANCHA VALUES (7,  'PRANCHA', '6.0 a 6.4', '21 polegadas', '34L');
INSERT INTO PRANCHA VALUES (8,  'PRANCHA', '5.10 a 6.4', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (9,  'PRANCHA', '5.10 a 6.4', '20 polegadas', '32L');
INSERT INTO PRANCHA VALUES (10,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '32L');
INSERT INTO PRANCHA VALUES (11,  'PRANCHA', '6.4 a 6.8', '21 polegadas', '34L');
INSERT INTO PRANCHA VALUES (12,  'PRANCHA', '6.2 a 6.6', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (13,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '30L');
INSERT INTO PRANCHA VALUES (14,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '34L');
INSERT INTO PRANCHA VALUES (15,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '36L');
INSERT INTO PRANCHA VALUES (16,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '38L');
INSERT INTO PRANCHA VALUES (17,  'PRANCHA', '6.2 a 7.0', '21 polegadas', '34L');
INSERT INTO PRANCHA VALUES (18,  'PRANCHA', '6.2 a 7.0', '21 polegadas', '38L');
INSERT INTO PRANCHA VALUES (19,  'PRANCHA', '5.5 a 5.8', '18 polegadas', '23L');
INSERT INTO PRANCHA VALUES (20,  'PRANCHA', '5.8 a 5.10', '18 polegadas', '24L');
INSERT INTO PRANCHA VALUES (21,  'PRANCHA', '5.10', '18 polegadas', '27L');
INSERT INTO PRANCHA VALUES (22,  'PRANCHA', '6.0 a 6.2', '19 polegadas', '28L');
INSERT INTO PRANCHA VALUES (23,  'PRANCHA', '6.0 a 6.2', '19 polegadas', '29 a 31L');
INSERT INTO PRANCHA VALUES (24,  'PRANCHA', '5.10 a 6.0', '19 polegadas', '24L');
INSERT INTO PRANCHA VALUES (25,  'PRANCHA', '5.10', '19 polegadas', '26L');
INSERT INTO PRANCHA VALUES (26,  'PRANCHA', '6.0', '19 polegadas', '27L');
INSERT INTO PRANCHA VALUES (27,  'PRANCHA', '6.0', '19 polegadas', '29L');
INSERT INTO PRANCHA VALUES (28,  'PRANCHA', '6.2', '20 polegadas', '30 a 31L');
INSERT INTO PRANCHA VALUES (29,  'PRANCHA', '6.0', '19 polegadas', '25L');
INSERT INTO PRANCHA VALUES (30,  'PRANCHA', '6.0', '19 polegadas', '28L');
INSERT INTO PRANCHA VALUES (31,  'PRANCHA', '6.0', '19 polegadas', '30L');
INSERT INTO PRANCHA VALUES (32,  'PRANCHA', '6.0 a 6.2', '20 polegadas', '30 a 31L');
INSERT INTO PRANCHA VALUES (33,  'PRANCHA', '5.11', '19 polegadas', '26L');
INSERT INTO PRANCHA VALUES (34,  'PRANCHA', '5.11', '19 polegadas', '28L');
INSERT INTO PRANCHA VALUES (35,  'PRANCHA', '6.0', '20 polegadas', '29L');
INSERT INTO PRANCHA VALUES (36,  'PRANCHA', '6.1', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (37,  'PRANCHA', '6.1 a 6.6', '20 polegadas', '30 a 31L');
INSERT INTO PRANCHA VALUES (38,  'PRANCHA', '6.1', '19 polegadas', '27L');
INSERT INTO PRANCHA VALUES (39,  'PRANCHA', '6.1', '19 polegadas', '28L');
INSERT INTO PRANCHA VALUES (40,  'PRANCHA', '6.1 a 6.3', '20 polegadas', '29L');
INSERT INTO PRANCHA VALUES (41,  'PRANCHA', '6.1 a 6.4', '20 polegadas', '31L');
INSERT INTO PRANCHA VALUES (42,  'PRANCHA', '6.2 a 6.6', '20 polegadas', '31L');

In my form, are all the values of height and weight. Visually he puts the exact height and weight of it. However, in value of the fields I placed the values of my table:

Height:

<option value="1,71 - 1,80m">1.71m</option>
<option value="1,71 - 1,80m">1.72m</option>
<option value="1,71 - 1,80m">1.73m</option>
<option value="1,71 - 1,80m">1.74m</option>
<option value="1,71 - 1,80m">1.75m</option>
<option value="1,71 - 1,80m">1.76m</option>

Heaviness:

<option value="81 - 90kg">88Kg</option>
<option value="81 - 90kg">89Kg</option>
<option value="81 - 90kg">90Kg</option>
<option value=">90kg">91Kg</option>
<option value=">90kg">92Kg</option>
<option value=">90kg">93Kg</option>
<option value=">90kg">94Kg</option>

MY JOIN:

SELECT USU.nome,
       USU.exp,
       USU.altura,
       USU.peso,
       PRAN.tipo_prancha,
       PRAN.tamanho_prancha, 
       PRAN.meio_prancha, 
       PRAN.litragem_prancha       
          FROM DADOS_USUARIO AS USU 
                INNER JOIN PRANCHA AS PRAN
                   ON(USU.usuario = PRAN.prancha_pri);
database join inner

3 answers

1

I recreated the tables and created the fields Alturamaxima and Alturaminima to solve the issue of tracks and changed the field height type to Integer for the comparison between the heights become easier and I am inserting the height in centimeters.

create table prancha ( nivel varchar(10),
   alturamaxima integer,
   alturaminima integer )

create table usuario ( nome varchar(30),
  nivel varchar(10),
  altura integer )


insert into prancha values( 'iniciante', 160, 0 )
insert into prancha values( 'iniciante', 170, 160 )
insert into prancha values( 'iniciante', 180, 171 )
insert into prancha values( 'iniciante', 190, 181 )
insert into prancha values( 'iniciante', 200, 191 )


insert into usuario values( 'pedro', 'iniciante', 200 )
insert into usuario values( 'joao', 'iniciante', 180 )
insert into usuario values( 'adriana', 'iniciante', 175 )

select with INNER JOIN bringing the ideal board for Pedro, João and Adriana:

select * from prancha a inner join  usuario b 
on a.nivel = b.nivel and
   ( b.altura >=  a.alturaminima and
     b.altura <=  a.alturamaxima )

Seems to me it solves your question.

  • The question of the height and weight range is no problem. As I put in the post, visually the user inserts their exact height and weight. Ex: Pedro, 1.85, 79Kg. But I save the value from the field and within the value i put the tracks that are on my reference table. It’s right at the end of the post.

  • My problem is to cross the user’s data with the reference table. Ex: João, Surf, Intermediario, >1.60 - 1.70m, <60kg. <- João’s data

  • Looking at the reference table, the prancha ideal for Joao is the type PRANCHA, 6.4 a 6.8, 21 polegadas and 34 litros.

  • How can I make this comparison, do you understand? Compare John’s data with the table prancha. And return the equivalent line.

  • As in the select I passed you I am comparing the height of the user to the maximum of the board. In this list you can add all the comparatives you want between the fields of the table and the table user.

1

First you must normalize your database: here

So we can turn your table into 7 example:

nivel / altura / peso / tipo / tamanho / meio / litragem

Thus, you would have a table equal to this, only with the ID of the rows of the other tables stating what information would be what. For example:

Prancha:
id_nivel, id_altura, id_peso, id_tipo, id_tamanho, id_meio, id_litragem, id

Ideally you would create a user table only with:

Usuario:
   id, nome, email, id_prancha

And with the information that comes from a form for example you could add the user already with the board id or just refer to the board for example

SELECT * FROM prancha
INNER JOIN tabela_nivel ON prancha.id_nivel = tabela_nivel.id AND tabela_nivel.nivel == 'Inciante'
INNER JOIN tabela_altura ON prancha.id_altura = tabela_altura.id AND tabela_altura.altura = '1.60'
INNER JOIN tabela_peso ON ...

And so on and so forth. And this way also to find the ID of the board that will be inserted in the user table. Remember to create INDEXES for id’s with their fields in the respective tables to have a more performatical query.

  • 1

    And how would I know which board model fits the user profile if the table usuario will not capture any information from it? In the table usuario I captured the experiencia, altura and peso. And on the table prancha, where is the whole relation of the types of plank according to the experiencia, altura and peso user. With the inner join I compared the two tables (see the @Diefani example) and I got the feedback I wanted. Your help with the bank normalization was very helpful, thank you!

  • In case you have to capture this information before entering the user, then for example it would come from the site what the user recorded in his profile, then before saving vc would take the record of the table board that meets the user’s data, dai would save in the user table only id that line.

1


The problem is this: You’re doing JOIN with two tables that do not correlate. There is no key or value within both that correlates them, you are making a JOIN of Id do usuário with the Id da Prancha and that doesn’t make any sense and that way you’ll never get what you want. So I have two suggestions for you:

  • You make a common select and treat it within the application code, in which case you do not need to tinker with your database
  • You add the fields of height, weight, style and user experience in the table of boards and make a JOIN with all or only a few
    • I don’t know anything about board so I just made an example here and the data returned to me will be different from yours because your values will be correct.

      CREATE TABLE `PRANCHA` (
  `prancha_pri` int(11) NOT NULL,
  `tipo_prancha` varchar(9) NOT NULL,
  `usuario_peso` varchar(9) NOT NULL,
  `usuario_altura` varchar(12) NOT NULL,
  `usuario_exp` varchar(13) NOT NULL,
  `usuario_estilo` varchar(20) NOT NULL,
  `tamanho_prancha` varchar(9) NOT NULL,
  `meio_prancha` varchar(12) NOT NULL,
  `litragem_prancha` varchar(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    The data I entered in a very random way because I don’t know which board fits each case

    INSERT INTO `PRANCHA` (`prancha_pri`, `tipo_prancha`, `usuario_peso`, `usuario_altura`, `usuario_exp`, `usuario_estilo`, `tamanho_prancha`, `meio_prancha`, `litragem_prancha`) VALUES
(1, 'FUN', '>90kg', '<1,60m', 'INICIANTE', 'StandUP Paddle', '8', '21 polegadas', '43L'),
(2, 'FUN', '>90kg', '<1,60m', 'INICIANTE', 'StandUP Paddle', '8.8', '21 polegadas', '43L'),
(3, 'LONGBOARD', '81 - 90kg', '<1,60m', 'INICIANTE', 'StandUP Paddle', '9.2', '21 polegadas', '55L'),
(4, 'PRANCHA', '81 - 90kg', '<1,60m', 'INICIANTE', 'StandUP Paddle', '5.5 a 5.8', '20 polegadas', '30L'),
(5, 'PRANCHA', '81 - 90kg', '<1,60m', 'INICIANTE', 'StandUP Paddle', '5.5 a 5.1', '20 polegadas', '30L'),
(6, 'PRANCHA', '81 - 90kg', '<1,60m', 'INICIANTE', 'StandUP Paddle', '5.9 a 6.0', '21 polegadas', '32L'),
(7, 'PRANCHA', '81 - 90kg', '<1,60m', 'INICIANTE', 'Surf', '6.0 a 6.4', '21 polegadas', '34L'),
(8, 'PRANCHA', '81 - 90kg', '1,81 - 1,90m', 'INICIANTE', 'Surf', '5.10 a 6.', '20 polegadas', '30L'),
(9, 'PRANCHA', '81 - 90kg', '1,81 - 1,90m', 'INICIANTE', 'Surf', '5.10 a 6.', '20 polegadas', '32L'),
(10, 'PRANCHA', '81 - 90kg', '1,81 - 1,90m', 'INICIANTE', 'Surf', '6.2 a 6.6', '21 polegadas', '32L'),
(11, 'PRANCHA', '81 - 90kg', '1,81 - 1,90m', 'INICIANTE', 'Surf', '6.4 a 6.8', '21 polegadas', '34L'),
(12, 'PRANCHA', '81 - 90kg', '1,81 - 1,90m', 'INICIANTE', 'Surf', '6.2 a 6.6', '20 polegadas', '30L'),
(13, 'PRANCHA', '81 - 90kg', '1,81 - 1,90m', 'INICIANTE', 'Surf', '6.2 a 6.6', '21 polegadas', '30L'),
(14, 'PRANCHA', '81 - 90kg', '1,81 - 1,90m', 'INICIANTE', 'Surf', '6.2 a 6.6', '21 polegadas', '34L'),
(15, 'PRANCHA', '81 - 90kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.2 a 6.6', '21 polegadas', '36L'),
(16, 'PRANCHA', '81 - 90kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.2 a 6.6', '21 polegadas', '38L'),
(17, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.2 a 7.0', '21 polegadas', '34L'),
(18, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.2 a 7.0', '21 polegadas', '38L'),
(19, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '5.5 a 5.8', '18 polegadas', '23L'),
(20, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '5.8 a 5.1', '18 polegadas', '24L'),
(21, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '5.10', '18 polegadas', '27L'),
(22, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.0 a 6.2', '19 polegadas', '28L'),
(23, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.0 a 6.2', '19 polegadas', '29 a 31L'),
(24, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '5.10 a 6.', '19 polegadas', '24L'),
(25, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '5.10', '19 polegadas', '26L'),
(26, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.0', '19 polegadas', '27L'),
(27, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.0', '19 polegadas', '29L'),
(28, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.2', '20 polegadas', '30 a 31L'),
(29, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.0', '19 polegadas', '25L'),
(30, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.0', '19 polegadas', '28L'),
(31, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.0', '19 polegadas', '30L'),
(32, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '6.0 a 6.2', '20 polegadas', '30 a 31L'),
(33, 'PRANCHA', '71 - 80kg', '1,81 - 1,90m', 'INTERMEDIARIO', 'Surf', '5.11', '19 polegadas', '26L'),
(34, 'PRANCHA', '71 - 80kg', '>1,90m', 'AVANÇADO', 'Surf', '5.11', '19 polegadas', '28L'),
(35, 'PRANCHA', '71 - 80kg', '>1,90m', 'AVANÇADO', 'Surf', '6.0', '20 polegadas', '29L'),
(36, 'PRANCHA', '71 - 80kg', '>1,90m', 'AVANÇADO', 'Surf', '6.1', '20 polegadas', '30L'),
(37, 'PRANCHA', '71 - 80kg', '>1,90m', 'AVANÇADO', 'Surf', '6.1 a 6.6', '20 polegadas', '30 a 31L'),
(38, 'PRANCHA', '71 - 80kg', '>1,90m', 'AVANÇADO', 'Surf', '6.1', '19 polegadas', '27L'),
(39, 'PRANCHA', '71 - 80kg', '>1,90m', 'AVANÇADO', 'Surf', '6.1', '19 polegadas', '28L'),
(40, 'PRANCHA', '71 - 80kg', '>1,90m', 'AVANÇADO', '', '6.1 a 6.3', '20 polegadas', '29L'),
(41, 'PRANCHA', '71 - 80kg', '>1,90m', 'AVANÇADO', '', '6.1 a 6.4', '20 polegadas', '31L'),
(42, 'PRANCHA', '71 - 80kg', '>1,90m', 'AVANÇADO', '', '6.2 a 6.6', '20 polegadas', '31L');

    So I made a INNER JOIN simple 

    SELECT USU.nome,
       USU.exp,
       USU.altura,
       USU.peso,
       PRAN.tipo_prancha,
       PRAN.tamanho_prancha, 
       PRAN.meio_prancha, 
       PRAN.litragem_prancha       
          FROM DADOS_USUARIO AS USU 
                INNER JOIN PRANCHA AS PRAN
                   ON USU.peso = PRAN.usuario_peso

    And the return was

    inserir a descrição da imagem aqui

    Already to a SELECT more detailed and other values 

    SELECT USU.nome,
       USU.exp,
       USU.altura,
       USU.peso,
       PRAN.tipo_prancha,
       PRAN.tamanho_prancha, 
       PRAN.meio_prancha, 
       PRAN.litragem_prancha       
          FROM DADOS_USUARIO AS USU 
                INNER JOIN PRANCHA AS PRAN
                   ON USU.peso = PRAN.usuario_peso AND USU.altura = PRAN.usuario_altura AND USU.exp = PRAN.usuario_exp AND USU.estilo = PRAN.usuario_estilo

    My result was more specific

    inserir a descrição da imagem aqui

    • The problem was occurring exactly in the way I was doing Join. The main item of my query is the experience (iniciante, intermediario, avancado). Yeah, every single one experiencia there is a single type of board according to the weight and height of the user (as shown in the table).

    • Each board has a unique user type (Exp, height and weight). I modified the Join having the experiencia as main item, followed by height and weight. I added the field experiencia_ref on Tablea PRANCHA, I did the comparison and it worked extly the way I want it. I single template according to user profile. Thanks for the help!

    Browser other questions tagged database join inner

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