Join SQL server

Question

Join SQL server

Asked 5 years, 10 months ago

Viewed 73 times

1

Good afternoon, I started working with sql to 3 mesês and I’m picking up on an application.

I have 3 Tables which I have information associated with by "registration"

I would like to get all the information from the first table called configuration even if it is not associated with the other divides and records.

sql = "SELECT * 
    FROM dividas a 
    LEFT JOIN registros r on (a.registro = r.registro)  
    INNER JOIN configuracao b on r.registro = b.registro 
    WHERE a.empresa='" & LEFT(Combo1.Text, 2) & "' 
    ORDER BY r.registro"

In this query it brings the information if you have record information in the 3 Tables, I want you to bring also the information you only have in "configuration" even if you do not have in "debts" and "records"

If anyone can help me.

Thank you!

You can show us the relationship between the tables?

– Barbetta

2018/08/15 at 20:45
As I do not know the template I will add here, if solve game in response, try the following qury: Select 
 * 
from configuracao b
 LEFT Join registros r on r.registro = b.registro 
 LEFT join dividas a on a.registro = r.registro
where a.empresa='" & Left(Combo1.Text, 2) & "' 
order by r.registro

– Barbetta

2018/08/15 at 20:48
1

Rodrigo, it is VERY important to understand the JOINS (LEFT, INNER, RIGHT, FULL). The ideal first of all, would be you understand them, try to do (which I’m sure you’ll get). This statement "if you have record information in the 3 Tables" may be incorrect, because you have a "LEFT" of the table registros, then bring the independent records if you have or not in the table dividas. Count on us to help you. Look at this JOINS table that will help you: Link 1, Link 2

– rbz

2018/08/16 at 11:05
Good morning, first of all thanks for the tips.. I tried the query and it brought me the same answer, I will send the grid of each table: configuration (which is the one I want to bring everything) Note: The records 3700 and 3800 are only in this table and these are the ones that do not appear in the others. REG NAME DT ADM STATUS DT DEM PAG 3000 BELTRANO X Y Z B 3700 NOMECONFIG X Y Z B 3800 NOMECONFIG2 X Y Z B 3300 NOMEQUALQUER X Y Z B In the table records and divides comes all information less the 3700 and 3800. ñ managed to send an image to explain better, I can send via email if it becomes difficult to understand

– Rodrigo Merce

2018/08/16 at 11:48

1 answer

Browser other questions tagged sql server join

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

by rLinhares • **7,797** points · Answer 1 · 2018-08-16T11:06:50+00:00

Considering only the code you passed, you can try the following query:

sql = "SELECT * 
    FROM configuracao b
    LEFT JOIN dividas a on b.registro = a.registro and a.empresa='" & LEFT(Combo1.Text, 2) & "' 
    LEFT JOIN registros r on (a.registro = r.registro)  
    ORDER BY r.registro"

Just move configuracao to be the main table of the query. Making a LEFT JOIN with the other tables; that is, if there is any occurrence in the table of configuracao it will bring the value returned.

But this change also forces you to move your WHERE a.empresa='" & LEFT(Combo1.Text, 2) & "' to the Join table dividas (if we maintain the condition it will only bring a return if there is someone in dividas that meets the condition).

Just as an observation, the query of the question makes a left Join with registros and a Inner Join with configuracao; in other words, it brings a return if there are occurrences in dividas and in configuracao (only not being necessary occurrence in the table registros).