help with query JOIN ? - 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 2005 Forums

Transact-SQL (2005)

help with query JOIN ?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-22 : 06:01:39

Hi,

I have the following query which I just realized is not bringing back exactly the results I have desired. I have two tables as shown below.

The current query is pretty simple. The problem is that I want to limit the rows that are brought back from this, to rows that have a corresponding "interestID" in "tblInterests". (this happens when users delete from the system, and nobody else shares their interest) I thought I could do this with a JOIN, but I dont end up getting the desired results when I do that. Perhaps I am doing it wrong?

SELECT interestID, interest FROM tblInterestDetails

any help is greatly appreciated ..

thanks very much !!

mike123

CREATE TABLE [dbo].[tblInterests](
[userID] [int] NOT NULL,
[interestID] [int] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblInterestDetails](
[interestID] [int] IDENTITY(1,1) NOT NULL,
[interest] [varchar](50) NOT NULL
) ON [PRIMARY]

GO

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-22 : 06:05:36

You can use a Not In clause.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-22 : 06:17:51

quote:
Originally posted by ayamas

You can use a Not In clause.

How so? I dont really think this is what I am looking for, but not sure. Can't I do a join?

Thanks!
mike

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-22 : 06:23:37

Oops my mistake.It must had been aa IN clause if I have understood the requirement right.

select * from tblInterestDetails where interestID IN
(
select interestID from tblInterests

)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 06:51:06

[code]SELECT userID,
interestID,
interest
FROM (
SELECT i.userID,
id.interestID,
id.interest,
ROW_NUMBER() OVER (PARTITION BY i.userID ORDER BY id.interestID) AS RecID
FROM tblInterestDetails AS id
INNER JOIN tblInterests AS i ON i.interestID = id.interestID
) AS d
WHERE RecID <= 3
ORDER BY userID,
interestID[/code]

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-22 : 22:15:00

Hey Peso,

I think perhaps I didnt explain well enough, but I am getting the following data brought back not as desired... I just want a unique list of the interests

userID / interestID / interest
32280 73 books
32280 407 reading
32280 1320 learning
32282 73 books

Also, would it be hard to take it one step further and only bring interests that minimum 3 users share ?

thanks verymuch

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 03:40:35

As before, please post some proper sample data and your expected output based on the supplied sample data.
And no, it isn't hatrd to write a query to return interests that is shared by a minimum of three users.

SELECT		id.interestID
FROM		tblInterestDetails AS id
INNER JOIN	tblInterests AS i ON i.interestID = id.interestID
GROUP BY	id.interestID
HAVING		COUNT(DISTINCT i.userID) >= 3

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 04:43:18

quote:
Originally posted by mike123

Hey Peso,

I think perhaps I didnt explain well enough, but I am getting the following data brought back not as desired... I just want a unique list of the interests

userID / interestID / interest
32280 73 books
32280 407 reading
32280 1320 learning
32282 73 books

Also, would it be hard to take it one step further and only bring interests that minimum 3 users share ?

thanks verymuch
mike123

and wat about the other fields? while you want unique list of interestids, what's the value of userid that you want to retrieve? show us by some sample

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-23 : 09:22:28

quote:
Originally posted by Peso

As before, please post some proper sample data and your expected output based on the supplied sample data.
And no, it isn't hatrd to write a query to return interests that is shared by a minimum of three users.
SELECT id.interestID
FROM tblInterestDetails AS id
INNER JOIN tblInterests AS i ON i.interestID = id.interestID
GROUP BY id.interestID
HAVING COUNT(DISTINCT i.userID) >= 3

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

sorry for not being more clear, but you got exactly what I was looking for :)

Thanks once again
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources