Please need help with interesting Cross Join query

[Max]

Hi,
I have two tables one for colors and another one for sizes and I need
to write a query that would give me all the combinations of sizes and
colors for a specific product. Here is how my tables look.

SIZES COLORS
________________________ __________________________
|Size Code | Size Name | |Color Code | Color Name |
------------------------ --------------------------
T-Shirt Large T-Shirt Blue
T-Shirt Medium T-Shirt Black
T-Shirt Small Plates Silver
Hats Large Plates Gold
Hats XL

The query should return combinations of Size Names and Color Names for
a specified Codes, but if the product has no size or color I need to
just return whatever it has size or color (plates for example have no
size and hats have no color) -> that's what causes me a problem. The
query I have no is like this
SELECT [Size Name], [Color Name]
FROM [SIZES] CROSS JOIN [COLORS]
WHERE [Size Code] = 'T-Shirt') AND [Color Code] = 'T-Shirt'

this query works fine if a product has both size code and color but if
it has either one (hats for example) than WHERE clause becomes like
this.
WHERE [Size Code] = 'Hats') AND [Color Code] = ''
and this returns me no rows but what i need is two rows

Size Name Color Name
--------- ----------
Large NULL
XL NULL

I appreciate your feedback. Thank you!

 

[[MVP] S.Clark]

To match two datasets with every combination is called a Cross Join or
Cartestian Product.

Add both tables to a query, but do not link them. Add fields from both
tables and run.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-