Home SELECT same element from two tables
Reply: 1

SELECT same element from two tables

Mariana Leites
1#
Mariana Leites Published in 2017-12-07 23:09:08Z

I got these two tables:

Create table  Consumidor  (
    numero  int(9),
    email   varchar(30) not null,
    sexo    char(1) not null,
    nascimento  date    not null,
    constraint Consumidor_sexo_RI001     check (sexo in ('F','M')),
    constraint Consumidor_unique_RI002  unique(email),
    constraint pk_Consumidor     primary key (numero)
);

Create table  Dependente  (
    consumidor  int(9),
    numero  int(2),
    sexo    char(1) not null,
    nascimento  date    not null,
    constraint Dependente_sexo_RI003 check (sexo in ('F','M')),
    constraint fk_Dependente_consumidor  foreign key (consumidor) references Consumidor(numero) on delete cascade,
    constraint pk_Dependente     primary key (consumidor,numero)
);

What I wanna do is SELECT the 'sexo' and 'nascimento' of both tables. I don't understand how I can do this.

------------------ EDIT ----------------------- The result table should look something like this:

idade | sexo
  34  |  F

It only has two columns for both tables.

Robert I
2#
Robert I Reply to 2017-12-07 23:36:59Z

Query:

SELECT c.sexo as "Consumidor", c.nascimento as "Consumidor",
       d.sexo as "Dependente", d.nascimento as "Dependente"
FROM
      Consumidor c JOIN Dependente d ON c.numero = d.consumidor
ORDER BY
      c.nascimento;

Result:

Consumidor  Consumidor  Dependente  Dependente
    M       2012-08-06       F      2012-08-06

If you want to combine them together perhaps

SELECT group_concat(c.sexo,"|",d.sexo) as "sexo", 
       group_concat(TIMESTAMPDIFF(YEAR,c.nascimento,CURDATE())
                    ,"|"
                    ,TIMESTAMPDIFF(YEAR,d.nascimento,CURDATE())) as "idade"
FROM
      Consumidor c JOIN Dependente d ON c.numero = d.consumidor
ORDER BY
      c.nascimento;

Which produces:

sexo    idade
F|M      5|5
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.342796 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO