help with 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with query

Author

Topic

lovesirius12
Starting Member

8 Posts

Posted - 2007-03-16 : 03:25:35

please help me!

i got two tables. tblSales and tblBalance. i got to get records from both tables in reference to date. tblSales have Sales_Date while tblBalance have Balance_Date. and those dates are still filtered on Client_Code which both have.

so my initial query is:

[CODE]SELECT *
FROM tblSales FULL OUTER JOIN tblBalance
ON tblSales.client_code = tblBalance.client_code[/CODE]

i am supposed to add:
[CODE]WHERE tblSales.Sales_Date = tblBalance.Balance_Date[/CODE]

but it is like giving a relationship to this two, right? and might as well put "AND" instead of "WHERE".

i need to get all the dates in two table in reference with the Client_Code, but it should also be in ascending order.

for example:
tblSales have this dates with Client_Code = 1
[CODE]2007/01/01
2007/02/01
2007/03/01
2007/04/01
2007/05/01[/CODE]

and tblBalance have this dates with the same Client_Code (1)
[CODE]2007/01/16
2007/01/22
2007/01/30
2007/03/15
2007/05/08
[/CODE]
the result should be

[CODE]2007/01/01 --- tblSales
2007/01/16 --- tblBalance
2007/01/22 --- tblBalance
2007/01/30 --- tblBalance
2007/02/01 --- tblSales
2007/03/01 --- tblSales
2007/03/15 --- tblBalance
2007/04/01 --- tblSales
2007/05/01 --- tblSales
2007/05/08 --- tblBalance[/CODE]

but it doesn't show like that, it shows like this
[CODE]2007/01/01 --- tblSales
2007/01/16 --- tblBalance
2007/02/01 --- tblSales
2007/01/22 --- tblBalance
2007/03/01 --- tblSales
2007/01/30 --- tblBalance
2007/04/01 --- tblSales
2007/03/15 --- tblBalance
2007/05/01 --- tblSales
2007/05/08 --- tblBalance[/CODE]

i already put "ORDER BY" but it just sorted the dates on its respected column. the idea is to sort these dates as one. if possible.
help please!!!

the Lord provides

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-16 : 03:45:33

[code]
select Sales_Date from tblSales where Client_Code = 1
union all
select Balance_Date from tblBalance where Client_Code = 1
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 03:52:11

[code]
select theDate, Col2
from (
select Sales_Date as theDate, 'tblSales' as Col2 from tblSales where Client_Code = 1
union
select Balance_Date, 'tblBalance' from tblBalance where Client_Code = 1
) as x
order by 1, 2[/code]
Peter Larsson
Helsingborg, Sweden

lovesirius12
Starting Member

8 Posts

Posted - 2007-03-16 : 04:04:05

tnx a lot Peso. really really thanks a lot. that solved my problem. thanks a lot.

the Lord provides

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 04:36:40

khtan was the one leading us to the right decision.

Peter Larsson
Helsingborg, Sweden

lovesirius12
Starting Member

8 Posts

Posted - 2007-03-18 : 20:16:48

oh yeah. sorry khtan. thanks a lot too. :)

the Lord provides

lovesirius12
Starting Member

8 Posts

Posted - 2007-03-18 : 21:10:53

sorry to bother you again peepz.

got another question, i tried to solve this on my own, but i still can't figure it out.

in regard with those two tables above. i used the query that khtan and peso posted.

the tables would look like this

tblSales
Sales_Date
Client_Code
Amount

tblBalance
Balance_Date
Client_Code
Balance
Price

tblClient
Client_Code
Name

i tried to include in select statement the other fields, like this:

select theDate, theCode
from (
select Sales_Date as theDate, Client_Code as theCode, Amount, NULL as Expr1
from tblSales 
union
select Balance_Date, Client_Code, Balance, Price
) as x
order by 1, 2

but i cannot select the Price field. and the thing is i need to connect to tblClient also to get the Name. sorry for the trouble. :)

the Lord provides

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-18 : 21:48:21

[code]
select theDate, theCode, Expr1, c.[Name] as theName
from (
select Sales_Date as theDate, Client_Code as theCode, Amount, NULL as Expr1
from tblSales
union
select Balance_Date, Client_Code, Balance, Price
) as x
inner join tblClient c
on x.theCode = c.Client_Code
order by 1, 2
[/code]

lovesirius12
Starting Member

8 Posts

Posted - 2007-03-18 : 22:00:39

thanks a lot, KHTAN, that's a really big help. i'll just try to find a way to find out how to select the Price field. :) thanks again.

the Lord provides

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-18 : 22:03:28

is the Price column available in tblSales ?

lovesirius12
Starting Member

8 Posts

Posted - 2007-03-18 : 22:19:28

no Khtan, it is not, it is only on tblBalance.

the Lord provides

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-18 : 22:30:25

so do you need to show the price for the record from tblSales ?

lovesirius12
Starting Member

8 Posts

Posted - 2007-03-18 : 22:36:39

No. i need to show the Price field which is in tblBalance.

the Lord provides

lovesirius12
Starting Member

8 Posts

Posted - 2007-03-18 : 23:27:12

it's okay now. i did the following query

select theDate, theCode, Expr1, c.[Name] as theName
from (
select Sales_Date as theDate, Client_Code as theCode, Amount, 0 as Expr1
from tblSales
union
select Balance_Date, Client_Code, Balance, Price
) as x
inner join tblClient c
on x.theCode = c.Client_Code
order by 1, 2

tnx a lot KHTAN

the Lord provides

pankaj.pareek@sparrowi.co
Starting Member

26 Posts

Posted - 2007-03-19 : 08:20:44

Help me guys

A table have 5 records only and you have to show 6 records using select query without inserting the record in the database and that record is not present in the database.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 08:22:20

pankaj,

Please post your question in a new thread.

Please include table structure, some sample data and the required result.

pankaj.pareek@sparrowi.co
Starting Member

26 Posts

Posted - 2007-03-20 : 03:30:05

I have posted my query in new thread titled "SQL Query Help"

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources