sql query3 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql query3

Author

Topic

purisqlserver
Yak Posting Veteran

73 Posts

Posted - 2001-12-13 : 23:21:12

sir,
I am given three tables emp_master,cust_master,users.......with login_master as the fourth table.The input to query is login_id from
login_master,which has all the login_id........these login_id are present in either of the three tables depending on the type employee,customer or user.In a single query to return the user name,given the login_id , searching out in the three tables,which have different columns names for data on the user name.The login_master does not contain the information on the type of user except for the login_id.How to go about this query.

Structure:
Tables
emp_master(Type,user name,login_id)
cust_master(Type,user name,login_id)
users(Type,user name,login_id)

Login_master(Login_id)

Thank you,

Nazim
A custom title

1408 Posts

Posted - 2001-12-14 : 05:20:53

select Type,user name,login_id from
emp_master e
inner join login_master l
on e.login_in=l.login_id
union
select Type,user name,login_id from cust_master c
inner join login_master l
on c.login_in=l.login_id
unioun
select Type,user name,login_id from users u
inner join login_master l
on u.login_in=l.login_id

u can add a where condition if u filter more.

HTH

-------------------------
"Success is when Preparedness meets Opportunity"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-14 : 11:21:02

If there is no other data in the login_master table that you need to display, you don't even need to join that table to the others. If you're querying for a specific known loginID, just put the where clause on each table in the UNION statement.

-------------------
It's a SQL thing...

Nazim
A custom title

1408 Posts

Posted - 2001-12-14 : 23:31:07

quote:

If there is no other data in the login_master table that you need to display, you don't even need to join that table to the others. If you're querying for a specific known loginID, just put the where clause on each table in the UNION statement.

-------------------
It's a SQL thing...

Thanx for the Tip Ajarn , i think that will great optimised the query.

-------------------------
"Success is when Preparedness meets Opportunity"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources