Sql Help that queries 2 fields from the same table

Asked

Viewed 931 times

0

I have the following problem to solve:

I need to make an Sql that refers to an X table

"Tabela X":
id coluna1 coluna2 

"Tabela 2":
id coluna1 coluna2 coluna3.

In "Table X" column 1 and column 2 are foreign keys of "Table 2" both with different values.

In Sql I need to return the id of the tableX the id of table 2 and any column of table two for the 2 foreign key fields.

Data sample:

Tabela X:

id   coluna1     coluna2   
1     1           2
2     2           1           
3     3           1
4     4           2

column 1 and column 2 are primary key of table 2.

Tabela 2:

id    coluna1       coluna2      coluna 3
1     TB2val1      TB2outro1     TB2aux1
2     TB2val2      TB2outro2     TB2aux2 
3     TB2val3      TB2outro3     TB2aux3
4     TB2val4      TB2outro4     TB2aux4

I tried to do so:

select  tx.id,tx.coluna1,tx.coluna2,t2.coluna1   from tabelaX tx,tabela2 t2 
where tx.id=t2.id group by tx.id,tx.coluna1,tx.coluna2,t2

but I could not complete the sql because I need to join the other query in select and I could not find how to do it.

I would need a return on select with these columns:

idTabela1   idTabela2 idTabela2  tabela2.coluna1  tabela2.coluna1

The first result would have to be like this:

First row select result I need: 1 1 2 TB2val1 TB2val2

I searched here and probably this issue is solved with a Union select.

sql join
  • 1

    tabela2.coluna1 tabela2.coluna1 the same field 2x ?

3 answers

0

Like coluna1 and coluna2 are primary and foreign key in tabelaX and tabela2, respectively, you need to use these fields in JOIN:

SELECT 
    tx.id as idTabela1, t2.id as idTabela2, tx.coluna1 as coluna1, tx.coluna2 as coluna2
FROM 
    tabelaX tx
JOIN 
    tabela2 t2 ON tx.coluna1 = t2.coluna1 AND tx.coluna2 = t2.coluna2

  • this way will only appear t2.coluna2

  • It’s not this query I want to do, I want to return the same column t2.coluna1 however for the 2 different fks, like the example I edited.

  • I remember that there are ways to join 2 selects when it is necessary to select the same field but that belong to different ids

  • I guess I don’t quite understand what the problem is

  • but if the ids are different and you want to join the results, strip the display id..

  • the important are the fields table2.coluna1 , even if I take out the ids, these other 2 will always be different because they are of different records.

  • from what I understand, coluna1 is equal in both tables (as well as coluna2), since they are keys.. so I do not understand the point that is in trouble

  • Column 1 and Column 2 in the tableX are references to the same field in table2 but in tableX they have different values example 1 and 2 or reference 2 different records and in each of these records there is a value in "column1" of table2

Show 3 more comments

0

If I understand what you’re wanting, you can use a simple JOIN:

SELECT tx.id AS "TabelaX ID", t2.id AS "Tabela2 ID", t2.coluna1, t2.coluna2 FROM TabelaX tx JOIN Tabela2 t2 ON (tx.coluna1 = t2.id OR tx.coluna2 = t2.id);

0

Analyzing the expected result that you mentioned, I think that to solve using Queries in select, I could not see a way using Union, Join or from in the two tables as you tried to do.

select
    x.id,
    x.coluna1,
    x.coluna2,
    (
        select coluna1 
        from tabela2 t2 
        where x.coluna1 = t2.id 
        limit 1
    ) as coluna1val,
    (
        select coluna1 
        from tabela2 t2 
        where x.coluna2 = t2.id 
        limit 1
    ) as coluna2val
from tabelaX x

Browser other questions tagged sql join

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