Can you include the columns of two separate tables in the result of a sql query?

All we need is an easy explanation of the problem, so here it is.

I am trying to order the results of a sql query of one table using the column of another table. Is it possible to include this column from a different table in my results?

For example, this query uses an inner join to order items in the Children table based on the Parent table’s column "dateReceived":

SELECT c.* 
FROM children c
INNER JOIN parent p
ON c.parent_id = p.parent_id  
ORDER BY dateReceived ASC;

Results:
Can you include the columns of two separate tables in the result of a sql query?

The view would be more helpful if it could somehow include the "dateReceived" column from the Parent table in the results. I am new to SQL, is this possible?

Thank you.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Yes all you need to do is add that column to your SELECT list via the correct alias like so:

SELECT p.dateReceived, c.*
FROM children c
INNER JOIN parent p
ON c.parent_id = p.parent_id  
ORDER BY p.dateReceived ASC;

Notice that the alias p represents your parent table and c represents your children table in the context of this query.

Also you shouldn’t use * in your SELECT list (most times) because it is bad practice for readability, maintainability, performance reasons, and is error prone. Instead you should explicitly list out each column that you want returned. For example:

SELECT 
    p.dateReceived, 
    c.child_id, 
    c.parent_id, 
    c.firstName, 
    c.gender, 
    c.age, 
    c.shoe, 
    c.notes
FROM children c
INNER JOIN parent p
ON c.parent_id = p.parent_id  
ORDER BY p.dateReceived ASC;

Method 2

Replacing c.* with * would return the results from both tables. As you currently have it written, c is the alias for children table. So, you’re only getting the columns from that table.

Try this…

SELECT c.*, p.dateReceived
FROM children c
INNER JOIN parent p
ON c.parent_id = p.parent_id  
ORDER BY dateReceived ASC;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply