1

I'm teaching myself SQL and I ran into a question that may be related to joining tables. I'm just a bit unsure.

Say, you have two tables, one listing the customer's creditcard_number, and one listing the customer's order_number. I am trying to match a customer's creditcard_number with their order_number.

What might this query look like? Would I first have to join the tables?

  • 2
    Please always provide basic table definitions to make things clear and easier to answer. Ideally also some sample data and the desired result. – Erwin Brandstetter Aug 27 '15 at 13:33
  • @ErwinBrandstetter I provided some - would be interested in any comments you may have. I think that the OP is a bit before the step where they can provide real data? – Vérace Aug 28 '15 at 6:22
4

To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown.

I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one!

A word of advice (and see discussion below), if you have a choice, I would strongly urge you to use PostgreSQL as its dialect of SQL is vastly superior to MySQL's. The -- (double hyphen) is a comment in SQL, insert/remove as appropriate for your server.

CREATE TABLE Customer
(   
  Cust_ID SERIAL NOT NULL PRIMARY KEY,                 -- PostgreSQL
  -- Cust_ID  INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- for MySQL
  Name VARCHAR(15),
  CC_Number VARCHAR(16)
);

CREATE TABLE Cust_Order  -- don't want a table named "Order" - SQL keyword
(
  Order_ID SERIAL NOT NULL PRIMARY KEY,  -- PostgreSQL
  -- Order_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- MySQL
  Cust_ID INT,
  Order_Value INT,
  Order_DateTime TIMESTAMP,  -- PostgreSQL
  -- Order_DateTime DATETIME -- MySQL
  CONSTRAINT Order_Cust_fk FOREIGN KEY (Cust_ID) REFERENCES Customer(Cust_ID)
);

Firstly, to answer your specific question about relating credit card number to the order, the query (for both PostgreSQL and MySQL) to run would be this:

SELECT c.Cust_ID, c.CC_Number, o.Order_ID
FROM Customer c
INNER JOIN Cust_Order o
ON c.Cust_ID = o.Cust_ID
ORDER BY c.Cust_ID;

which gives the result:

+---------+---------------+----------+
| Cust_ID | CC_Number     | Order_ID |
+---------+---------------+----------+
|       2 | 4656576576767 |        1 |
|       2 | 4656576576767 |        2 |
|       2 | 4656576576767 |        3 |
|       2 | 4656576576767 |        4 |
|       3 | 9999999999999 |        5 |
|       3 | 9999999999999 |        6 |
|       3 | 9999999999999 |        7 |
|       3 | 9999999999999 |        8 |
|       3 | 9999999999999 |        9 |
+---------+---------------+----------+

The reason that I didn't directly answer the specific question in your post was that it is very unlikely that anyone would be performing such queries. Storing credit card numbers as plain text is a recipe for disaster. Since the question related to JOINs and SQL generally, I was more interested in demonstrating JOINing and SQL than in how to display CC numbers.

SQL is all about aggregations and GROUPing and SUMming, so in my original example, I showed the SUM of sales by Customer and CC number (although, as mentioned, you should never be performing SQL against CC numbers directly). Chances are you'll be interested in sales per customer rather than actual CC numbers.

SELECT c.Cust_ID, c.Name, c.CC_Number, SUM(o.Order_Value) AS Total
FROM Customer c 
INNER JOIN Cust_Order o 
ON c.Cust_ID = o.Cust_ID
GROUP BY c.Cust_ID, o.Cust_ID                      -- PostgreSQL GROUP BY
-- GROUP BY c.Cust_ID, c.Name, c.CC_Number, o.Cust_ID -- MySQL GROUP BY
ORDER BY o.Cust_ID;

result:

+---------+-------+---------------+-------+
| Cust_ID | Name  | CC_Number     | Total |
+---------+-------+---------------+-------+
|       2 | Jimmi | 4656576576767 | 13736 |
|       3 | Mary  | 9999999999999 | 27775 |
+---------+-------+---------------+-------+

A word of warning about MySQL:

MySQL has a variable sql_mode which is a set of parameters - you can view it by issuing the command 'SELECT @@sql_mode;' in the mysql client (or MySQL Workbench). You can set it in the my.cnf or my.ini (Windows) in the mysqld section:

[mysqld]

sql_mode    = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

The ONLY_FULL_GROUP_BY parameter is the one to set, otherwise MySQL can produce (ahem...) anomalous (i.e. wrong) results!

