Result from two tables

Transact-SQL
1

Hi Support,

Please see the table and expected result needed. Direct joining seems to be not giving the desired result.
CREATE TABLE vehicletable
(
customer BIGINT,
newvehicleid INT
)

insert into vehicletable values ('11341',4781),('11341',4782)

    CREATE TABLE customerenq
    (
    customerenqid  BIGINT,
    newvehicleenqid INT
    )

insert into customerenq values ('11341',4781),('11341',4782),('11341',4783),('11341',4784)

Expected Result

customer newvehicleid customerenqid newvehicleenqid
11341 4781 11341 4781
11341 4782 11341 4782
0 0 11341 4783
0 0 11341 4784
2

hi i hope this helps

:slight_smile:
:slight_smile:

drop create data 
DROP TABLE dbo.vehicletable
GO 

CREATE TABLE vehicletable
(
    customer BIGINT,
    newvehicleid INT
);

INSERT INTO vehicletable
VALUES
('11341', 4781),
('11341', 4782);

DROP TABLE customerenq
GO 

CREATE TABLE customerenq
(
    customerenqid BIGINT,
    newvehicleenqid INT
);
INSERT INTO customerenq
VALUES
('11341', 4781),
('11341', 4782),
('11341', 4783),
('11341', 4784);
SQL 
;WITH cte
AS (SELECT ROW_NUMBER() OVER (ORDER BY customer) rn,
           *
    FROM dbo.vehicletable),
      cte1
AS (SELECT ROW_NUMBER() OVER (ORDER BY customerenqid) rn,
           *
    FROM dbo.customerenq)
SELECT ISNULL(a.customer, 0) AS customer,
       ISNULL(a.newvehicleid, 0) AS newvehicleid,
       b.customerenqid,
       b.newvehicleenqid
FROM cte a
    RIGHT JOIN cte1 b
        ON a.rn = b.rn;
GO
result

image

1 Like
4

hi

Another way to do it ...

Earlier Approach .. may not be suitable for all cases
:slight_smile:
:slight_smile:

drop create data .. 
DROP TABLE dbo.vehicletable
GO 

CREATE TABLE vehicletable
(
    customer BIGINT,
    newvehicleid INT
);

INSERT INTO vehicletable
VALUES
('11341', 4781),
('11341', 4782);

DROP TABLE customerenq
GO 

CREATE TABLE customerenq
(
    customerenqid BIGINT,
    newvehicleenqid INT
);
INSERT INTO customerenq
VALUES
('11341', 4781),
('11341', 4782),
('11341', 4783),
('11341', 4784);
Another WAY .. SQL .. 
SELECT ISNULL(a.customer,0) AS customer,
       ISNULL(a.newvehicleid,0) AS newvehicleid ,
       b.customerenqid,
       b.newvehicleenqid
FROM vehicletable a
    RIGHT JOIN customerenq b
        ON a.customer = b.customerenqid
           AND a.newvehicleid = b.newvehicleenqid;
Result

image

5

CREATE TABLE vehicletable
(
customer BIGINT,
newvehicleid INT
)

insert into vehicletable values ('11341',4781),('11341',4782)

CREATE TABLE customerenq
(
customerenqid  BIGINT,
newvehicleenqid INT
)

insert into customerenq values ('11341',4781),('11341',4782),('11341',4783),('11341',4784)
select isnull(customer,0) as customer,isnull(newvehicleid,0) as newvehicleid,customerenqid,newvehicleenqid from vehicletable
right outer join customerenq
on newvehicleenqid = newvehicleid