Originally Posted by
hansdbase
Suppose I have a table that has parts_no and parts_name with 5 parts totals. So 2 columns and 5 rows in other words. Parts_name is unique. And I have a second unrelated table of departments that has department_no and department_name with 4 departments total. So 2 columns and 4 departments. department_name is unique. When I run a query to select parts_name, department_name from parts, departments; the answer I get appears to cross join the rows.
But what I want is for it to create only 5 rows total with no duplicate values in any column and where there is not a corresponding value in the department table it should just put a null in that row.
IS this possible?
Are you trying to do something like assign a random part to each department? The only way I can think of doing it is to create a CTE for each so I can add ROW_NUMBER() and then join on the result from the two tables. Sounds like a raffle type thing where each department wins one random prize. That's a really bizarre requirement, but anyway... I think this does what you want:
Code:
use tempdb;
go
CREATE TABLE Department(
DepartmentName VARCHAR(10) PRIMARY KEY
);
GO
CREATE TABLE Prize(
PrizeName VARCHAR(20) PRIMARY KEY
);
GO
INSERT INTO Department(DepartmentName) VALUES ('History'),('English'), ('French'),('Chemistry');
INSERT INTO Prize(PrizeName) VALUES ('Chewed Sock'),('Old tennis ball'),('nylabone'),('wrench'),('hammer');
;WITH cteDepts(DepartmentName, rn)
AS (
SELECT TOP (5) DepartmentName,
rn = ROW_NUMBER() OVER (ORDER BY NEWID())
FROM Department
ORDER BY ABS(CHECKSUM(NEWID()))
),
ctePrizes(PrizeName, rn)
AS (
SELECT TOP(4) PrizeName,
rn = ROW_NUMBER() OVER (ORDER BY NEWID())
FROM PRIZE
ORDER BY ABS(CHECKSUM(NEWID()))
)
SELECT p.PrizeName, d.DepartmentName
FROM ctePrizes p
LEFT JOIN cteDepts d
ON p.rn = d.rn;
Result:
PrizeName DepartmentName
hammer History
Chewed Sock Chemistry
nylabone English
wrench French
Windows 11 
Access 2021
Reply With Quote
