Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
campaine
Starting Member
6 Posts |
 Posted - 2014-09-15 : 17:25:24
|
| good night I have a problem. I'm doing a query from two tables. the table "pr" and the table "prre" with the following data: pr.no, pr.nome, pr.data, pr.recibo, pr.ettsuj, pr.ettdesc, prre.cr pr.eliquido, prre.rqtt , prre.ervu, prre.ere I'm doing the following query: select cast(pr.no as varchar) as 'Numero',pr.nome as 'Nome',pr.data as 'Data',cast(pr.recibo as varchar) as 'Recibo', cast(prre.rqtt as integer) as 'Qt Dias', prre.ervu as 'valor unitario',prre.ere as 'Total Subsidio',pr.ettsuj as 'Total iliquido', pr.ettdesc as 'Total Descontos', pr.eliquido as 'Total Liquido'from pr inner join prre on prre.prstamp=pr.prstampReturns this but the repeated lines. can someone help me? for this query is a list of salaries, with the fields, official number, expiration, name, date, receipt number, net total, the total gross, days subsidy amount, subsisidio Thank you |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-09-15 : 17:33:51
|
That usually means that there are not enough conditions on your JOIN clause. For example, you may need to join on prstamp AND the no column? ....from prinner join prre on prre.prstamp=pr.prstamp AND prre.no = pr.no I am just guessing here, the table prre may not have an "no" column. But the idea is that you have to figure out what the appropriate join condition is based on the relationship between the two tables. |
 |
|
|
campaine
Starting Member
6 Posts |
Posted - 2014-09-15 : 17:43:22
|
| I'm new with SQL the junction of the two tables is the prre.prstamp = pr.prstamp. I need to do but repeat the query data, I do not know if the inner join is necessary if there is an easier way to do the query this to return it but I just need a linehttp://imagizer.imageshack.us/v2/1024x768q90/904/15GN6t.jpg |
|
|
|
campaine
Starting Member
6 Posts |
Posted - 2014-09-18 : 08:00:42
|
| can someone help me ? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2014-09-18 : 08:11:26
|
| Unless you give actual query output and how you want it to be, it is really difficult for us to give correct solution.As a wild guess, you may try using DISTINCT in the SELECT statement to remove duplicates.Harsh Athalyehttp://www.letsgeek.net/ |
|
|
|
campaine
Starting Member
6 Posts |
Posted - 2014-09-18 : 10:15:44
|
| I that this query is to return thishttps://imagizer.imageshack.us/v2/815x298q90/904/15GN6t.jpg |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-09-18 : 10:31:59
|
| Looking at the screenshot you posted, for Numero=7, there are four rows. Which one of those rows do you want to get in the output? It matters which one you pick because the columns Qt Dias, valor untario, Total Subsidio etc. will return different values depending on which of the four rows is picked. |
|
|
|
campaine
Starting Member
6 Posts |
Posted - 2014-09-18 : 13:56:01
|
| I did this query is working and do not know if there is a way to improve?select pr.nome, pr.data, cast(pr.recibo as varchar) as 'Recibo', Qtd_Dias = (select isnull( sum (prre.rqtt),0) from prre where prre.prstamp=pr.prstamp and prre.cr in ('25', '28')), Valor_Unitario= (select isnull( sum (prre.ervu ),0) from prre where prre.prstamp=pr.prstamp and prre.cr in ('25', '28')), Total_Sub = (select isnull( sum (prre.ere),0) from prre where prre.prstamp=pr.prstamp and prre.cr in ('25', '28')), pr.ettsuj as 'Total iliquido', pr.ettdesc as 'Total Descontos', pr.eliquido as 'Total Liquido'from pr |
|
|
|
|
|
|
|
|
query two tables