May 2, 2013 at 2:30 am
SELECT a.id,a.brand,oa.color
FROM (SELECT c.id, c.brand, (ABS(CHECKSUM(NEWID())) % 3) + 1
FROM Cars c
)a(id,brand,colorid)
OUTER APPLY (SELECT color
FROM colors co
WHERE co.id = a.colorid) oa;
Or left outer join: -
SELECT a.id,a.brand,co.color
FROM (SELECT c.id, c.brand, (ABS(CHECKSUM(NEWID())) % 3) + 1
FROM Cars c
)a(id,brand,colorid)
LEFT OUTER JOIN colors co ON co.id = a.colorid;
This relies on you knowing how many ids there are in "colors" and knowing that they're sequential. If that's not the case, you'll need to modify the solution.
May 2, 2013 at 2:37 am
The number of colors is dynamic.
May 2, 2013 at 2:49 am
SELECT c.id, c.brand, oa.color
FROM Cars c
OUTER APPLY (SELECT TOP 1 co.color
FROM colors co
ORDER BY CHECKSUM(NEWID(), c.id) & 2147483647
)oa;
May 2, 2013 at 3:00 am
Perfect!
Nice trick with "& 2147483647", haven't used bit wise and before, interesting.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply