Using 3 TBLS. Want to display fields frm othr tbls

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

General SQL Server Forums

New to SQL Server Programming

Using 3 TBLS. Want to display fields frm othr tbls

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 09:32:43

I am using three tables.

select * from ldap_users as PL
where (emcidentitytype like '%P%')
and(ctscUserKeywords NOT LIKE '%LockedOut%')
AND(ctscUserKeywords NOT LIKE '%LockedOut%')
and emclastdayworked = ''
and (uid in(select fax from oracle_11i_user)
or uid in (select fax from ORACLE_1103_USER))

This query only display all the fields from PL.
I want to display end_date from both oracle11i and oracle1103 tables?
How would I do that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 09:38:47

Then you have to JOIN the tables.

SELECT		u.*,
		x.End_Date
FROM		LDAP_Users AS u
INNER JOIN	(
			SELECT	Fax,
				End_Date
			FROM	Oracle_11i_User

			UNION

			SELECT	Fax,
				End_Date
			FROM	Oracle_1103_User
		) AS x ON x.Fax = u.UID
WHERE		u.EmcIdentityType LIKE '%P%'
		AND u.ctscUserKeywords NOT LIKE '%LockedOut%'
		AND u.emcLastDayWorked = ''

E 12°55'05.25"
N 56°04'39.16"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-21 : 09:41:54

depending on the distribution of values and row counts in the Oracle tables this may perform better - maybe not:

Assuming this is MS Sql Server (rather than oracle),
you need to include the tables in your FROM clause to see those table's columns in the results.
This also assumes that PL.uid values really match your [fax] values in the other oracle_* tables:


select <columnList from any of the tables in the JOIN FROM clause>
from   ldap_users as PL
inner join oracle_11i_user z
              on z.fax = pl.uid
inner join oracle_11i_user y
              on y.fax = pl.uid
where  emcidentitytype like '%P%'
and    pl.ctscUserKeywords NOT LIKE '%LockedOut%'
AND    pl.ctscUserKeywords NOT LIKE '%LockedOut%'
and    pl.emclastdayworked = ''

Be One with the Optimizer
TG

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 09:49:04

select PL.uid,PL.displayname,PL.mail,PL.emcidentitytype,PL.emcntlogin,PL.emcloginname,PL.emcmanager,
PL.ctscUserKeywords,PL.emcLastDayWorked,O1.end_date,O2.end_date
from ldap_users as PL
inner join oracle_11i_user O1
on O1.fax = pl.uid
inner join ORACLE_1103_USER O2
on O2.fax = pl.uid
where emcidentitytype like '%P%'
and pl.ctscUserKeywords NOT LIKE '%LockedOut%'
AND pl.ctscUserKeywords NOT LIKE '%LockedOut%'
and pl.emclastdayworked = ''

I tried this b4 posting my first question. Let say if i had about 10rows in PL that has 5 matches with O1 wouldn't the 5 will be matched with O2 instead or 10rows from PL? Isn't this true.

The above query returns nothing.

Saru

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 09:50:45

I also tried this, but in this case, the two fields i want to display is o1.Enddate and O2.end_date is not possible. Only one end-date column is displayed.

select PL.uid,PL.displayname,PL.mail,PL.emcidentitytype,PL.emcntlogin,PL.emcloginname,PL.emcmanager,
PL.ctscUserKeywords,PL.emcLastDayWorked,O1.fax
from ldap_users as PL
INNER JOIN dbo.oracle_11i_user AS O1
ON dbo.zeroExtend(PL.uid) = dbo.zeroExtend(O1.fax)
WHERE (ctscUserKeywords NOT LIKE '%Lockedout%')
AND(ctscUserKeywords NOT LIKE '%LockedOut%')
and (emcidentitytype like '%P%')
UNION
select PL.uid,PL.displayname,PL.mail,PL.emcidentitytype,PL.emcntlogin,PL.emcloginname,PL.emcmanager,
PL.ctscUserKeywords,PL.emcLastDayWorked,O2.fax
from ldap_users as PL
INNER JOIN ORACLE_1103_USER as O2
ON dbo.zeroExtend(PL.uid) = dbo.zeroExtend(O2.fax)
WHERE (ctscUserKeywords NOT LIKE '%Lockedout%')
AND(ctscUserKeywords NOT LIKE '%LockedOut%')
and (emcidentitytype like '%P%')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 10:01:35

