Query information from two tables

Databases
1

Hello everyone!

Assuming I have a users table and an articles table with the following structure


users (id, name)
articles (id, title, created_by)

created_by contains the id of the user that created the article, and I want to create a table that contains all articles so I can display

article title, article id, created by name

For the first two I can just query the articles table and will have the records to do whatever I want with them, for the created by name I have to query the database with the ids of the users I need the names and then do whatever I want with the data, at least the way I am doing it right now is query the database twice, once for the articles and the second time for the needed users’ names, if there is an easier way can someone please point me in the right direction by giving me a sample?

2

you don’t want to create a table – that would be storing redundant data, a real headache in the making

you just want to retrieve the data

may i introduce you to the concept of a join query

SELECT articles.title
     , articles.id
     , users.name
  FROM articles 
INNER 
  JOIN users 
    ON users.id = articles.created_by
3

I should have clarified, when I said I want to create a table I meant display the records in a table and not create an additional table in the database, the actual reason I was asking about this is to avoid redundant data

may i introduce you to the concept of a join query

SELECT articles.title
     , articles.id
     , users.name
  FROM articles 
INNER 
  JOIN users 
    ON users.id = articles.created_by

Thank you I will try this

4

I just ran the query and do get the correct results, however when I want to use the results how could I differentiate one from another one, since users have a column called id and the same goes for the articles, then I get a result with two columns called id?

5

not from the query i gave you :slight_smile:

however, to solve this problem, you would use column aliases

SELECT articles.title
     , [COLOR="#0000FF"]articles.id AS articles_id[/COLOR]
     , [COLOR="#FF0000"]users.id    AS users_id[/COLOR]
     , users.name
  FROM articles 
INNER 
  JOIN users 
    ON users.id = articles.created_by
6

Thank you so much I was using AS but the wrong way, I was doing this:


SELECT * 
FROM e70n2_tlakcalendarcategories
INNER 
  JOIN e70n2_users AS user_name
    ON e70n2_users.id  = e70n2_tlakcalendarcategories.created_by

So “AS” should be used before the INNER JOIN right?

7

This is the query I am trying to run but I am getting an error,


SELECT * 
FROM e70n2_tlakcalendarcategories,
e70n2_users.id AS user_id,
e70n2_users.username AS user_name,
INNER 
  JOIN e70n2_users
    ON e70n2_users.id  = e70n2_tlakcalendarcategories.created_by

basically I am trying to get all from the table e70n2_tlakcalendarcategories and match the username from the table e70n2_users using the e70n2_tlakcalendarcategories.created_by

8

I finally got it, I just need to practice more doing this kind of thing to better understand it, in my previous query I had an extra comma, thank you for all your help

9

that wasn’t all that was wrong with it, but i’m glad you were able to fix it yourself

:slight_smile:

10

Earlier I asked how I could join two tables to match records from two tables and was able to pull the records that I wanted however I have a list of categories and each category can belong to another category, so lets say category 1 can have many subcategories so I need to get the name of the parent category, if I have a table with the structure:


categories ( id=the id of the category, name=the name of the category, parentcategory=the id of the parent category stored in this same table )

SO I want to run a query that gives me id, name, parentcategoryid and parentcategoryname, I ran this query


SELECT e70n2_tlakcalendarcategories . * , e70n2_users.id AS user_id, e70n2_users.username AS created_un
FROM e70n2_tlakcalendarcategories
INNER JOIN e70n2_users ON e70n2_users.id = e70n2_tlakcalendarcategories.created_by
INNER JOIN e70n2_tlakcalendarcategories ON e70n2_tlakcalendarcategories.id = e70n2_tlakcalendarcategories.catid

but I get: Not unique table/alias: ‘e70n2_tlakcalendarcategories’

I am trying to get the parent’s category name with an alias so I can then identify it properly

11

I have some progress on it but I am still not getting the results so I am still missing something, I now don’t get an error but I am not getting the category’s name, this is the query so far:


SELECT 
	e70n2_tlakcalendarcategories.*,
	e70n2_users.id AS user_id,
	e70n2_users.username as created_un
FROM e70n2_tlakcalendarcategories 
INNER JOIN e70n2_users ON e70n2_users.id = e70n2_tlakcalendarcategories.created_by
INNER JOIN e70n2_tlakcalendarcategories catname ON e70n2_tlakcalendarcategories.id = e70n2_tlakcalendarcategories.catid
12

Okay, I finally got it, bellow is the query in case someone comes looking for the same thing


SELECT 
maintable . * , 
secondarytable.id AS user_id, 
secondarytable.username AS created_un, 
maintablealias.title AS catname
FROM maintable
INNER JOIN 
secondarytable ON secondarytable.id = maintable.created_by
INNER JOIN 
maintable maintablealias ON maintablealias.id = maintable.catid