CODEWITHSUNDEEP
sqlsql-server
Lock
Lock

Reputation: 5522

SQL- get data from two tables in different columns without using unions

I have a table STOCK that looks like this:

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

There are many STOCK_X buckets but for simplicity's sake, I've excluded.

Now I have another table SIZE_GRID:

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

As you might have guessed, these are stock on hand for a certain product, by size.

I need to get the STOCK values from the first table, and the size from the second table. Originally, I was doing 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 around 40 STOCK_X that I need to retrieve, so wandering if there is a much easier way to do this? Preferably I want to use pure SQL and no UDF/SP's.

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

Upvotes: 4

Views: 3685

Answers (3)

Andriy M
Andriy M

Reputation: 77717

If you are on SQL Server 2008 or later version, you could 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 small tweak you could make it work in SQL Server 2005 as well:

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 might be of help:

SELECT
  STOCK.PRODUCT,
  SIZE  = CASE X.N
            WHEN 1 THEN SIZE_GRID.SIZE_1
            WHEN 2 THEN SIZE_GRID.SIZE_2
            WHEN 3 THEN SIZE_GRID.SIZE_3
          END,
  STOCK = CASE X.N
            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 are combining single rows only, not entire subsets

Upvotes: 4

Andomar
Andomar

Reputation: 238246

Consider normalizing 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 the SIZE_GRID table:

SALES_CODE    SIZE_NO   SIZE

Now you can query without the need 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

Upvotes: 3

mel3kings
mel3kings

Reputation: 9415

Here are some alternatives you can use:

  • Execute each SQL separately and merge and sort the result sets within 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 dual;

  • Try UNION using FULL OUTER JOIN with the NVL function: It is suggested that this has faster performance than the UNION operator.
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;

Upvotes: 0

Related Questions