How to Join tables to get the result

Question

I am having data like below

Table1 data:

Attr1    Attr2
36 L
37 L
38 L
39 L
40 L
41 L
42 L
43 L
44 L
46 L
48 L
50 L
52 L
54 L
56 L
58 L
60 L
62 L
36 P
37 P
38 P
39 P
40 P
41 P
42 P
43 P
44 P
46 P
48 P
50 P
52 P
54 P
56 P
58 P
60 P
62 P
36 PL
37 PL
38 PL
39 PL
40 PL
41 PL
42 PL
43 PL
44 PL
46 PL
48 PL
50 PL
52 PL
54 PL
56 PL
58 PL
60 PL
62 PL
36 PS
37 PS
38 PS
39 PS
40 PS
41 PS
42 PS
43 PS
44 PS
46 PS
48 PS
50 PS
52 PS
54 PS
56 PS
58 PS
60 PS
62 PS
36 R
37 R
38 R
39 R
40 R
41 R
42 R
43 R
44 R
46 R
48 R
50 R
52 R
54 R
56 R
58 R
60 R
62 R
36 S
37 S
38 S
39 S
40 S
41 S
42 S
43 S
44 S
46 S
48 S
50 S
52 S
54 S
56 S
58 S
60 S
62 S
36 XL
37 XL
38 XL
39 XL
40 XL
41 XL
42 XL
43 XL
44 XL
46 XL
48 XL
50 XL
52 XL
54 XL
56 XL
58 XL
60 XL
62 XL

And table2 like below:

ItemCode          Attr1     Attr2
ITEM-000001 43 S
ITEM-000001 52 L
ITEM-000006 42 R
ITEM-000006 44 R
ITEM-000009 56 R

there will be more items in table2.

How i can get output in which i will get the all rows of table1 who are having same 'Attr2' say for example 'ITEM-000001' having two Attr2 - 'S' & 'L' so it will display as shown below:

Attr1    Attr2       ItemCode
    36 L           ITEM-000001
    37 L           ITEM-000001
    38 L           ITEM-000001
    39 L           ITEM-000001
    40 L           ITEM-000001
    41 L           ITEM-000001
    42 L           ITEM-000001
    43 L           ITEM-000001
    44 L
    46 L
    48 L
    50 L
    52 L
    54 L
    56 L
    58 L
    60 L
    62 L           ITEM-000001
    36 S           ITEM-000001
    37 S
    38 S
    39 S
    40 S
    41 S
    42 S
    43 S
    44 S
    46 S
    48 S
    50 S
    52 S
    54 S           ITEM-000001
    56 S           ITEM-000001
    58 S           ITEM-000001
    60 S           ITEM-000001
    62 S           ITEM-000001

Answer 1

I think finally I understand your question. First let me describe what I think you want in my own words, to see if I understand correctly your requirements.

You want to make a query for a specific item code. Table1 shows all (Attr1, Attr2) combinations that are possible, and Table2 shows which are available for the specific item code. You want to return rows for those Attr2 that are available for your item code, and for each (Attr1, Attr2) pair show whether or not the item code is available for this pair.

I think this query does what you want:

SELECT T1.Attr1, T1.Attr2, T2_2.ItemCode
FROM Table1 T1
JOIN Table2 T2_1
ON T1.Attr2 = T2_1.Attr2
LEFT JOIN Table2 T2_2
ON T1.Attr1 = T2_2.Attr1 AND T1.Attr2 = T2_2.Attr2
WHERE T2_1.ItemCode = 'ITEM-000001'

For your (incomplete) test data the result is:

36, 'L', ''
37, 'L', ''
38, 'L', ''
39, 'L', ''
40, 'L', ''
41, 'L', ''
42, 'L', ''
43, 'L', ''
44, 'L', ''
46, 'L', ''
48, 'L', ''
50, 'L', ''
52, 'L', 'ITEM-000001'
54, 'L', ''
56, 'L', ''
58, 'L', ''
60, 'L', ''
62, 'L', ''
36, 'S', ''
37, 'S', ''
38, 'S', ''
39, 'S', ''
40, 'S', ''
41, 'S', ''
42, 'S', ''
43, 'S', 'ITEM-000001'
44, 'S', ''
46, 'S', ''
48, 'S', ''
50, 'S', ''
52, 'S', ''
54, 'S', ''
56, 'S', ''
58, 'S', ''
60, 'S', ''
62, 'S', ''

I have have misunderstood something, please let me know.

Answer 2

EDIT

Reading your query, I guess I finally know what you want:

Get all DISTINCT ItemCode and Attr2 from Table2, and join all DISTINCT Attr1 from Table1. I still can not see that from your provided samples...

You could reduce your query to:

SELECT DISTINCT T2.ItemCode, T1.Attr1, T2.Attr2
FROM
  Table1 T1
  CROSS JOIN Table2 T2
ORDER BY 1, 2, 3

I guess that performance could be improved by:

SELECT T2.ItemCode, T1.Attr1, T2.Attr2
FROM
  ( SELECT DISTINCT Attr1
    FROM Table1
  ) T1
  CROSS JOIN
  ( SELECT DISTINCT ItemCode, Attr2
    FROM Table2
  ) T2
ORDER BY 1, 2, 3

---

Original post:

You can do this using a LEFT JOIN. This will return all rows of Table1 and join rows of Table2 where the condition matches. Columns of Table2 are NULL, if no row is matched.

SELECT t1.Attr1, t1.Attr2, t2.ItemCode
FROM Table1 t1
LEFT JOIN Table2 t2 ON ( t2.Attr1 = t1.Attr1 AND t2.Attr2 = t1.Attr2 )
WHERE t1.Attr2 IN ( 'S', 'L' )

Answer 3

I think what your looking for is to grab all records from table 1 that have an ItemCode of a specific value, like 'Item-000001'. If thats the case the code below should do what you want, just replace the 'Item-000001' with what ever value you looking for. Also, if you are looking for only records that have an Attr2 of 'S' or 'L' then you'll need the second part of the where clause.

SELECT T1.Attr1, T1.Attr2, T2.ItemCode
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Attr1 = T2.Attr1 AND T1.Attr2 = T2.Attr2
WHERE T2.ItemCode = 'Item-000001' AND T2.Attr2 IN ('S', 'L')

Answer 4

Hi All thank you for all your efforts & time for my question, here is the query which will give me the expected result. Thank you very much to all.

SELECT DISTINCT T2.ItemCode, T1.Attr1, T2.Attr2
FROM  
    (
       SELECT Attr1
       FROM Table1
    ) AS T1

    CROSS JOIN

    ( 
       SELECT ItemCode, Attr2
       FROM Table2
    ) AS T2
ORDER BY 1, 2, 3