Query without cross join - 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 2000 Forums

Transact-SQL (2000)

Query without cross join

Author

Topic

Hillside
Starting Member

23 Posts

Posted - 2007-08-07 : 10:51:04

Hi Guys thanks for your earlier help.

Here i am stuck again with performance issue and there are 2 tables and they dont have any link and I need join them without using cross join.

I hope you experts help me....

Table

DECLARE @Table1 TABLE (t06_t06_id INT, t06_date DATETIME, t06_value decimal(10,8))

INSERT @Table1
SELECT 59, '2005-11-09 00:00:00' ,6.5890 UNION ALL
SELECT 59, '2005-11-16 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2005-12-21 00:00:00' ,3.6600 UNION ALL
SELECT 59, '2005-12-28 00:00:00' ,3.6033 UNION ALL
SELECT 59, '2006-01-04 00:00:00' ,3.5793 UNION ALL
SELECT 59, '2006-01-11 00:00:00' ,3.5544 UNION ALL
SELECT 59, '2006-01-18 00:00:00' ,3.4872 UNION ALL
SELECT 59, '2006-01-25 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2006-02-08 00:00:00' ,3.5888

Table2
2002-06-26 00:00:00
2002-07-03 00:00:00
2002-07-10 00:00:00
2002-07-17 00:00:00
2002-07-24 00:00:00
2002-07-31 00:00:00
2002-08-07 00:00:00
2002-08-14 00:00:00
2002-08-21 00:00:00
2002-08-28 00:00:00
2002-09-04 00:00:00
2002-09-11 00:00:00
2002-09-18 00:00:00
2002-09-25 00:00:00
2002-10-02 00:00:00
2002-10-09 00:00:00
2002-10-16 00:00:00
2002-10-23 00:00:00
2002-10-30 00:00:00
2002-11-06 00:00:00
2002-11-13 00:00:00
2002-11-20 00:00:00
2002-11-27 00:00:00
2002-12-04 00:00:00
2002-12-11 00:00:00
2002-12-18 00:00:00
2002-12-25 00:00:00
2003-01-01 00:00:00
2003-01-08 00:00:00
2003-01-15 00:00:00
2003-01-22 00:00:00
2003-01-29 00:00:00
2003-02-05 00:00:00
2003-02-12 00:00:00
2003-02-19 00:00:00
2003-02-26 00:00:00
2003-03-05 00:00:00
2003-03-12 00:00:00
2003-03-19 00:00:00
2003-03-26 00:00:00
2003-04-02 00:00:00
2003-04-09 00:00:00
2003-04-16 00:00:00
2003-04-23 00:00:00
2003-04-30 00:00:00
2003-05-07 00:00:00
2003-05-14 00:00:00
2003-05-21 00:00:00
2003-05-28 00:00:00
2003-06-04 00:00:00
2003-06-11 00:00:00
2003-06-18 00:00:00
2003-06-25 00:00:00
2003-07-02 00:00:00
2003-07-09 00:00:00
2003-07-16 00:00:00
2003-07-23 00:00:00
2003-07-30 00:00:00
2003-08-06 00:00:00
2003-08-13 00:00:00
2003-08-20 00:00:00
2003-08-27 00:00:00
2003-09-03 00:00:00
2003-09-10 00:00:00
2003-09-17 00:00:00
2003-09-24 00:00:00
2003-10-01 00:00:00
2003-10-08 00:00:00
2003-10-15 00:00:00
2003-10-22 00:00:00
2003-10-29 00:00:00
2003-11-05 00:00:00
2003-11-12 00:00:00
2003-11-19 00:00:00
2003-11-26 00:00:00
2003-12-03 00:00:00
2003-12-10 00:00:00
2003-12-17 00:00:00
2003-12-24 00:00:00
2003-12-31 00:00:00
2004-01-07 00:00:00
2004-01-14 00:00:00
2004-01-21 00:00:00
2004-01-28 00:00:00
2004-02-04 00:00:00
2004-02-11 00:00:00
2004-02-18 00:00:00
2004-02-25 00:00:00
2004-03-03 00:00:00
2004-03-10 00:00:00
2004-03-17 00:00:00
2004-03-24 00:00:00
2004-03-31 00:00:00
2004-04-07 00:00:00
2004-04-14 00:00:00
2004-04-21 00:00:00
2004-04-28 00:00:00
2004-05-05 00:00:00
2004-05-12 00:00:00
2004-05-19 00:00:00
2004-05-26 00:00:00
2004-06-02 00:00:00
2004-06-09 00:00:00
2004-06-16 00:00:00
2004-06-23 00:00:00
2004-06-30 00:00:00
2004-07-07 00:00:00
2004-07-14 00:00:00
2004-07-21 00:00:00
2004-07-28 00:00:00
2004-08-04 00:00:00
2004-08-11 00:00:00
2004-08-18 00:00:00
2004-08-25 00:00:00
2004-09-01 00:00:00
2004-09-08 00:00:00
2004-09-15 00:00:00
2004-09-22 00:00:00
2004-09-29 00:00:00
2004-10-06 00:00:00
2004-10-13 00:00:00
2004-10-20 00:00:00
2004-10-27 00:00:00
2004-11-03 00:00:00
2004-11-10 00:00:00
2004-11-17 00:00:00
2004-11-24 00:00:00
2004-12-01 00:00:00
2004-12-08 00:00:00
2004-12-15 00:00:00
2004-12-22 00:00:00
2004-12-29 00:00:00
2005-01-05 00:00:00
2005-01-12 00:00:00
2005-01-19 00:00:00
2005-01-26 00:00:00
2005-02-02 00:00:00
2005-02-09 00:00:00
2005-02-16 00:00:00
2005-02-23 00:00:00
2005-03-02 00:00:00
2005-03-09 00:00:00
2005-03-16 00:00:00
2005-03-23 00:00:00
2005-03-30 00:00:00
2005-04-06 00:00:00
2005-04-13 00:00:00
2005-04-20 00:00:00
2005-04-27 00:00:00
2005-05-04 00:00:00
2005-05-11 00:00:00
2005-05-18 00:00:00
2005-05-25 00:00:00
2005-06-01 00:00:00
2005-06-08 00:00:00
2005-06-15 00:00:00
2005-06-22 00:00:00
2005-06-29 00:00:00
2005-07-06 00:00:00
2005-07-13 00:00:00
2005-07-20 00:00:00
2005-07-27 00:00:00
2005-08-03 00:00:00
2005-08-10 00:00:00
2005-08-17 00:00:00
2005-08-24 00:00:00
2005-08-31 00:00:00
2005-09-07 00:00:00
2005-09-14 00:00:00
2005-09-21 00:00:00
2005-09-28 00:00:00
2005-10-05 00:00:00
2005-10-12 00:00:00
2005-10-19 00:00:00
2005-10-26 00:00:00
2005-11-02 00:00:00
2005-11-09 00:00:00
2005-11-16 00:00:00
2005-11-23 00:00:00
2005-11-30 00:00:00
2005-12-07 00:00:00
2005-12-14 00:00:00
2005-12-21 00:00:00
2005-12-28 00:00:00
2006-01-04 00:00:00
2006-01-11 00:00:00
2006-01-18 00:00:00
2006-01-25 00:00:00
2006-02-01 00:00:00
2006-02-08 00:00:00
2006-02-15 00:00:00
2006-02-22 00:00:00
2006-03-01 00:00:00
2006-03-08 00:00:00
2006-03-15 00:00:00
2006-03-22 00:00:00
2006-03-29 00:00:00
2006-04-05 00:00:00
2006-04-12 00:00:00
2006-04-19 00:00:00
2006-04-26 00:00:00
2006-05-03 00:00:00
2006-05-10 00:00:00
2006-05-17 00:00:00
2006-05-24 00:00:00
2006-05-31 00:00:00
2006-06-07 00:00:00
2006-06-14 00:00:00
2006-06-21 00:00:00
2006-06-28 00:00:00
2006-07-05 00:00:00
2006-07-12 00:00:00
2006-07-19 00:00:00
2006-07-26 00:00:00
2006-08-02 00:00:00
2006-08-09 00:00:00
2006-08-16 00:00:00
2006-08-23 00:00:00
2006-08-30 00:00:00
2006-09-06 00:00:00
2006-09-13 00:00:00
2006-09-20 00:00:00
2006-09-27 00:00:00
2006-10-04 00:00:00
2006-10-11 00:00:00
2006-10-18 00:00:00
2006-10-25 00:00:00
2006-11-01 00:00:00
2006-11-08 00:00:00
2006-11-15 00:00:00
2006-11-22 00:00:00
2006-11-29 00:00:00
2006-12-06 00:00:00
2006-12-13 00:00:00
2006-12-20 00:00:00
2006-12-27 00:00:00
2007-01-03 00:00:00
2007-01-10 00:00:00
2007-01-17 00:00:00
2007-01-24 00:00:00
2007-01-31 00:00:00
2007-02-07 00:00:00
2007-02-14 00:00:00
2007-02-21 00:00:00
2007-02-28 00:00:00
2007-03-07 00:00:00
2007-03-14 00:00:00
2007-03-21 00:00:00
2007-03-28 00:00:00
2007-04-04 00:00:00
2007-04-11 00:00:00
2007-04-18 00:00:00
2007-04-25 00:00:00
2007-05-02 00:00:00
2007-05-09 00:00:00
2007-05-16 00:00:00
2007-05-23 00:00:00
2007-05-30 00:00:00
2007-06-06 00:00:00
2007-06-13 00:00:00
2007-06-20 00:00:00
2007-06-27 00:00:00
2007-07-04 00:00:00
2007-07-11 00:00:00
2007-07-18 00:00:00

