CODEWITHSUNDEEP
sqlsql-servert-sql
Nate Pet
Nate Pet

Reputation: 46222

CROSS APPLY to JOIN tables that are not related

I noticed that the CROSS APPLY is used normally for functions but I used it in the following way:

SELECT pu.UserID UserID,  A.AlertID 
FROM tbl_Users pu
CROSS APPLY 
(Select AlertID from tbl_Alerts) A

Essentially for each user, I wanted to have it show the 2 alert that are in the tbl_Alerts table.

As such it would show somehting like: 

    UserID    AlertID
    1         1 
    1         2
    2         1
    2         2
    3         1
    3         2

Wondering if this is a legitimate use of CROSS APPLY in this case.

Upvotes: 1

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

First, it is only going to return one user id.

Is it legitimate? I think this is much simpler:

SELECT pu.UserID UserID,  A.AlertID 
FROM tbl_Users pu 
     CROSS JOIN tbl_Alerts A
WHERE pu.Userid = @UserID;

But your code is correct SQL.

Upvotes: 4

Related Questions