Query the vendors table to confirm the inserted data. Mysql> INSERT INTO vendors (vendor_name) VALUES ('JKL QUICK SERVICES') Mysql> INSERT INTO vendors (vendor_name) VALUES ('ABC 24HRS DISTRIBUTORS') mysql> INSERT INTO vendors (vendor_name) VALUES ('XYZ DIGITAL SUPPLIERS') Then, insert a few records to the vendors table. Vendor_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, Create the vendors table by running the command below. However, you'll just require one column for the vendors' names. Just like customers, every vendor in your store should be identified with a unique value( vendor_id). +-+-+-+ģ rows in set (0.00 sec) Create Vendors Table You should see a list of customers as shown below. Mysql> INSERT INTO customers (first_name, last_name) VALUES ('JOE', 'SMITH') Įxecute the SELECT command below against the customers table to confirm the entries above. Mysql> INSERT INTO customers (first_name, last_name) VALUES ('RICHARD', 'ROE') mysql> INSERT INTO customers (first_name, last_name) VALUES ('JANE', 'DOE') mysql> CREATE TABLE customers (Ĭustomer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,Īdd some records to the customers table. This table stores the customers' unique identifiers ( customer_id) alongside their names( first_name and last_name). mysql> USE sample_pos Create a Customers TableĬreate a customers table. After the mysql> prompt is displayed, create a sample_pos database. When prompted, enter the root password of your MySQL server and press Enter to proceed. SSH your server and sign in to MySQL as a root user. You can also follow this tutorial on a Vultr One-Click LAMP server which has MySQL pre-installed. To follow along with this tutorial, ensure you have the following: While you'll record both the customer's and vendors' activities in different tables, you will use the UNION clause later in the tutorial to generate a useful report that shows the cash flow in your store. In this guide, you'll create a hypothetical store database for recording payments made by customers and those paid to vendors. This is where the MySQL UNION clause comes into action. However, when implementing the reporting part, you might feel the need to combine data from different tables into a single report. Such a design pattern should adhere to the principle of separation of concerns. When designing a database application, you should consider separating it into distinct sections. The clause is useful when a report must retrieve records from different tables. The MySQL UNION statement combines the results of multiple SELECT statements.

Mysql union with same value