How to get column from other tables, to form a new table?

Question

I'm relatively new to working with JDBC and SQL. I have two tables, CustomerDetails and Cakes. I want to create a third table, called Transactions, which uses the 'Names' column from CustomerDetails, 'Description' column from Cakes, as well as two new columns of 'Cost' and 'Price'. I'm aware this is achievable through the use of relational databases, but I'm not exactly sure about how to go about it. One website I saw said this can be done using ResultSet, and another said using the metadata of the column. However, I have no idea how to go about either.

Answer 1

What you're probably looking to do is to create a 'SQL View' (to simplify - a virtual table), see this documentation

CREATE VIEW view_transactions AS
SELECT Name from customerdetails, Description from cakes... etc.
FROM customerdetails;

Or something along those lines

That way you can then query the View view_transactions for example as if it was a proper table.

---

Also why have you tagged this as mysql when you are using sqlite.

Answer 2

You should create the new table manually, i.e. outside of your program. Use the commandline 'client' sqlite3 for example.

If you need to, you can use the command .schema CustomerDetails in that tool to show the DDL ("metadata" if you want) of the table.

Then you can write your new CREATE TABLE Transactions (...) defining your new columns, plus those from the old tables as they're shown by the .schema command before.

Note that the .schema is only used here to show you the exact column definitions of the existing tables, so you can create matching columns in your new table. If you already know the present column definitions, because you created those tables yourself, you can of course skip that step.

Also note that SELECT Name from CUSTOMERDETAILS will always return the data from that table, but never the structure, i.e. the column definition. That data is useless when trying to derive a column definition from it.

---

If you really want/have to access the DB's metadata programatically, the documented way is to do so by querying the sqlite_master system table. See also SQLite Schema Information Metadata for example.

Answer 3

You should read up on the concept of data modelling and how relational databases can help you with it, then your transaction table might look just like this:

CREATE TABLE transactions ( 
  id          int not null primary key
, customer_id int not null references customerdetails( id )
, cake_id     int not null references cakes( id )
, price       numeric( 8, 2 ) not null
, quantity    int not null 
);

This way, you can ensure, that for each transaction (which is in this case would be just a single position of an invoice), the cake and customer exist.

And I agree with @hanno-binder, that it's not the best idea to create all this in plain JDBC.