I have 3 tables

Account

acc_id
acc_name
acc_status
acc_owner_id

User

role_id
user_name

Sub_Contract_ID

basic_con_id
start_date
end_date

also,

Account.acc_owner_id = User.role_id

I want the result to contain

acc_id
acc_name
acc_status
user_name
basic_con_id
start_date
end_date
WHERE acc_status  = "Inactive"

I did try a way but was getting stuck at how I can pull user_name using Account.acc_owner_id = User.role_id and fit it into the main query

This is what I tried:

SELECT 
Account.acc_id,
Account.acc_name,
Account. acc_status,
Sub_Contract_ID.basic_con_id,
Sub_Contract_ID.basic_con_id.start_date,
Sub_Contract_ID.end_date
FROM 
(
Account,
User,
Sub_Contract_ID)
WHERE Account. acc_status = “Inactive”

Please advise!

CodePudding user response：

Try this:

SELECT a.acc_id, a.acc_name, a.acc_status, u.user_name, sci.basic_con_id, sci.start_date, sci.end_date
FROM Account AS a
LEFT JOIN User AS u ON a.acc_owner_id = u.role_id
LEFT JOIN Sub_Contract_ID AS sci ON ????
WHERE a.acc_status = 'Inactive'

You just have to replace ???? by the relation between User or Account and Sub_Contract_ID because you haven't describe this relation in your post.

CodePudding user response：

use JOIN to connect several tables

SELECT *
FROM Account 
JOIN User on Account.acc_owner_id = User.role_id
WHERE Account. acc_status = 'Inactive'

please note: There are different types of joins, and the syntax is DB-system specific