Finally, and to expand, you might also (as someone who's selling widgets :-) ) be interested in clients who haven't bought anything (possibly in a specified period). I wrote this query using a LEFT JOIN (not INNER). This works for PostgreSQL and MySQL.

The LEFT JOIN will bring in everything in the left (or first, i.e. Customer) table - i.e. all clients whether they've made purchases or not, i.e. whether they have a corresponding entry in the Cust_Order table or not. The INNER JOIN will only match records which correspond in both tables - i.e. it will only show clients to whom a sale has actually been made.

SELECT c.Cust_ID, c.Name, c.CC_Number, COALESCE(SUM(o.Order_Value), 0) AS Total
FROM Customer c 
LEFT JOIN Cust_Order o 
ON c.Cust_ID = o.Cust_ID
-- WHERE EXTRACT(MONTH FROM Order_DateTime) = 2        -- PostgreSQL
-- WHERE MONTH(Order_DateTime) = 2                     -- MySQL
GROUP BY c.Cust_ID, o.Cust_ID                          -- PostgreSQL
-- GROUP BY c.Cust_ID, c.Name, c.CC_Number, o.Cust_ID  -- MySQL
-- HAVING COALESCE(SUM(o.Order_Value), 0) >= 0
-- ORDER BY SUM(o.Order_Value) NULLS FIRST, c.Cust_ID ASC;  -- PostgreSQL
-- ORDER BY SUM(o.Order_Value) IS NULL DESC, c.Cust_ID ASC; -- MySQL

Result:

 cust_id | name  |   cc_number   | total 
---------+-------+---------------+-------
       3 | Mary  | 9999999999999 | 27775
       2 | Jimmi | 4656576576767 | 13736
       4 | Joe   | 2222222222222 |     0
       1 | Billy | 3434343434343 |     0

This is the result with no WHERE, HAVING or ORDER BY clauses.

I would urge you to experiment with the COALESCE function (PostgreSQL and MySQL) and instead just using SUM(o.Order_Value) - instructive for NULLs. Vary it both in the SELECT, the HAVING and the ORDER BY.

Check out the EXTRACT (PostgreSQL) here and MONTH (MySQL) functions and the other functions on those pages. Vary the SUM in the HAVING and the ORDER BY - go mad!! :-)

These servers (as do others) have string functions (PostgreSQL and MySQL) and also maths ones (P and M) - as well as regular expressions (much better supported in PostgreSQL than MySQL - another reason to use PostgreSQL).

This should get you started on the road to SQL mastery! :-)

Take a look at the questions on this forum and make an attempt to answer them - you'll gradually find that situations that used to baffle you become clearer.

===================== Data for Queries ===========================

Customer table data:, for both PostgreSQL and MySQL.

 INSERT INTO Customer (Name, CC_Number) VALUES ('Billy', '3434343434343');
 INSERT INTO Customer (Name, CC_Number) VALUES ('Jimmi', '4656576576767');
 INSERT INTO Customer (Name, CC_Number) VALUES ('Mary',  '9999999999999');
 INSERT INTO Customer (Name, CC_Number) VALUES ('Joe',   '2222222222222');

Cust_Order table data: (again, for both PostgreSQL and MySQL).

INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-02-14 11:55:34');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-02-18 14:24:34');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-03-09 09:23:17');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-03-27 16:02:56');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-01-22 12:45:03');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-02-25 13:34:43');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-02-28 17:50:01');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-03-01 18:30:16');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-03-17 11:17:17');
  • 1
    Your query will fail in Postgres: "column "c.cust_id" must appear in the GROUP BY clause or be used in an aggregate function" – a_horse_with_no_name Aug 27 '15 at 7:00
  • @a_horse_with_no_name but it will succeed if we use GROUP BY c.Cust_ID and 9.1+ ;) sqlfiddle.com/#!15/d1ea41/2 – ypercubeᵀᴹ Aug 27 '15 at 13:52
  • @ypercube: I know that, but it will not work as it is shown right now using o.Cust_ID. – a_horse_with_no_name Aug 27 '15 at 13:58
  • @a_horse_with_no_name I agree 100%. I expect Verace to edit - especially since he is suggesting Postgres as a better product, I expect answers to be checked first. Plus the query doesn't really gives us the order_number, as asked in the question. – ypercubeᵀᴹ Aug 27 '15 at 14:00
  • 1
    @Vérace , the GROUP BY behaviour of MySQL is well known. It is being corrected in 5.7 version after many years of weirdness. See Why does MySQL add a feature that conflicts with SQL standards? for some explanation and links with more info. – ypercubeᵀᴹ Aug 27 '15 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.