Help with SQL Join

[jimoo]

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

 

[bborissov]

Could you post some example data and desired result?

Borislav Borissov
VFP9 SP2, SQL Server

 

[Savil]

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]

Did not missing add a filter to limit the result to one of the contacts? Ex:

where (i.active = 1) and (ci.contactid = 123)

Hope it's useful.


[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[jimoo]

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

 

[Qik3Coder]

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.

CREATE 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]

Try:

where (i.active = 1) and (ci.contactid = 123 or ci.contactid is null)

Hope this helps.


[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[jimoo]

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.

create 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