Join 2 Tables together with SQL

Question

I have a Customer and Customer_2 table which I am trying to join together:

Both tables have data in them, but upon joining with a statement only the column names are being returned without data. I am trying to use the following join statement:

select distinct * 
from Customer c 
join Customer_2 d on c.CUST_NUM = d.CUST_NUM

These are the tables:

CREATE TABLE [Customer] 
(
    [CUST_NUM] [INT] NOT NULL,
    [CUST_LNAME] [VARCHAR](50) NULL,
    [CUST_FNAME] [VARCHAR](50) NULL,
    CUST_BALANCE [MONEY] NOT NULL, 
) ON [PRIMARY]

CREATE TABLE [Customer_2]  
(
    [CUST_NUM] [INT] NOT NULL,
    [CUST_LNAME] [VARCHAR](50) NULL,
    [CUST_FNAME] [VARCHAR](50) NULL, 
) ON [PRIMARY]

Data in each Table:

INSERT INTO Customer 
VALUES ('1000', 'Smith', 'Jeanne', '1050.11'),
       ('1001', 'Ortega', 'Juan', '840.92');

INSERT INTO CUSTOMER_2 
VALUES ('2000', 'McPherson', 'Anne'),
       ('2001', 'Ortega', 'Juan'),
       ('2002', 'Kowalski', 'Jan'),
       ('2003', 'Chan', 'George');

Expected output would be combining customer_2 onto the bottom of the customer table with the extra column CUST_BALANCE being 0 or null for each of the four customers on the customer 2 table.

Most importantly if you can't post all data, post data for each table making sure we have at least one cust_num which the same in both tables — zip, Nov 27 '19 at 20:52
What does the INVOICE table have to do with your question? No idea what the question here as it doesn't make much sense. — Sean Lange, Nov 27 '19 at 20:55
I edited the main post with the data and a description with expected output, hopefully it is more clear, thank you. Invoice table is not relevant to this particular question, it is just an inclusion of the database. — Christian R, Nov 27 '19 at 20:58

score 1 · Answer 1 · edited Nov 27 '19 at 21:03

1

I guess whats happening is your join condition is never met meaning both tables aren't having same cust_name try to debug as below left join

   select distinct * 
   from Customer c 
   Left join Customer_2 d on 
   c.CUST_NUM =
   d.CUST_NUM

edited Nov 27 '19 at 21:03

Dale K

25,246
15
42
71

answered Nov 27 '19 at 20:55

Himanshu

3,830
2
10
29

I don't think this is going to do what you think it will. This will join every single row in the first table with every single row in the second table when the CUST_NUM isn't the same. It is almost a cross join or cartesian product. – Sean Lange Nov 27 '19 at 20:56
Check now although the first too had shown some data to OP. I nowhere said it will do the thing which OP wants but definitely it would have shown some data as I already said above as debug – Himanshu Nov 27 '19 at 21:01
You posted it as an answer and I suggested it probably wasn't the answer they were looking for. To be fair, the OP is still defining the question. Still not really sure what they are looking for but your answer is a more reasonable guess now. – Sean Lange Nov 27 '19 at 21:03
@SeanLange agreed mate but without the data i couldnt proceed with editing my answer again as I could see now the OP has added one new condition but that too require some data view – Himanshu Nov 27 '19 at 21:05
I tried this and it returned all the data from Customer table, but with the columns cust_num, cust_lname, and cust_fname repeated and showing null entries. for 2 rows, The expected output should be 7 rows total with null listed for the cust_balnace column as there is no cust_balance column on customer_2 table. – Christian R Nov 27 '19 at 21:09

score 1 · Accepted Answer · answered Nov 27 '19 at 21:06

Think you are trying to get a total of the invoice amount for each customer? The easiest way to accomplish this is create a derived table of Customer and Customer_2. I am doing that with a cte in this example. Then it becomes a simple aggregate to get the total.

with Customers as
(
    select CUST_NUM
        , CUST_LNAME
        , CUST_FNAME
    from Customer
    UNION ALL
    select CUST_NUM
        , CUST_LNAME
        , CUST_FNAME
    from Customer_2
)

select c.CUST_NUM
    , c.CUST_FNAME
    , c.CUST_LNAME
    , InvoiceTotal = SUM(i.INV_AMOUNT)
from Customers c
left join INVOICE i on i.CUST_NUM = c.CUST_NUM
group by c.CUST_NUM
    , c.CUST_FNAME
    , c.CUST_LNAME

This gives the correct output except for the one entry for Juan Ortega is duplicated, — Christian R, Nov 27 '19 at 21:35

sam · Answer 3 · 2019-11-27T23:49:40.477

0

Try this:

select c.[CUST_NUM] , c.[CUST_BALANCE]
FROM INVOICE I
JOIN (
     SELECT CUST_NUM, CUST_BALANCE FROM Customer c 
     UNION
     SELECT CUST_NUM, NULL as CUST_BALANCE FROM Customer_2 d
   ) tbl t on i.cust_num = t.cust_num

edited Nov 27 '19 at 23:49

answered Nov 27 '19 at 20:55

sam

1,937
1
8
14

provide context around your answer to avoid removal. – ZF007 Nov 27 '19 at 22:21
I tried to give the answer. Not sure what context you want me to add. – sam Nov 27 '19 at 23:49

Join 2 Tables together with SQL

3 Answers3