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)

CROSS JOIN?

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-02 : 17:39:12

I think I need a CROSS JOIN to make this work, but I can't seem to get it right. If I use DISTINCT, I get the expected results but I'm sure there's a better way.



SET NOCOUNT ON

CREATE TABLE Table1
(
	parm_defn_id int NOT NULL
)

CREATE TABLE Table2
(
	terminal_gt_id int NOT NULL,
	parm_defn_id int NOT NULL
)

INSERT INTO Table1 (parm_defn_id)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
UNION ALL SELECT 4 UNION ALL SELECT 5

INSERT INTO Table2 (terminal_gt_id, parm_defn_id)
SELECT 42, 1 UNION ALL SELECT 42, 3 
UNION ALL SELECT 98, 1 UNION ALL SELECT 98, 3
UNION ALL SELECT 66, 1 UNION ALL SELECT 66, 3
UNION ALL SELECT 32, 1 UNION ALL SELECT 32, 3 UNION ALL SELECT 32, 4

SELECT t2.terminal_gt_id, t1.parm_defn_id
FROM Table1 t1
CROSS JOIN Table2 t2

DROP TABLE Table1
DROP TABLE Table2

Notice the dups in the result set. Now if I do this, I get what I need:

SELECT DISTINCT t2.terminal_gt_id, t1.parm_defn_id
FROM Table1 t1
CROSS JOIN Table2 t2

Expected result set:



terminal_gt_id parm_defn_id 
-------------- ------------ 
32             1
32             2
32             3
32             4
32             5
42             1
42             2
42             3
42             4
42             5
66             1
66             2
66             3
66             4
66             5
98             1
98             2
98             3
98             4
98             5

DISTINCT just doesn't seem right though.

I'd like to further refine my query. I'd like to exclude parm_defn_ids that already have a row for that terminal_gt_id. Here's the expected result set:



terminal_gt_id parm_defn_id 
-------------- ------------ 
32             2
32             5
42             2
42             4
42             5
66             2
66             4
66             5
98             2
98             4
98             5

Note terminal_gt_id of 32 is different from the others. It already has 4 for parm_defn_id where as the others do not so that's why 32 doesn't have 4 in this result set.

Does a CROSS JOIN automatically order the results? I was surprised to see the ordering be terminal_gt_id, parm_defn_id since I didn't specify it.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-02 : 18:39:57

MAybe something like this:

select distinct d.*
from
(
	select t2.terminal_gt_id, t1.parm_defn_id
	from Table1 t1
	cross join Table2 t2
) d
full outer join table2 t2 on t2.terminal_gt_id = d.terminal_gt_id and t2.parm_defn_id = d.parm_defn_id
where t2.terminal_gt_id is null

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-02 : 19:29:40

Is this basically what you have:

a table of Terminals
a table of Parmeters
a table that matches terminals to parameters -- PK of TerminalID/ParamID ?

and you want: all combos of Terminals/Params that don't exist in that last table?

if so, you start by creating all possible combos of terminals/parameters with a cross join, and then do a left outer join to your data. return rows in which there is no match in the outer table.


select 
  T.TerminalID, P.ParamID
from 
  Terminals T
cross join
  Parameters P
left outer join 
  TerminalsParameters TP
on
 T.TerminalID = TP.TerminalID and
 P.ParameterID = Tp.ParameterID
where
 TP.TerminalID is null

This is what I call a classic (A x B) -> C query.

- Jeff

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-02 : 21:10:07

My $.02

select t2.terminal_gt_id, t1.parm_defn_id
from Table1 t1
join (
select t2a.terminal_gt_id
from Table2 t2a
group by t2a.terminal_gt_id
) t2

on t1.parm_defn_id not in (
select t2b.parm_defn_id
from Table2 t2b
where t2b.terminal_gt_id = t2.terminal_gt_id
)
order by t2.terminal_gt_id, t1.parm_defn_id

HTH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-03 : 12:33:42

Jay's and Bustaz' code work great.

Jeff, I do have the situation that you describe, but I can not use the data from the Terminals table. It contains a lot more rows than I need to process. The only rows that I need to process are the terminals found in the TerminalParameters table. So here's the real situation. New parameters were added for the next version of this application. So if any terminal in the TerminalParameters table does not have every single parameter in the Parameters table, then I need to insert it (for this exercise just a select though will do). Can your solution be adapted to this?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-03 : 13:31:07

you can just use the distinct set of terminals from the "TerminalsParameters" table:


select 
  T.TerminalID, P.ParamID
from 
  (select distinct TerminalID from TerminalsParameters ) T
cross join
  Parameters P
left outer join 
  TerminalsParameters TP
on
 T.TerminalID = TP.TerminalID and
 P.ParameterID = Tp.ParameterID
where
 TP.TerminalID is null

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-03 : 13:43:46

Works great, Jeff.

Question on CROSS JOINs from my original post:

Does a CROSS JOIN automatically order the results? I was surprised to see the ordering be terminal_gt_id, parm_defn_id since I didn't specify it.

I didn't find the answer in BOL. There's very little info on CROSS JOINs in there.

Tara

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-03 : 18:56:33

CROSS JOINS do not inherently order the data. The only thing that guarantees the ordering is the ORDER BY clause. Everything else is "an unordered set of data".

Will the database often return the data order by its primary keys? Sure. But it's still no guarantee.

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources