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 |
|
dirac
Starting Member
12 Posts |
 Posted - 2007-06-08 : 09:11:37
|
| hi there , i'm using sql server 2005 express and i have some problems with the two of my tables :), iwant to query both of my tables but the table entries are not in an equal count of rows for example:incoming(table) outgoing(table) money date corp money date corp15 1,1,2006 ar 17 1,1,2006 ar25 1,2,2007 ar 21 2,2,2007 es35 2,2,2007 es6 3,3,2007 arin this example the first table has more rows but the opposite is possible in my tables because this is an (account extract) query and here's my problem: first i used the "select incoming.money as m1,incoming date as d1,incoming.corp as c1,outgoing.money as m2,outgoing.date as d2,outgoing.corp as c2 from incoming left outer join outgoing on incoming.corp=outgoing.corp where incoming.corp='ar'(or don't use the where eliminating)" any way i couldn't get the correct results, then i used the (union all) select , the result was ok but the results form is not what i want here's the form i want and is it possible to query to tables to get this?in this query i just wanted the elements with the corporation named 'ar' for example: m1 m2 d1 d2 c1 c2 15 17 1,1,2006 1,1,2006 ar ar 25 null(or (0)) 1,2,2007 6 null(or (0)) 3,3,2007is it possible to achieve this form(and may be any of the tables may have more rows than the other, it's not exact) , i am in a very difficult position :) and now i need your help , thanks anybody to think of helping me |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-06-08 : 09:58:36
|
| Hi,according to u r join condition it is not possible to get the records properly.u may get m1 and m2 different if many 'ar' exists in 2 tbls.u need to join on date also.To get the desired out put try to use temporary table wit hu r fields.Firs t insert from first table, and then update those records with 2 table |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-06-08 : 10:27:51
|
I think you want a FULL OUTER JOIN:select incoming.money as m1, incoming date as d1, incoming.corp as c1, outgoing.money as m2, outgoing.date as d2, outgoing.corp as c2from incoming full outer join outgoing on incoming.corp=outgoing.corp e4 d5 xd5 Nf6 |
|
|
|
dirac
Starting Member
12 Posts |
Posted - 2007-06-08 : 10:39:54
|
| thanks for your interest peterneo but that tables inserting or updating themselves seperately, an thanks for you blindman for the answer i tried the solution of you but gave me hundreds of results and rows(even i limited with a where clause)what can i do i am desperate now :( |
|
|
|
dirac
Starting Member
12 Posts |
Posted - 2007-06-08 : 10:44:50
|
| actually i think i must explain the tables( it will be better): the INCOMING table is a table that's holding the records of the receipts and the (amount of the receipts) which i bought from corporations, and the other table which name is OUTGOING is holding the payments that i pay to the corporations(and sometimes they send me one receipt and i pay them in two parts and in two different dates and amounts, and sometime they send me two seperate receipts and i pay them as a single payment, because of the situation both tables can't have a relationship and they have different row counts time to time ) :( |
|
|
|
|
|
|
|
|
a strange query