how to do 'left join' on cross join tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

how to do 'left join' on cross join tables?

Author

Topic

xianwinwin
Starting Member

17 Posts

Posted - 2007-04-02 : 23:48:24

Hi everyone,

The query below returns 2 numbers --each is a sum of a given period. the names are accumulated and net_accumulated. The query works fine as long as there are values on BOTH tables.

when net_accumulated = NULL and the accumulated =5000 the return result is NULL.

can anyone advise how to have a solution such as 5000 and 0 ?

thank you

select Accumulated, net_accumulated
from
(
SELECT SUM(CSV.cur_amount) AS Accumulated
FROM csv_production AS CSV
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv.ID
) a
cross join
(
SELECT SUM(CSV_net.cur_amount) AS net_accumulated
FROM csv_net_production AS CSV_NET
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv_net.ID
) b

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 00:27:59

select coalesce(accumulated, 0) AS accumulated, coalesce(net_accumulated, 0) AS net_accumulated
from ...

Peter Larsson
Helsingborg, Sweden

xianwinwin
Starting Member

17 Posts

Posted - 2007-04-03 : 00:32:53

thank you Peso, but it didnt work.
any idea?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 00:45:12

Can you tell us WHY you think it does not work?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 00:51:25

"Can you tell us WHY you think it does not work?"
because no records return in either one of the query


select Accumulated, net_accumulated
from
(
    SELECT SUM(CSV.cur_amount) AS Accumulated
    FROM csv_production AS CSV
    WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
    GROUP BY csv.ID
) a
cross join full outer join
(
    SELECT SUM(CSV_net.cur_amount) AS net_accumulated
    FROM csv_net_production AS CSV_NET
    WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
    GROUP BY csv_net.ID
) b
on 1 = 2

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 00:54:03

or assign the results to local variables


declare @Accumulated     int,
        @net_accumulated int

SELECT @Accumulated = SUM(CSV.cur_amount)
FROM csv_production AS CSV
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv.ID

SELECT @net_accumulated = SUM(CSV_net.cur_amount)
FROM csv_net_production AS CSV_NET
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv_net.ID

select @Accumulated as Accumulated, @net_accumulated as net_accumlated,

KH

xianwinwin
Starting Member

17 Posts

Posted - 2007-04-03 : 01:06:19

>"Can you tell us WHY you think it does not work?"

not sure. the tables csv_net and csv share the same structure and the common column is the ID. when I do this:

SELECT SUM(CSV.cur_amount) AS Accumulated
FROM csv_production AS CSV
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv.ID

I get 50000

when I exec this:

SELECT SUM(CSV_net.cur_amount) AS net_accumulated
FROM csv_net_production AS CSV_NET
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv_net.ID

I get nothing (not even 0)

so I'm not sure what causes this. I though LEFT JOIN will solve it but not sure how to integrate LEFT JOIN with cross

KH's solution:

select Accumulated, net_accumulated
from
(
SELECT SUM(CSV.cur_amount) AS Accumulated
FROM csv_production AS CSV
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv.ID
) a
cross join full outer join
(
SELECT SUM(CSV_net.cur_amount) AS net_accumulated
FROM csv_net_production AS CSV_NET
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv_net.ID
) b
on 1 = 2

return an sql error:

[root@loclahost:3306] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join
(
SELECT SUM(CSV_net.cur_amount) AS net_accumulated
FROM' at line 9

Thank you all for trying

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 01:08:18

Note that, i strike out the ~~cross join~~ and replace with full outer join

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 01:10:51

quote:

when I exec this:


SELECT SUM(CSV_net.cur_amount) AS net_accumulated
FROM csv_net_production AS CSV_NET
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv_net.ID

I get nothing (not even 0)

That means there isn't any records that matches the condition that you specify in the WHERE clause.
Try a simple

SELECT * FROM csv_net_production

and check the result

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 01:13:12

quote:
return an sql error:
[root@loclahost:3306] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join

Pardon my ignorance, What tools are you using for running this query ?

KH

xianwinwin
Starting Member

17 Posts

Posted - 2007-04-03 : 01:15:31

Yes, I realized you removed the cross join.

I exec this:

select Accumulated, net_accumulated
from
(
SELECT SUM(CSV.cur_amount) AS Accumulated
FROM csv_production AS CSV
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv.ID
) a
full outer join
(
SELECT SUM(CSV_net.cur_amount) AS net_accumulated
FROM csv_net_production AS CSV_NET
WHERE ID='5636300' AND Production_Date BETWEEN '2006-05-01' AND '2006-08-31'
GROUP BY csv_net.ID
) b
on a = b

the same error returned:

[root@loclahost:3306] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join
(
SELECT SUM(CSV_net.cur_amount) AS net_accumulated
FROM' at line 9

>That means there isn't any records that matches the condition that >you specify in the WHERE clause.
correct

>Try a simple SELECT * FROM csv_net_production

this returns all columns + some result of diff period.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 01:20:49

quote:
MySQL server

Ah ! I missed this keyword. My query will works for MS SQL Server. I am not sure about MySQL.

For your information, this is a Microsoft SQL Server forums and you have posted your question in SQL Server 2000 Forums - SQL Server Development (2000).

Try posting your MySQL question over at dbforums.com or http://forums.mysql.com/

KH

xianwinwin
Starting Member

17 Posts

Posted - 2007-04-03 : 01:27:48

will do

THANK YOU for everything!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources