SQL-receiving data from two tables in different columns without using joins

Question

I have a STOCK table that looks like this:

 PRODUCT SALES_CODE STOCK_1 STOCK_2 STOCK_3 ----------------------------------------------------- A 6-10 0 1 2 

There are many STOCK_X branches, but for simplicity I have excluded.

Now I have another SIZE_GRID table:

 SALES_CODE SIZE_1 SIZE_2 SIZE_3 -------------------------------------- 6-10 6 8 10 

As you might have guessed, this is a stock at hand for a specific product in size.

I need to get the STOCK values ​​from the first table and the size from the second table. I originally did the following

 SELECT STOCK.PRODUCT, SIZE_GRID.SIZE_1, STOCK.STOCK_1 FROM STOCK INNER JOIN SIZE_GRID ON SIZE_GRID.SALES_CODE = STOCK.SALES_CODE UNION ALL SELECT STOCK.PRODUCT, SIZE_GRID.SIZE_2, STOCK.STOCK_2 FROM STOCK INNER JOIN SIZE_GRID ON SIZE_GRID.SALES_CODE = STOCK.SALES_CODE UNION ALL SELECT STOCK.PRODUCT, SIZE_GRID.SIZE_3, STOCK.STOCK_3 FROM STOCK INNER JOIN SIZE_GRID ON SIZE_GRID.SALES_CODE = STOCK.SALES_CODE 

I have about 40 STOCK_X that I need to get, so wandering around if there is a much easier way to do this? I prefer to use pure SQL and not UDF / SP.

http://sqlfiddle.com/#!6/f323e

Answer 1

If you are in SQL Server 2008 or later, you can try the following method (found here ):

 SELECT STOCK.PRODUCT, X.SIZE, X.STOCK FROM STOCK INNER JOIN SIZE_GRID ON SIZE_GRID.SALES_CODE = STOCK.SALES_CODE CROSS APPLY ( VALUES (SIZE_GRID.SIZE_1, STOCK.STOCK_1), (SIZE_GRID.SIZE_2, STOCK.STOCK_2), (SIZE_GRID.SIZE_3, STOCK.STOCK_3) ) X (SIZE, STOCK) ; 

With a little tweaking, you can make it work in SQL Server 2005 too:

 SELECT STOCK.PRODUCT, X.SIZE, X.STOCK FROM STOCK INNER JOIN SIZE_GRID ON SIZE_GRID.SALES_CODE = STOCK.SALES_CODE CROSS APPLY ( SELECT SIZE_GRID.SIZE_1, STOCK.STOCK_1 UNION ALL SELECT SIZE_GRID.SIZE_2, STOCK.STOCK_2 UNION ALL SELECT SIZE_GRID.SIZE_3, STOCK.STOCK_3 ) X (SIZE, STOCK) ; 

However, if you are using an even earlier version, this may help:

 SELECT STOCK.PRODUCT, SIZE = CASE XN WHEN 1 THEN SIZE_GRID.SIZE_1 WHEN 2 THEN SIZE_GRID.SIZE_2 WHEN 3 THEN SIZE_GRID.SIZE_3 END, STOCK = CASE XN WHEN 1 THEN STOCK.STOCK_1 WHEN 2 THEN STOCK.STOCK_2 WHEN 3 THEN STOCK.STOCK_3 END, FROM STOCK INNER JOIN SIZE_GRID ON SIZE_GRID.SALES_CODE = STOCK.SALES_CODE CROSS JOIN ( SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) X (N) ; 

Although the last two options use UNION ALL, they only combine single lines, not whole subsets.

Answer 2

Consider the normalization of the table. Instead of a repeating column:

 PRODUCT SALES_CODE STOCK_1 STOCK_2 STOCK_3 

Use a normalized table:

 PRODUCT SALES_CODE STOCK_NO STOCK 

And the same for table SIZE_GRID:

 SALES_CODE SIZE_NO SIZE 

Now you can query without having to list 40 columns:

 select * from STOCK s join SIZE_GRID sg on sg.SALES_CODE = s.SALES_CODE and sg.SIZE_NO = s.STOCK_NO 

Answer 3

Here are some alternatives you can use:

• Run each SQL separately and combine and sort the result sets in your program.
• Join the tables.
• Use a scalar subquery.

select
select col1, col2, col3 from table_1 q1,

select col1, col2, col3 from table_2 q2 from double;

• Try UNION using the FULL OUTER JOIN with the NVL function: this is assumed to have better performance than the UNION statement.

 select empno, ename, nvl(dept.deptno,emp.deptno) deptno, dname from emp full outer join dept on (emp.deptno = dept.deptno) order by 1,2,3,4;