just query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

just query

Author

Topic

vital
Starting Member

12 Posts

Posted - 2008-05-16 : 08:16:47

hi there

i use the delphi and as database firebird.but i have a problem with query.
i have 3 tables they are master and details,table1->table2->table3
table1
id
prname

table2
table2id
table1id
prp

table3
table3id
table2id
prpieces

there are their fields

i use this query but this doesnt give me which result i want

select table2.id ,table2.prp-sum(table3.prpieces) from table2 left outer join table3 on table2.id=table3.id
this query give me this result
table2id sum
-------- ----
1 4
2 90
and now i wantto join in this query the table1 too i want get this result

table1id table2id sum
-------- -------- ----
x 1 4
c 2 90

this rule is a problem for me the table3 must not has the records
if i use this query it wont give me true result
because the table3 maybe doesnt has the records thats why it wont give the true result.

how i need to use or which query i need to use ?

select table1.prpname,table1.id table2.prp-sum(table3.prpieces) from table1 left outer join table2 on table1.id=table2.table1id and table2.id=table3.table2.id

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 11:13:20

What will be relationship b/w table1 and table2? Will it be always one to one?

vital
Starting Member

12 Posts

Posted - 2008-05-17 : 02:30:39

table1 table2 table3
------ ------- -------
id table1id table2id
urnad sipadet id
id geladet
yes they are one to one .these tables in relationship with master detail.

table1 table2 table3 there will tobe a table
id = table1id table2
there are foreignkeys .it will to be one to one .maybe on the table 3 wont to be record but it must give to me as result if there is record on the table2
i mean if there wont to be record on the table3 then that query wont give me the true result because of this table2id=table3id.it must give to me all record on the table1 but if there wont to be a result on the table2 then it cant to be onthe table3 either. how i can join all tables

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 03:09:42

quote:
Originally posted by vital

table1 table2 table3
------ ------- -------
id table1id table2id
urnad sipadet id
id geladet
yes they are one to one .these tables in relationship with master detail.

table1 table2 table3 there will tobe a table
id = table1id table2
there are foreignkeys .it will to be one to one .maybe on the table 3 wont to be record but it must give to me as result if there is record on the table2
i mean if there wont to be record on the table3 then that query wont give me the true result because of this table2id=table3id.it must give to me all record on the table1 but if there wont to be a result on the table2 then it cant to be onthe table3 either. how i can join all tables

thanks.

SELECT t1.id,t2.table2id,t2.prp-isnull(sum(t3.prppieces),0)
FROM table1 t1
LEFT JOIN table2 t2
ON t2.table1id=t1.id
LEFT JOIN table3 t3
ON t3.table2id=t2.table2id
GROUP BY t1.id,t2.table2id,t2.prp

vital
Starting Member

12 Posts

Posted - 2008-05-17 : 16:09:44

thank you very much visakh16

but if the table3 wont tobe one to one also i mean

table1 is in relation ship with table2 one to one
table2 is in relationship with table3 but the table2 and table3 is not one to one also like this

table1
------
3(table1.id)

table2
-------
3(table1.id)
1(table2.id)

table3
------
1(table2.id)
1(table2.id)
1(table2.id)
10(table3.id)

if the table1 and table2 are one to one and table3isnot one to one with table2 then how wil tobe query?

thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 16:15:40

quote:
Originally posted by vital

thank you very much visakh16

but if the table3 wont tobe one to one also i mean

table1 is in relation ship with table2 one to one
table2 is in relationship with table3 but the table2 and table3 is not one to one also like this

table1
------
3(table1.id)

table2
-------
3(table1.id)
1(table2.id)

table3
------
1(table2.id)
1(table2.id)
1(table2.id)
10(table3.id)

if the table1 and table2 are one to one and table3isnot one to one with table2 then how wil tobe query?

thanks a lot.

No problem if table2 table3 relation is not one to one as we are grouping on table1 and table2 fields so that you will get a single result and we are also using left join which returns the records even if not in table3.

vital
Starting Member

12 Posts

Posted - 2008-05-20 : 04:16:25

yes visakh16

it gives me which result i want .

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources