Pull value if two tables have entry based on a third table.

Question

Post reply

Pull value if two tables have entry based on a third table.

Jeff Sims-413169

Say Hey Kid

Points: 711
More actions
October 29, 2013 at 9:07 am

#279324

I have three tables Callog, Asnmnt and Tracker. With the used values for this query below.

Callog- Callid and ownerteam. Asnmnt - callid and groupname Tracker - loginid and team.

What I need is based on Tracker.loginID pull the tracker.team and compare to the other tables and return a callid if it is in either Calllog or Asnmnt table - (does not have to be in both) I need it to reture the calllog only once if it does find it in either or both.

(of course if there is a asnmnt record there is a linked calllog record to correspond) (one to many)

I tried my joins but not sure what to use.

The below pulls the callid if both calllog.ownerteam and Asnmnt.groupname = the tracker.team but I need it to pull if either one not only if both... HELP what joins do I need?

USE heat

SELECT distinct (c.callid)

FROM calllog c INNER JOIN Tracker ta

ON c.OwnerTeam = ta.Team LEft Outer JOIN Asgnmnt a

ON ta.Team = a.GroupName

WHERE ta.LoginID = 'simsj'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

This is just a shot in the dark. It might be wrong but the information you gave is limited. Could you post DDL and sample data as well as expected results? For information on how to do it, read the article linked on my signature.

SELECT distinct (c.callid)



FROM calllog c

WHERE EXISTS( SELECT * FROM Tracker ta

          WHERE c.OwnerTeam = ta.Team

          AND ta.LoginID = 'simsj')

OR EXISTS( SELECT * FROM Asgnmnt a

WHERE ta.Team = a.GroupName)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Sims-413169 Say Hey Kid Points: 711 More actions · Answer 2

Getting errors that the ta.team could not be bound. Here is a quick example of what I need.

Edit: Sorry I will read article I missed that part and try to repost if you cant read this.

Calllog Asgnmnt Tracker

Callid Ownerteam Callid Groupname LoginID Team

1111 Team1 1111 Team2 Simsj Team2

2222 Team2 2222 Team2

3333 Team1 3333 Team1

4444 Team1 3333 Team2

Calllid links the Calllog and Asgnmnt tables. (there can be no Asgnmnt for a callid or many)

So I want a query that returns one distinct value, callid, if either calllog ownerteam or Asgnmnt Groupname is = the the team of the loginid

So for Simsj. would return

Calllog.Callid

1111

2222 (notice only returns this value once even tho it both tables)

3333

LinksUp SSCertifiable Points: 6526 More actions · Answer 3

Jeff Sims-413169 (10/29/2013)

Getting errors that the ta.team could not be bound.

You are getting that error because the 2nd exists query knows nothing about the alias ta at the time of parsing. In order to fix that, I replaced the Where clause on Mr. Cazares original query with an inner join.

Note: The distinct around c.Call_ID should not be necessary. If proper short-circuit boolean evaluation is being employed, the first Exists that returns a true will be used, so only 1 of each Call_ID will be returned. Of course, it does not hurt (too much) to have it there.



SELECT distinct (c.callid)

FROM calllog c

WHERE EXISTS( SELECT * FROM Tracker ta

          WHERE c.OwnerTeam = ta.Team

          AND ta.LoginID = 'simsj')

  OR  EXISTS( SELECT * FROM Asgnmnt a

            INNER JOIN Tracker ta on ta.Team = a.GroupName)

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/