SQL Basics: How to get parts from multiple tables in a single query?

Question

I have used SQL for years, but never used its potential.

In this example, let's say that I have two tables:

CREATE TABLE messages ( MessageID INTEGER NOT NULL PRIMARY KEY, UserID INTEGER, Timestamp INTEGER, Msg TEXT); CREATE TABLE users ( UserID INTEGER NOT NULL PRIMARY KEY, UserName TEXT, Age INTEGER, Gender INTEGER, WebURL TEXT); 

As I understand it, PRIMARY KEY basically indexes the field so that it can be used as a quick search later - a query based on the exact values ​​of the primary key very quickly gets results even in huge tables. (which also ensures that the field must be unqiue in each record.)

In my current workflow, I would do something like

 SELECT * FROM messages; 

and then in the code for each message do:

 SELECT * FROM users WHERE UserID = results['UserID']; 

It clearly sounds very inefficient, and I know it can be done much better.

I want to end with a result that contains all the fields from messages , except that instead of the UserID field, it contains all the fields from the users table that match the specified UserID.

Can someone please give me a quick guide on how this can be done?

If that matters, I use SQLite3 as the SQL engine, but I might also want to do this in MySQL.

Thanks!

Answer 1

Not sure about the requested order, but you can adapt it.
Just JOIN tables on UserID

 SELECT MESSAGES.*, USERS.USERNAME, USERS.AGE, USERS.GENDER, USERS.WEBURL FROM MESSAGES JOIN USERS ON USERS.USERID = MESSAGES.USERID ORDER BY MESSAGES.USERID, MESSAGES.TIMESTAMP