SQL Always return all rows from LUT for each ID

I am wondering if this query can be modified to achieve what I want:

SELECT 
cv.[ID]
,cv.[CustomValue]
,cf.[SpecialInformationId]
FROM #CustomFields cf
FULL OUTER JOIN #CustomValues cv ON cf.SpecialInformationId = cv.id

This returns all cv.Id's. It also returns any unmatched cf.SpecialInformationId's with a NULL for the cv information. What I actually want is for each instance of cv, I want every cf to show. cf is a lookup table. In this instance there are 12 values, but that varies everytime the query runs. Here is an example:

What this query currently returns:

cv.id   cv.customvalue   cf.specialinformationid
1           003            1
1           abc            2
2           004            1
2           1/1/2010       4 
2         开发者_如何学JAVA  abc            2
3           009            1
4           003            1
4           acb            2
4           1/2/2010       4

What I want it to return:

cv.id   cv.customvalue   cf.specialinformationid
1          003               1
1          abc               2
1          NULL              3
1          NULL              4
1          NULL              5
2          004               1
2          abc               2
2          NULL              3
2          1/1/2010          4
2          NULL              5
3          009               1
3          NULL              2
3          NULL              3
3          NULL              4
3          NULL              5
4          003               1
4          acb               2
4          NULL              3
4          1/2/2010          4
4          NULL              5

A Left join cannot be used because there are only 12 rows in the lookup table so if a left join is used the same result will be achieved as the full outer join.

This is a spinoff of my other question: SQL 2 tables always return all rows from 1 table match existing on other

Thanks

---

I believe a CROSS JOIN will achieve the results you're looking for.

---

The problems are arising because your Table2 is not really a 'vehicle' table. Because the VehicleId does not uniquely identify a record in that table. This is where all of the confusion is coming from. So to solve that and get your problem to work I did a select distinct on table2 against the values in table 1 (I also did a select distinct for clarity, but it was not necessary.) Hope this helps.

CREATE TABLE #Table1 (Id INT)
CREATE TABLE #Table2 (VehicleID INT, Value VARCHAR(50), Table1ID INT)

INSERT INTO #Table1 VALUES (1),(2),(3),(4),(5)
INSERT INTO #Table2 VALUES (1, 't', 1),(1, 'q', 2),(3, 'w', 3),(3, 'e', 4),(4, 't', 1),(5, 'e', 1),(5, 'f', 2),(5, 'g', 4)

SELECT * FROM #Table1
SELECT * FROM #Table2

SELECT  t2.VehicleID, t2.Value
FROM    ( SELECT    t2.VehicleId, t1.Id
          FROM      ( SELECT DISTINCT
                                VehicleId
                      FROM      #Table2 ) t2
                    CROSS JOIN ( SELECT Id
                                 FROM   #Table1 ) t1 ) Base
        LEFT JOIN #Table2 t2
            ON Base.VehicleId = t2.VehicleID
               AND Base.Id = t2.Table1ID
WHERE (Base.VehicleId BETWEEN 1 AND 3)

DROP TABLE #Table1
DROP TABLE #Table2