CODEWITHSUNDEEP
mysqlsqljoinview
Phil Young
Phil Young

Reputation: 1354

SQL views with right joins

I have to create a view to show an overview of orders in my database. The overview doesn't need the order items, just the order id, order date, customer name, order total and order status. In the database, there are 2 tables with customer details (one for registered users and one for unregistered users), shop_customer and shop_temp_customer respectively.

I need the view to pull the customer's forename and surname from the relevant table so that I can display it on my order overview page.

Here are the table structures:

shop_orders:

Column Name             Column Type    Null Default

order_id                int(11)         No  None
order_date              int(255)        No  None
order_customer_id       int(11)         Yes NULL
order_temp_customer_id  int(11)         Yes NULL
order_postage           double(11,2)    No  None
order_discount          double(11,2)    No  None
order_total             double(11,2)    No  None
order_status            tinyint(1)      No  None

shop_customers:

Column Name               Column Type  Null Default

customer_id               int(11)       No  None
customer_title            varchar(255)  No  None
customer_forename         varchar(255)  No  None
customer_surname          varchar(255)  No  None
customer_company          varchar(255)  No  None
customer_contact_number   varchar(255)  No  None
customer_email            varchar(255)  No  None
customer_password         varchar(255)  No  None
customer_reference        varchar(255)  No  None
customer_marketing        tinyint(1)    No  None
customer_active           tinyint(1)    No  None
customer_decomission_date int(11)       No  None

shop_temp_customers:

Column Name                     Column Type    Null Default

temp_customer_id                int(11)         No  None
temp_customer_title             varchar(255)    No  None
temp_customer_forename          varchar(255)    No  None
temp_customer_surname           varchar(255)    No  None
temp_customer_company           varchar(255)    No  None
temp_customer_contact_number    varchar(255)    No  None
temp_customer_email             varchar(255)    No  None
temp_customer_reference         varchar(255)    No  None
temp_customer_decomission_date  int(11)         No  None

Here is my code for the view, but It isn't pulling any data items in:

CREATE VIEW shop_order_view_all_preview AS
SELECT o.order_id, o.order_date, c.customer_forename, c.customer_surname, tc.temp_customer_forename, tc.temp_customer_surname, o.order_total, o.order_status
FROM shop_orders o
RIGHT JOIN shop_customers c
ON c.customer_id = o.order_customer_id
RIGHT JOIN shop_temp_customers tc
ON tc.temp_customer_id = o.order_customer_id

Any ideas?

EDIT:

Here is some data from the tables:

shop_orders:

order_id order_date order_customer_id order_temp_customer_id order_postage order_discount order_total order_status
1        1322697540 2                 NULL                   12.50         0.00           1012.50     0
2        1322697540 NULL              1                      13.00         0.00           1200.00     1

shop_customer:

customer_id customer_title customer_forename customer_surname customer_company customer_telephone customer_email customer_password                 customer_reference customer_marketing customer_active customer_decomission_date
2           Mr             b                 a                d                 044444            [email protected]     098f6bcd4621d373cade4e832627b4f6  Poster / Flyer     1                  1               0

shop_temp_customer:

temp_customer_id temp_customer_title temp_customer_forename temp_customer_surname temp_customer_company temp_customer_contact_number temp_customer_email temp_customer_reference temp_customer_decomission_date
 1               Mr                  e                      asy                   NULL                  04444                        [email protected]       Google                  1322697540

Upvotes: 0

Views: 513

Answers (3)

Sergi
Sergi

Reputation: 2892

Try it with left joins instead of right, as the table with ALL the orders is on the left:

CREATE VIEW shop_order_view_all_preview
AS
SELECT
  o.order_id,
  o.order_date,
  c.customer_forename,
  c.customer_surname,
  tc.temp_customer_forename,
  tc.temp_customer_surname,
  o.order_total,
  o.order_status
FROM shop_orders o
  LEFT JOIN shop_customers c ON c.customer_id = o.order_customer_id
  LEFT JOIN shop_temp_customers tc ON tc.temp_customer_id = o.order_customer_id

It's possible this does not solve the problem, but I'll give it a try before anything else.

Upvotes: 3

user359040
user359040

Reputation:

One minor issue on the last line:

CREATE VIEW shop_order_view_all_preview AS
SELECT o.order_id, o.order_date, c.customer_forename, c.customer_surname, tc.temp_customer_forename, tc.temp_customer_surname, o.order_total, o.order_status
FROM shop_orders o
LEFT JOIN shop_customers c
ON c.customer_id = o.order_customer_id
LEFT JOIN shop_temp_customers tc
ON tc.temp_customer_id = o.order_temp_customer_id
--                               ^^^^^

- you are linking the permanent customer_id from the order table to the temporary customer table, even though you have a separate field on the order table for temporary customer IDs.

EDIT: changed RIGHT joins to LEFT joins - order table should be on inside of join.

Upvotes: 2

shoaib akhunzada
shoaib akhunzada

Reputation: 45

try this....

CREATE VIEW shop_order_view_all_preview AS
      SELECT
       shop_orders.order_id, shop_orders.order_date, shop_customers.customer_forename, shop_customers.customer_surname, shop_temp_customers.temp_customer_forename, shop_temp_customers.temp_customer_surname, shop_orders.order_total, shop_orders.order_status
      FROM 
       shop_orders, shop_customers, shop_temp_customers 
      RIGHT JOIN shop_customers 
      ON customer_id = order_customer_id
      AND
      RIGHT JOIN shop_temp_customers
      ON temp_customer_id = order_customer_id

Upvotes: 0

Related Questions