INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...I love this site! It's so nice to know that there are so many people out there who are willing to share their knowledge..."
Geography
Where in the world do Tek-Tips members come from?
|
|
|
jimoo (Programmer) |
26 Jun 12 22:26 |
I have a contact table, and the contacts have contctinterests that is joined to the master lookup table of interest.
I want to retrieve a list of all the interests and join in the contacts interests whether or not they have a corresponding contactinterest.
I wrote:
select i.interestid, i.interest, i.sortorder, ci.interestid from interest i
left outer join contactinterest ci on i.interestid = ci.interestid
where i.active = 1 order by i.interestid, i.interest
However, this returns the interests (master table) more than once.
What I am seeking is a lists of all unique interests, and then a field that determines if this person has that particular interests.
Example
Interest
A 1
B 1
C NULL
D 1
E NULL
This means the interests are A through E and this person had interest A, B, and D in the contractinterest table.
I hope this makes sense. Thanks in advance for your help. Jim |
|
Could you post some example data and desired result? Borislav Borissov
VFP9 SP2, SQL Server |
|
|
Savil (Programmer) |
27 Jun 12 5:52 |
Jim
Correct me if i'm wrong but this smacks of a many to many relationship ie a contact can have many interests and an interest can have many contacts. If this the case then your schema is wrong and needs a link table to acheive what you want. If I'm barking up the wrong tree then you may need to explain further.
Savil |
|
|
imex (Programmer) |
27 Jun 12 10:01 |
|
|
jimoo (Programmer) |
27 Jun 12 12:15 |
select interestid, interest from interest
1 Update
2 Parks
3 Schools
4 Legislative
5 Sports
6 Vacation
7 Job Opportunites
Each person (contact) can have multiple interests (one to many) and they are stored in the contactInterest Table. The interest table holds the entire list of all insterest.
Thus, contact is one to many to contactInterest which is one to one to interest.
I want to retrieve a list of all interest, and then indicate if whether that individual contact has that give interest.
I could easily do this in 2 queries. Assume it is contactID of one had he has parks and sports setup as an intereest, I am looking for output like this:
Update 0
Parks 1
Schools 0
Legislative 0
Sports 1
Vacation 0
Job Opportunites 0
FYI: Imax, I added the contactID in the query but it only returned the interests for this contact.
Again, trying to get all interests, and then whether or not this contact has that interest in same query.
Jim |
|
Here is a baseline sample for what you're trying to do.
I've posted it a few times, but want a cleaner post to reference in the future.
CODECREATE TABLE myTestData(
[Year] int,
CRS_Code varchar(10),
School varchar(10),
Sub varchar(10),
prcnt money)
INSERT INTO myTestData
SELECT 2011, 'KT101', 'ML', 'English', .10 UNION ALL
SELECT 2011, 'KT101', 'ML', 'French', .60 UNION ALL
SELECT 2011, 'KT101', 'ML', 'German', .30 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'English', .60 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'French', .40 UNION ALL
SELECT 2011, 'KT102', 'ML', 'French', .50 UNION ALL
SELECT 2012, 'KT102', 'ML', 'French', .80 UNION ALL
SELECT 2013, 'KT102', 'ML', 'Japanese', .80 UNION ALL
SELECT 2013, 'KT102', 'JK', 'English', .20
DECLARE @_SQL varchar(8000), @int int
SELECT @_SQL = 'SELECT [Year], CRS_Code', @int = 0
SELECT @_SQL = @_SQL + ', MAX(CASE WHEN t.School = ''' + tabs.School + ''' and t.SUB = ''' +
tabs.Sub + ''' THEN t.prcnt else 0 END) as ['+tabs.School+'-'+tabs.Sub +']' +char(13)
FROM (Select School, SUB FROM (SELECT DISTINCT SUB FROM myTestData) as a
CROSS JOIN (SELECT DISTINCT School FROM myTestData) as b) as Tabs
SELECT @_SQL = @_SQL + 'FROM myTestData t GROUP BY [YEAR], CRS_Code'
PRINT @_SQL
Exec(@_SQL)
Lodlaiden You've got questions and source code. We want both!
There's a whole lot of Irish in that one. |
|
|
imex (Programmer) |
27 Jun 12 15:12 |
|
|
jimoo (Programmer) |
11 Jul 12 11:28 |
Thanks for all that replied. Here is the solution. This returns all interests and indicates by returning the interest id whether that contact has selected that interest. If the interest id is NULL, they have not selected that interest.
FYI: POI table stands for Points of Interest
Be sure to executed the commented scripts at the bottom to delete the temp tables.
CODEcreate table #contact (contactid int, name char(15))
insert into #contact values (1, 'Jim')
insert into #contact values (2, 'Joe')
insert into #contact values (3, 'Phil')
insert into #contact values (4, 'Bob')
--select * from #contact
create table #interest (interestid int, interest char(15))
insert into #interest values (1, 'Item 1')
insert into #interest values (2, 'Item 2')
insert into #interest values (3, 'Item 3')
insert into #interest values (4, 'Item 4')
--select * from #interest
create table #poi (poiid int, contactid int, interestid int)
insert into #poi values (1, 2, 1)
insert into #poi values (2, 2, 2)
insert into #poi values (3, 3, 2)
insert into #poi values (4, 3, 3)
--select * from #poi
--SOLUTION
declare @contactid int
set @contactid = 2
select * from #interest i left outer join #poi p
on i.interestid = p.interestid
and p.contactid = @contactid
/*
drop table #poi
drop table #interest
drop table #contact
*/ Jim |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One