Please help me.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-07 : 11:00:52

How do you want to join them?
If you can define what you want you will probably find it easy.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hillside
Starting Member

23 Posts

Posted - 2007-08-07 : 11:14:06

Hi,

I need all table1 multiply by table2 with complete Ids and date value in all columns

thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-07 : 11:41:34

multiply what? and how?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Hillside
Starting Member

23 Posts

Posted - 2007-08-07 : 12:10:06

PLease find results below like that

59 2002-06-26 00:00:00 6.5180
59 2002-07-03 00:00:00 NULL
59 2002-07-10 00:00:00 NULL
59 2002-07-17 00:00:00 NULL
59 2002-07-24 00:00:00 NULL
59 2002-07-31 00:00:00 NULL
59 2002-08-07 00:00:00 NULL
59 2002-08-14 00:00:00 NULL
59 2002-08-21 00:00:00 NULL
59 2002-08-28 00:00:00 NULL
59 2002-09-04 00:00:00 NULL
59 2002-09-11 00:00:00 NULL
59 2002-09-18 00:00:00 NULL
59 2002-09-25 00:00:00 NULL
59 2002-10-02 00:00:00 NULL
59 2002-10-09 00:00:00 NULL
59 2002-10-16 00:00:00 NULL
59 2002-10-23 00:00:00 NULL
59 2002-10-30 00:00:00 NULL
59 2002-11-06 00:00:00 NULL
59 2002-11-13 00:00:00 NULL
59 2002-11-20 00:00:00 NULL
59 2002-11-27 00:00:00 NULL
59 2002-12-04 00:00:00 NULL
59 2002-12-11 00:00:00 NULL
59 2002-12-18 00:00:00 NULL
59 2002-12-25 00:00:00 NULL
59 2003-01-01 00:00:00 NULL
59 2003-01-08 00:00:00 NULL
59 2003-01-15 00:00:00 NULL
59 2003-01-22 00:00:00 NULL
59 2003-01-29 00:00:00 NULL
59 2003-02-05 00:00:00 NULL
59 2003-02-12 00:00:00 NULL
59 2003-02-19 00:00:00 NULL
59 2003-02-26 00:00:00 NULL
59 2003-03-05 00:00:00 NULL
59 2003-03-12 00:00:00 NULL
59 2003-03-19 00:00:00 NULL
59 2003-03-26 00:00:00 NULL
59 2003-04-02 00:00:00 NULL
59 2003-04-09 00:00:00 NULL
59 2003-04-16 00:00:00 NULL
59 2003-04-23 00:00:00 NULL
59 2003-04-30 00:00:00 NULL
59 2003-05-07 00:00:00 NULL
59 2003-05-14 00:00:00 NULL
59 2003-05-21 00:00:00 NULL
59 2003-05-28 00:00:00 NULL
59 2003-06-04 00:00:00 NULL
59 2003-06-11 00:00:00 NULL
59 2003-06-18 00:00:00 NULL
59 2003-06-25 00:00:00 NULL
59 2003-07-02 00:00:00 NULL
59 2003-07-09 00:00:00 NULL
59 2003-07-16 00:00:00 NULL
59 2003-07-23 00:00:00 NULL
59 2003-07-30 00:00:00 NULL
59 2003-08-06 00:00:00 NULL
59 2003-08-13 00:00:00 NULL
59 2003-08-20 00:00:00 NULL
59 2003-08-27 00:00:00 NULL
59 2003-09-03 00:00:00 NULL
59 2003-09-10 00:00:00 NULL
59 2003-09-17 00:00:00 NULL
59 2003-09-24 00:00:00 NULL
59 2003-10-01 00:00:00 NULL
59 2003-10-08 00:00:00 NULL
59 2003-10-15 00:00:00 NULL
59 2003-10-22 00:00:00 NULL
59 2003-10-29 00:00:00 NULL
59 2003-11-05 00:00:00 NULL
59 2003-11-12 00:00:00 NULL
59 2003-11-19 00:00:00 NULL
59 2003-11-26 00:00:00 NULL
59 2003-12-03 00:00:00 NULL
59 2003-12-10 00:00:00 NULL
59 2003-12-17 00:00:00 NULL
59 2003-12-24 00:00:00 NULL
59 2003-12-31 00:00:00 NULL
59 2004-01-07 00:00:00 NULL
59 2004-01-14 00:00:00 NULL
59 2004-01-21 00:00:00 NULL
59 2004-01-28 00:00:00 NULL
59 2004-02-04 00:00:00 NULL
59 2004-02-11 00:00:00 NULL
59 2004-02-18 00:00:00 NULL
59 2004-02-25 00:00:00 NULL
59 2004-03-03 00:00:00 NULL
59 2004-03-10 00:00:00 NULL
59 2004-03-17 00:00:00 NULL
59 2004-03-24 00:00:00 NULL
59 2004-03-31 00:00:00 NULL
59 2004-04-07 00:00:00 NULL
59 2004-04-14 00:00:00 NULL
59 2004-04-21 00:00:00 NULL
59 2004-04-28 00:00:00 NULL
59 2004-05-05 00:00:00 NULL
59 2004-05-12 00:00:00 NULL
59 2004-05-19 00:00:00 NULL
59 2004-05-26 00:00:00 NULL
59 2004-06-02 00:00:00 NULL
59 2004-06-09 00:00:00 NULL
59 2004-06-16 00:00:00 NULL
59 2004-06-23 00:00:00 NULL
59 2004-06-30 00:00:00 NULL
59 2004-07-07 00:00:00 NULL
59 2004-07-14 00:00:00 NULL
59 2004-07-21 00:00:00 NULL
59 2004-07-28 00:00:00 NULL
59 2004-08-04 00:00:00 NULL
59 2004-08-11 00:00:00 NULL
59 2004-08-18 00:00:00 NULL
59 2004-08-25 00:00:00 NULL
59 2004-09-01 00:00:00 NULL
59 2004-09-08 00:00:00 NULL
59 2004-09-15 00:00:00 NULL
59 2004-09-22 00:00:00 NULL
59 2004-09-29 00:00:00 NULL
59 2004-10-06 00:00:00 NULL
59 2004-10-13 00:00:00 NULL
59 2004-10-20 00:00:00 NULL
59 2004-10-27 00:00:00 NULL
59 2004-11-03 00:00:00 NULL
59 2004-11-10 00:00:00 NULL
59 2004-11-17 00:00:00 NULL
59 2004-11-24 00:00:00 NULL
59 2004-12-01 00:00:00 NULL
59 2004-12-08 00:00:00 NULL
59 2004-12-15 00:00:00 NULL
59 2004-12-22 00:00:00 NULL
59 2004-12-29 00:00:00 NULL
59 2005-01-05 00:00:00 NULL
59 2005-01-12 00:00:00 NULL
59 2005-01-19 00:00:00 NULL
59 2005-01-26 00:00:00 NULL
59 2005-02-02 00:00:00 NULL
59 2005-02-09 00:00:00 NULL
59 2005-02-16 00:00:00 NULL
59 2005-02-23 00:00:00 NULL
59 2005-03-02 00:00:00 NULL
59 2005-03-09 00:00:00 NULL
59 2005-03-16 00:00:00 NULL
59 2005-03-23 00:00:00 NULL
59 2005-03-30 00:00:00 NULL
59 2005-04-06 00:00:00 NULL
59 2005-04-13 00:00:00 NULL
59 2005-04-20 00:00:00 NULL
59 2005-04-27 00:00:00 NULL
59 2005-05-04 00:00:00 NULL
59 2005-05-11 00:00:00 NULL
59 2005-05-18 00:00:00 NULL
59 2005-05-25 00:00:00 NULL
59 2005-06-01 00:00:00 NULL
59 2005-06-08 00:00:00 NULL
59 2005-06-15 00:00:00 NULL
59 2005-06-22 00:00:00 NULL
59 2005-06-29 00:00:00 NULL
59 2005-07-06 00:00:00 NULL
59 2005-07-13 00:00:00 NULL
59 2005-07-20 00:00:00 NULL
59 2005-07-27 00:00:00 NULL
59 2005-08-03 00:00:00 NULL
59 2005-08-10 00:00:00 NULL
59 2005-08-17 00:00:00 NULL
59 2005-08-24 00:00:00 NULL
59 2005-08-31 00:00:00 NULL
59 2005-09-07 00:00:00 NULL
59 2005-09-14 00:00:00 NULL
59 2005-09-21 00:00:00 NULL
59 2005-09-28 00:00:00 NULL
59 2005-10-05 00:00:00 NULL
59 2005-10-12 00:00:00 NULL
59 2005-10-19 00:00:00 NULL
59 2005-10-26 00:00:00 NULL
59 2005-11-02 00:00:00 NULL
59 2005-11-09 00:00:00 NULL
59 2005-11-16 00:00:00 NULL
59 2005-11-23 00:00:00 NULL
59 2005-11-30 00:00:00 NULL
59 2005-12-07 00:00:00 NULL
59 2005-12-14 00:00:00 NULL
59 2005-12-21 00:00:00 3.6600
59 2005-12-28 00:00:00 3.6033
59 2006-01-04 00:00:00 3.5793
59 2006-01-11 00:00:00 3.5544
59 2006-01-18 00:00:00 3.4872
59 2006-01-25 00:00:00 3.6101
59 2006-02-01 00:00:00 3.7548
59 2006-02-08 00:00:00 3.7416
59 2006-02-15 00:00:00 3.7262
59 2006-02-22 00:00:00 3.6275
59 2006-03-01 00:00:00 3.7079
59 2006-03-08 00:00:00 3.8002
59 2006-03-15 00:00:00 3.9038
59 2006-03-22 00:00:00 3.8700
59 2006-03-29 00:00:00 3.9738
59 2006-04-05 00:00:00 4.0845
59 2006-04-12 00:00:00 4.1183
59 2006-04-19 00:00:00 4.2088
59 2006-04-26 00:00:00 4.2669
59 2006-05-03 00:00:00 4.2681
59 2006-05-10 00:00:00 4.2289
59 2006-05-17 00:00:00 4.2838
59 2006-05-24 00:00:00 4.1270
59 2006-05-31 00:00:00 4.2867
59 2006-06-07 00:00:00 4.3417
59 2006-06-14 00:00:00 4.2423
59 2006-06-21 00:00:00 4.3489
59 2006-06-28 00:00:00 4.4291
59 2006-07-05 00:00:00 4.4821
59 2006-07-12 00:00:00 4.4294
59 2006-07-19 00:00:00 4.3523
59 2006-07-26 00:00:00 4.3005
59 2006-08-02 00:00:00 4.2806
59 2006-08-09 00:00:00 4.3229
59 2006-08-16 00:00:00 4.3025
59 2006-08-23 00:00:00 4.2000
59 2006-08-30 00:00:00 4.1934
59 2006-09-06 00:00:00 4.2204
59 2006-09-13 00:00:00 4.1324
59 2006-09-20 00:00:00 4.1319
59 2006-09-27 00:00:00 4.0452
59 2006-10-04 00:00:00 4.0837
59 2006-10-11 00:00:00 4.1832
59 2006-10-18 00:00:00 4.1743
59 2006-10-25 00:00:00 4.2652
59 2006-11-01 00:00:00 4.0270
59 2006-11-08 00:00:00 4.0847
59 2006-11-15 00:00:00 4.0135
59 2006-11-22 00:00:00 4.0117
59 2006-11-29 00:00:00 4.0415
59 2006-12-06 00:00:00 3.9494
59 2006-12-13 00:00:00 4.0481
59 2006-12-20 00:00:00 4.1325
59 2006-12-27 00:00:00 4.2098
59 2007-01-03 00:00:00 4.2422
59 2007-01-10 00:00:00 4.3062
59 2007-01-17 00:00:00 4.3450
59 2007-01-24 00:00:00 4.3572
59 2007-01-31 00:00:00 4.4698
59 2007-02-07 00:00:00 4.3764
59 2007-02-14 00:00:00 4.4248
59 2007-02-21 00:00:00 4.3050
59 2007-02-28 00:00:00 4.2663
59 2007-03-07 00:00:00 4.2459
59 2007-03-14 00:00:00 4.1882
59 2007-03-21 00:00:00 4.2485
59 2007-03-28 00:00:00 4.3487
59 2007-04-04 00:00:00 4.4279
59 2007-04-11 00:00:00 4.4957
59 2007-04-18 00:00:00 4.5107
59 2007-04-25 00:00:00 4.5380
59 2007-05-02 00:00:00 4.5534
59 2007-05-09 00:00:00 4.5417
59 2007-05-16 00:00:00 4.6358
59 2007-05-23 00:00:00 4.7011
59 2007-05-30 00:00:00 4.7191
59 2007-06-06 00:00:00 4.7868
59 2007-06-13 00:00:00 4.9996
59 2007-06-20 00:00:00 5.0406
59 2007-06-27 00:00:00 4.9656
59 2007-07-04 00:00:00 5.0523
59 2007-07-11 00:00:00 5.0526
59 2007-07-18 00:00:00 5.0274

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-07 : 12:23:43

i'm sorry but this makes absolutly no sense.

where are you getting all those numbers? what are you multiplying and with what?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Hillside
Starting Member

23 Posts

Posted - 2007-08-07 : 14:34:47

When I make cross join to the above mentioned tables(table1 and table2) i will get the these results.It is very big table with 4 billion records and I cant use cross join bcos of performance issue.

I need to avoid cross to get same out put.

I hope cleared your doubts and if you need any clarification than please let me know

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-08 : 05:33:28

select a.t06_t06_id, t2.dte, t1.t06_value
from @Table2 t1
join (select distinct t06_t06_id from @Table1) a
left join @Table1 t1
on t1.t06_t06_id = a.t06_t06_id
and t1.t06_date = t2.dte

That should give a row for each date for the IDs with the values filled in if they exist.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources