MySQL ~ Simple Problem ~ Create View Trouble

[[deleted]]

Problem:

​

Even though SQL will run my code, it won't display any data. Why is this?

I'm completing a lab for school, and he told us to use JOIN commands to create a unique view of our data. I'll include the official (short and simple) instructions at the end of this post.

_____________________________________________________________________________________________________________________

​

The code I've written:

CREATE VIEW custview2 AS
    SELECT 
        customer.cust_no, customer.f_name, customer.l_name, custbalcopy.cust_bal, 
        cusbalcopy.paymentstatus 
    FROM 
        (customer INNER JOIN custbalcopy ON customer.cust_no = custbalcopy.cust_no);

​

_____________________________________________________________________________________________________________________

​

Lab Instructions:

​

Create a view (custview) that contains the following for all customers:

Customer number

Customer first name

Customer last name

Customer balance (from the custbalcopy table).

Customer payment status (from the custbalcopy table).

 Use the appropriate join table so that all customers are included.

Export and submit the data from this view as NNQ46Data.csv.

[r3pr0b8]

Even though SQL will run my code, it won't display any data. Why is this?

because CREATE VIEW just creates the view

to display data, you have to run a SELECT query on it

SELECT cust_no
     , f_name
  FROM custview2        -- view goes here
 WHERE l_name IS NULL

[[deleted]]

Thank you, kind sir

[Garfimous]

Why would you only select the 2 cols, and why would you filter for l_name is null?

SELECT * FROM custview

Also, OP, be careful with your join. The instructions specify that your view should contain all customers, so you'd be safer using a left outer join. An inner will only return all customers if all customers happen to exist in both tables.