select * from 
   Topics_Discussion d
left join 
   Topics_User u 
on	
   d.userid = u.userid

select * from Topics_Discussion d
left join Topics_User u 
on d.userid = u.userid
where u.userid IS NOT NULL

WITH my_topics_user (userid, nickname) 
AS (SELECT DISTINCT userid, nickname 
FROM [WiseTopic_topics_user])
	SELECT e.*, d.* FROM [WiseTopic_Topics_Discussion] d
LEFT JOIN
	my_topics_user u on d.userid = u.userid
LEFT JOIN
	[WiseTopic_Topics_Category] e on e.categoryid = d.categoryid
where 
	u.userid IS NOT NULL

quote:

---

Originally posted by webfred

A common table expression is only one way to solve your problem from above.
It's just a resultset that you can use as a table.
I like the readability of this way.

For more info see:
http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx

Greetings
Webfred

Planning replaces chance by mistake

---

select * from Topics_Discussion d
inner join (select distinct userid, nickname
           from Topics_User) u 
on d.userid = u.userid

quote:

---

Originally posted by shawnmolloy

Common Table Expression, huh? I like it! Thanks for this new piece of knowledge, Fred.

Hey one last question, if you don't mind?

If I want to expand this query with another join, I assume I'd do this:

WITH my_topics_user (userid, nickname) 
AS (SELECT DISTINCT userid, nickname 
FROM [WiseTopic_topics_user])
	SELECT e.*, d.* FROM [WiseTopic_Topics_Discussion] d
LEFT JOIN
	my_topics_user u on d.userid = u.userid
LEFT JOIN
	[WiseTopic_Topics_Category] e on e.categoryid = d.categoryid
where 
	u.userid IS NOT NULL

But there are two problems:

1) How do I add the columns from "e" (the new table; category)
2) Why now is the data redundant? What I mean is... now the results look like:

categoryId title
------------- -------------------
1 Politics
2 Aliens
1 Politics
2 Aliens

I musta done something screwy......

---

SELECT e.*, d.* FROM [WiseTopic_Topics_Discussion] d
INNER JOIN
	(SELECT DISTINCT userid, nickname 
FROM [WiseTopic_topics_user]) u on d.userid = u.userid
LEFT JOIN
	[WiseTopic_Topics_Category] e on e.categoryid = d.categoryid

SELECT e.*, d.* FROM [WiseTopic_Topics_Discussion] d
INNER JOIN
	(SELECT DISTINCT userid, nickname 
FROM [WiseTopic_topics_user]) u on d.userid = u.userid
LEFT JOIN
	(SELECT DISTINCT categoryid,title
         FROM [WiseTopic_Topics_Category]) e on e.categoryid = d.categoryid

quote:

---

Originally posted by webfred

Hello Visa,
i have never intended that CTE is absolute needed but I Like It.
I have no preference for left join or (inner) join. I think it doesn't matter.

Greetings
Webfred

Planning replaces chance by mistake

---

quote:

---

Originally posted by webfred

OK, i will take that in mind the next time.

And in the join above it would be useful for the output to include u.nickname in selectlist wouldn't it?

Webfred

Planning replaces chance by mistake

---

quote:

---

Originally posted by shawnmolloy

Visakh. I changed my stored proc to use the join query example you have above and it produced the same results. I like that syntax much better as well, its easier to get your head around. Derived tables are the way to go.

And also, next time I post I'll be sure to post my database table structure as well. Speaking of, whats the best way to translate that info from the database to a forum post? I've tried changing the results in Query Analyzer to "Results to Text" (as opposed to grid) and pasting it, but that never works well. I could take a screen shot of the table schemas but that is a pain. Or I could just list out the column names and types, but is there a standard format for doing that?

Thanks.

---

quote:

---

Originally posted by shawnmolloy

Thanks for the link; its a good read.

1. State the question
2. Post the DDL
3. Post sample DML
5. Post the expected results

Good rules to post by, I'll keep them in mind.

---