How can I display data using JOIN?

Question

How can I display data using JOIN?

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (1 votes)

1

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

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

I'mdoingitthisway:

Idividedintotwotables:
-Tabledados_usuario,ofcourse,storesuserdata(Experience,heightandweight);Home -Tablepranchahasthereferencevalues(type,size,weightandlit-tle)oneachboardaccordingtoexperience,weight,heightoftheuser.

->Icomparethedados_usuariotablewiththepranchatableandreturntheusertheidealmodel.Howtodothis?

AtfirstIthoughtI'dputthesamefields,bothinthedados_usuariotableandinthepranchatable,makeainnerjoinandhavethedataIwant.

However,thetwotableswouldhaveduplicatevalues.

'dados_usuario''prancha'altura2peso2altura2peso2altura2peso2altura1peso1altura1peso1altura1peso1comparoeexibo...

Idonotthinkthisisagoodpracticeorthebestwaytodoit.Iknowthereareothermethodsofdoingthis.

Thequestionis,Whatisthebestwaytocomparethisdata?
Howtoidentifywhichlineiscompatiblewiththedatathattheuserwillenter?

Mybank:

CREATETABLEDADOS_USUARIO(usuarioINTNOTNULLAUTO_INCREMENT,nomeVARCHAR(150)notnull,emailVARCHAR(50)notnull,estiloVARCHAR(14)notnull,expVARCHAR(13)notnull,alturaVARCHAR(12)notnull,pesoVARCHAR(9)notnull,PRIMARYKEY(usuario));CREATETABLEPRANCHA(prancha_priINTNOTNULLAUTO_INCREMENT,tipo_pranchaVARCHAR(9)NOTNULL,tamanho_pranchaVARCHAR(9)notnull,meio_pranchaVARCHAR(12)notnull,litragem_pranchaVARCHAR(8)notnull,PRIMARYKEY(prancha_pri));

DATAINSERTINTHE'DATA_USUARIO'TABLE:

INSERTINTOEXPERIENCIAVALUES(NULL,'joao','[email protected]','Surf','INICIANTE','<1,60m','>90kg');INSERTINTOEXPERIENCIAVALUES(NULL,'john','[email protected]','StandUPPaddle','INTERMEDIARIO','1,81-1,90m','81-90kg');INSERTINTOEXPERIENCIAVALUES(NULL,'carl','[email protected]','Surf','AVANÇADO','>1,90m','71-80kg');

DATAINSERTINTABLEPRANCHA:

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

Inmyform,thereareallvaluesofheightandweight.Visuallyitputstheexactheightandweightofit.However,in%offields%Iputthevaluesinmytable:

Height:

<optionvalue="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>

Weight:

<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);

join inner

asked by anonymous 15.06.2016 / 19:45

3 answers

1

I re-created the tables and created the MaximumMaxima and MinimumMima fields to resolve the issue of the ranges and changed the type of the field height to Integer so the comparison between the heights becomes 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 )

The select with INNER JOIN bringing the ideal board to Pedro, João and Adriana looks like this:

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

It seems to me that it solves your question.

15.06.2016 / 20:22

1

First you should normalize your database: here

So, we can turn your table into 7 example:

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

So you would have a table like this, except that with the row IDs of the other tables telling you what information it would be. For example:

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

Ideally, you should create a user-only table 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 id of the board or just consult 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 also to find the ID of the board that will be inserted in the user table. Remember to create INDEXES for ids with their fields in their tables for a more performative query.

15.06.2016 / 21:27

PHPMailer without ajax Paging with Spring Framework

score 1 · Accepted Answer

The problem is this: You are doing JOIN with two tables that do not correlate. There is no key or value within both of which correlates, you are doing JOIN of Id do usuário with Id da Prancha and this does not make any sense and that way you will 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 height, weight, style, and user experience fields in the table of boards and make a JOIN with all or only a few

I do not understand anything about plank so I just made the 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 very randomly because I do not know which board is suitable for 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');

I made a simple% w / w

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

AlreadyforaINNERJOINmoredetailedandothervalues

SELECTUSU.nome,USU.exp,USU.altura,USU.peso,PRAN.tipo_prancha,PRAN.tamanho_prancha,PRAN.meio_prancha,PRAN.litragem_pranchaFROMDADOS_USUARIOASUSUINNERJOINPRANCHAASPRANONUSU.peso=PRAN.usuario_pesoANDUSU.altura=PRAN.usuario_alturaANDUSU.exp=PRAN.usuario_expANDUSU.estilo=PRAN.usuario_estilo

Myresultwasmorespecific