Join 2 tables with row number in query-CodePudding

I need to connect 2 tables which is complex. The Contract tab is the link between the two tables. The large table is queried with Row Number and the small table may only be connected with WHERE type 'AI' because the info tab contains the data that I need.

Here is the large table

SELECT *
FROM (
    SELECT 
        z.plan_auftrag, 
        b.aunr as contract,
        b.user_n_07,
        b.user_c_47,
        b.user_n_08,
        b.erranf_dat,
        b.erranf_zeit,
        s.a_status,
        b.user_f_25,
        b.user_f_26,
        b.user_c_56,
        b.soll_menge_pri,
        b.user_c_49, 
        b.kunden_bez, 
        ROW_NUMBER() OVER (PARTITION BY b.aunr ORDER BY b.erranf_zeit) AS [row_number]
    FROM [hydra1].[hydadm].[v_auftrags_zusatz] z
    JOIN [hydra1].[hydadm].[auftrags_bestand] b
        ON z.auftrag_nr = b.aunr
    JOIN [hydra1].[hydadm].[v_auftrag_status] s
        ON b.auftrag_nr = s.auftrag_nr
    JOIN [hydra1].[hydadm].[mlst_hy] m
        ON s.auftrag_nr = m.auftrag_nr
        AND s.masch_nr = 'FIMI3'
        AND s.a_status IN ('V','L','U')
        AND m.kennz = 'M'
        AND s.eingeplant = ('M')
        AND b.a_typ IN ('AU','AG')
) AS x
WHERE x.row_number = 1
ORDER BY x.a_status ASC
    , x.erranf_dat ASC
    , x.erranf_zeit ASC;

Here the small table

SELECT info1, left([key],9) as contract
FROM [hydra1].[hydadm].[v_hyinfo]
WHERE typ = 'AI'

Result large Table

Result small table

CodePudding user response：

You can join the small table to the large table before selecting it.

SELECT x.*,
info.info1
FROM
(SELECT 
    z.plan_auftrag, 
    b.aunr as contract,
    b.user_n_07,
    b.user_c_47,
    b.user_n_08,
    b.erranf_dat,
    b.erranf_zeit,
    s.a_status,
    b.user_f_25,
    b.user_f_26,
    b.user_c_56,
    b.soll_menge_pri,
    b.user_c_49, 
    b.kunden_bez
    , ROW_NUMBER() OVER (PARTITION BY b.aunr ORDER BY b.erranf_zeit) AS [row_number]
    FROM [hydra1].[hydadm].[v_auftrags_zusatz] z
    JOIN [hydra1].[hydadm].[auftrags_bestand] b
    ON z.auftrag_nr = b.aunr
    JOIN [hydra1].[hydadm].[v_auftrag_status] s
    ON b.auftrag_nr = s.auftrag_nr
    JOIN [hydra1].[hydadm].[mlst_hy] m
    ON s.auftrag_nr = m.auftrag_nr
    AND s.masch_nr = 'FIMI3'
    AND s.a_status IN ('V','L','U')
    AND m.kennz = 'M'
    AND s.eingeplant = ('M')
    AND b.a_typ IN ('AU','AG')
) AS x
JOIN
(SELECT info1, left([key],9) as contract
FROM [hydra1].[hydadm].[v_hyinfo]
WHERE typ = 'AI'
) info ON x.contract = info.contract
WHERE    x.row_number = 1
ORDER BY x.a_status ASC
, x.erranf_dat ASC
, x.erranf_zeit ASC;