How to unify two tables into one?

Question

How to unify two tables into one?

Navigation

#1 by (5 votes)

0

I am working on a report which should show the information of an item, as well as its locations, the different warehouses and their current stock. Use SQL server with an openquery to get information from BaaN.

What I want to do is something similar to the image I'm attaching:

As you can see, the blue marks are a kind of header that groups the item number and the estimated quantity that is required of that item. Below that record, the information about the different locations and stock of that same item is displayed.

The problem I have, and that I really do not know how to achieve, is that the information I get from SQL is displayed in that format for me just pull it to my application.

The current query that I have, and that does not show the blue part is the following:

SELECT * FROM OPENQUERY
(am3p1, 'SELECT o.T$PDNO, REPLACE(o.T$SITM,'' '','''')T$SITM, o.T$CWAR, o.T$QUNE, o.T$CPES$1, w.T$LOCA, w.T$STKS FROM baan.tticst001305 o
INNER JOIN baan.twhinr140305 w ON REPLACE(o.T$SITM,'' '','''') = REPLACE(w.T$ITEM,'' '','''')
 WHERE o.T$PDNO = ''2GE000632'' AND w.T$LOCA <> '' '' ORDER BY o.T$SITM, w.T$IDAT')

sql sql-server

asked by Federico Prado 31.03.2016 в 16:45

source

1 answer

NSString or String Problems with Dictionaries

score 5 · Accepted Answer

As I see in the data, the ordering is given by the column "item". So you could do 2 separate queries and use UNION , then sort them by "Item" and an additional field that we will call "source" so that after ordering it, the header is first.

I will not put real names of fields, but the concept, keep in mind that both queries must return the same number of columns and of the same type.

Whether it is OPENQUERY or not, it does not matter.

SELECT * FROM
(
  /* hacemos el primer select, este ya lo tienes */
  (SELECT 1 as origen, miItem as item, las, demas, columnas, aqui 
       FROM tabla1 WHERE ...)
  UNION /* esto une las consultas en un solo resultado */
  /* este select lo armas como sea que necesites para calcular esos valores de cabecera */
  (SELECT 0 as origen, suItem as item, las, demas, columnas, aqui
       FROM tabla2 WHERE ...)
)
ORDER BY item, origen;

Thus, first you join the results, then you order them by Item, and finally by origin, leaving first the one of lesser origin.

Now, for this to work, since the two tables have different columns, you will have to use a padding so that the columns are equalized.

I'll explain how ...

Assumes that table A has the columns "name" and "address", and table B has the columns "letters sent" ..

The query on table A should fill in the column "sent letters" and table B should fill in the columns of table A ..

(SELECT A.nombre, A.direccion, '' as cartarenviadas FROM A)
UNION
(SELECT '' as nombre, '' as direccion, B.cartasenviadas FROM B)

I hope you understand, anything consult me.

Note:

At the instance of @rsciriano, using UNION ALL instead of UNION you will get higher performance since SQL does not perform the step of eliminating duplicates.