[code]SELECT u.*,
a.End_Date,
b.End_Date
FROM LDAP_Users AS u
LEFT JOIN Oracle_11i_User AS a ON a.Fax = u.UID
LEFT JOIN Oracle_1103_User AS b ON b.Fax = u.UID
WHERE u.EmcIdentityType LIKE '%P%'
AND u.ctscUserKeywords NOT LIKE '%LockedOut%'
AND u.emcLastDayWorked = ''
AND (a.Fax IS NOT NULL OR b.Fax IS NOT NULL)[/code]

E 12°55'05.25"
N 56°04'39.16"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-21 : 10:06:43

sorry, I missed the OR condition in your original query (you should use [CODE] tags when you post so you don't loose your formatting)

quote:
Let say if i had about 10rows in PL that has 5 matches with O1 wouldn't the 5 will be matched with O2 instead or 10rows from PL? Isn't this true.

I didn't quite follow question - see if this works:


select <columnList from any of the tables in the JOIN clause>
from   ldap_users as PL
left outer join oracle_11i_user z
              on z.fax = pl.uid
left outer join oracle_11i_user y
              on y.fax = pl.uid
where  emcidentitytype like '%P%'
and    pl.ctscUserKeywords NOT LIKE '%LockedOut%'
AND    pl.ctscUserKeywords NOT LIKE '%LockedOut%'
and    pl.emclastdayworked = ''
and    coalesce(z.fax, y.fax) is not null

Be One with the Optimizer
TG

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 15:48:35

The where clause does not seem to work. I cant try where before the joins of the two other tables.

SELECT PL.uid,PL.displayname,PL.mail,PL.emcidentitytype,PL.emcntlogin,PL.emcloginname,PL.emcmanager,
PL.ctscUserKeywords,PL.emcLastDayWorked,
O1.End_Date as Ora11i_EndDate,
O2.End_Date as Ora1103_EndDate
FROM LDAP_Users AS PL
LEFT JOIN Oracle_11i_User AS O1 ON O1.Fax = PL.UID
LEFT JOIN Oracle_1103_User AS O2 ON O2.Fax = PL.UID
WHERE (PL.EmcIdentityType LIKE '%P%')
AND(PL.ctscUserKeywords NOT LIKE '%LockedOut%')
AND(PL.ctscUserKeywords NOT LIKE '%LockedOut%')
AND PL.emcLastDayWorked = ''
AND coalesce(O1.Fax, O2.Fax) is not null
AND O1.end_date is null or O1.end_date > getdate() + 30
AND O2.end_date is null or O2.end_date > getdate() + 30

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 15:49:58

where clause only table PL does not seem to work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 15:59:35

[code]SELECT PL.uid,
PL.displayname,
PL.mail,
PL.emcidentitytype,
PL.emcntlogin,
PL.emcloginname,
PL.emcmanager,
PL.ctscUserKeywords,
PL.emcLastDayWorked,
O1.End_Date as Ora11i_EndDate,
O2.End_Date as Ora1103_EndDate
FROM LDAP_Users AS PL
LEFT JOIN Oracle_11i_User AS O1 ON O1.Fax = PL.UID
AND O1.end_date > GETDATE() + 30
LEFT JOIN Oracle_1103_User AS O2 ON O2.Fax = PL.UID
AND O2.end_date > GETDATE() + 30
WHERE PL.EmcIdentityType LIKE '%P%'
AND PL.ctscUserKeywords NOT LIKE '%LockedOut%'
AND PL.emcLastDayWorked = ''
AND COALESCE(O1.Fax, O2.Fax) IS NOT NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 16:19:11

All set. Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources