0

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!

  • 1
    How is sub_contract_id related in this – Nathan_Sav Mar 09 '22 at 12:53
  • 2
    There's several problems here. Firstly you put your delimited list of tables in parenthesis (`()`); the `FROM` doesn't go inside parenthesis (it's not a function). Speaking of the delimited list of tables, the ANSI-92 explicit JOIN has been around for **30 years**; long past time you adopted it. [Bad Habits to Kick : Using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins). Also you're using "smart quotes" around `Inactive` which aren't supported. Also is `Inactive` *meant* to be the **column** `Inactive`or should it be the literal string `'Inactive'`? – Thom A Mar 09 '22 at 12:55
  • 2
    `USER` is also a [Reserved Keyword](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver15) in T-SQL. If you want to reference an object with a reserved keyword you *must* delimit identify it. Ideally, however, don't use reserved keywords for object names at all (or any name that needs delimited identification). – Thom A Mar 09 '22 at 12:56
  • 6
    JOINs are a fundamental feature of SQL. Not knowing how they work is like asking someone how to parallel park a car, but saying you don't know how the steering wheel works. As Stackoverflow is not geared up to teach the fundamentals of a language, I strongly recommend, for your own benefit, that you invest time in a course for SQL. There are a great many free courses online. – MatBailie Mar 09 '22 at 12:59
  • 1
    Does this answer your question? [SQL Inner-join with 3 tables?](https://stackoverflow.com/questions/10195451/sql-inner-join-with-3-tables) – pringi Mar 09 '22 at 13:01

2 Answers2

2

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.

Hans Kesting
  • 38,117
  • 9
  • 79
  • 111
Alaindeseine
  • 3,260
  • 1
  • 11
  • 21
  • Thanks for this, I do not have any relation in `Sub_Contract_ID`. I have to get all the field from `Account` table and `user_name` from `User` table where `acc_owner_id` =`role_id` and where `acc_status` is "Inactive". Now for this set of accounts I want all the fields of `Sub_Contract_ID`. How can I do this –  Mar 11 '22 at 12:19
  • If you don't have any relation between Sub_Contract_Id and other tables, it quite impossible to join. I don't undertand what you want to do. – Alaindeseine Mar 11 '22 at 12:48
-3

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. In case you are using a DB-system where "User" is a keyword like in MS-SQL, you have to escape it according to the DB-system specifications, in case of MS-SQL you have to use double quotes.

draz
  • 793
  • 6
  • 10