Add two query different tables to one query and one table

Goran Bibic

3y928SQL

ADD TWO QUERY DIFFERENT TABLES TO ONE QUERY AND ONE TABLE

NEED GOOD EXAMPLE

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

Accepted answer

DECLARE @SerialTable TABLE
(
    SerialNumber INT IDENTITY(1,1),
    naziv VARCHAR(50),
    kolicina INT,
    CIJENA NUMERIC(18, 2),
    UKUPNO DECIMAL
);
INSERT INTO @SerialTable
SELECT t.naziv, t.kolicina, t.CIJENA, t.UKUPNO
FROM
(
    SELECT roba AS naziv, FORMAT(SUM(kolicina), '#,0.00') AS kolicina, FORMAT((mp_cijena), '#,0.00') AS 'CIJENA', FORMAT(SUM(iznos_sa_pdv), '#,0.00') AS 'UKUPNO'
    FROM dbo.mp_racun_roba
    WHERE roba IN (SELECT naziv FROM roba_usluge WHERE podgrupa_artikala = 'Hrana') AND id_fakture IN (SELECT id FROM dbo.mp_racun_lista WHERE datum = '2023-05-18')
    GROUP BY roba, mp_cijena

    UNION ALL

    SELECT roba AS naziv, FORMAT(SUM(kolicina), '#,0.00') AS kolicina, FORMAT((mp_cijena), '#,0.00') AS 'CIJENA', FORMAT(SUM(iznos_sa_pdv), '#,0.00') AS 'UKUPNO'
    FROM dbo.mp_faktura_roba
    WHERE roba IN (SELECT naziv FROM roba_usluge WHERE podgrupa_artikala = 'Hrana') AND id_fakture IN (SELECT id FROM dbo.mp_faktura_lista WHERE datum = '2023-05-18')
    GROUP BY roba, mp_cijena
) t

SELECT * FROM @SerialTable t1 LEFT JOIN dbo.normativi_roba t2 ON t1.SerialNumber = t2.redni_broj
WHERE id_fakture IN (SELECT id FROM dbo.normativi_lista WHERE naziv = 'BUREK');

Amit Mohanty3y ago

SELECT t.naziv, t.kolicina, t.CIJENA, t.UKUPNO, NULL AS 'id', NULL AS 'redni_broj', NULL AS 'sifra', NULL AS 'naziv', NULL AS 'proizvodjac', NULL AS 'jedinica_mjere', 
NULL AS 'id_fakture', NULL AS 'kolicina', NULL AS 'iznos', NULL AS 'prosjecna_nab_cijena', NULL AS 'bar_kod', NULL AS 'porez_procenat', NULL AS 'iznos_poreza_mp_cijena'
FROM
(
    SELECT roba AS naziv, FORMAT(SUM(kolicina), '#,0.00') AS kolicina, FORMAT((mp_cijena), '#,0.00') AS 'CIJENA', FORMAT(SUM(iznos_sa_pdv), '#,0.00') AS 'UKUPNO'
    FROM dbo.mp_racun_roba
    WHERE roba IN (SELECT naziv FROM roba_usluge WHERE podgrupa_artikala = 'Hrana') AND id_fakture IN (SELECT id FROM dbo.mp_racun_lista WHERE datum = '2023-05-18')
    GROUP BY roba, mp_cijena

    UNION ALL

    SELECT roba AS naziv, FORMAT(SUM(kolicina), '#,0.00') AS kolicina, FORMAT((mp_cijena), '#,0.00') AS 'CIJENA', FORMAT(SUM(iznos_sa_pdv), '#,0.00') AS 'UKUPNO'
    FROM dbo.mp_faktura_roba
    WHERE roba IN (SELECT naziv FROM roba_usluge WHERE podgrupa_artikala = 'Hrana') AND id_fakture IN (SELECT id FROM dbo.mp_faktura_lista WHERE datum = '2023-05-18')
    GROUP BY roba, mp_cijena
) t

UNION ALL

SELECT NULL AS 'naziv', NULL AS 'kolicina', NULL AS 'CIJENA', NULL AS 'UKUPNO', id, redni_broj, sifra, naziv, proizvodjac, jedinica_mjere, 
id_fakture, kolicina, iznos, prosjecna_nab_cijena, bar_kod, porez_procenat, iznos_poreza_mp_cijena
FROM dbo.normativi_roba WHERE id_fakture IN (SELECT id FROM dbo.normativi_lista WHERE naziv = 'BUREK');

Amit Mohanty3y ago

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;