Query madness.........Outer Join? Inner? Full? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Query madness.........Outer Join? Inner? Full?

Author

Topic

jeffery1493
Starting Member

8 Posts

Posted - 2007-03-21 : 21:48:17

Hi All,

I thought I was reasonably familiar with SQL syntax, but I guess I'm wrong.

What I'm trying to do is: I have a table which already has information in it, and I want to add a second table some of the same fields.

I'm doing a query on the first table already which works fine, like this:

SELECT topic_time, topic_title, topic_id, comments
FROM `topics`
WHERE topic_id = $person_selected

I have a 2nd table which does not have all the fields of `topics`, but has the fields for this query (topic_time, topic_id, comments, etc).

I want to pull all the info from the first query, and IF there are any rows in the new table that also match the $person_selected, include those. If not, just list the contents of the first table.

Does this make sense? I know it is an outer join of some sort, but I have been struggling fruitlessly for hours. Thanks for any advice,

JEFFERY1493

jeffery1493
Starting Member

8 Posts

Posted - 2007-03-21 : 21:50:21

Sorry if any confusion, I see nowhere were I can edit my posting. If it doesn't make sense, I'm here to reply, thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 22:01:03

LEFT JOIN


SELECT  *
FROM   [topics] t1
LEFT JOIN [2ndTable] t2 ON t1.topic_id = t2.topic_id
WHERE  t1.topic_id = $person_selected

KH

jeffery1493
Starting Member

8 Posts

Posted - 2007-03-21 : 22:18:58

Ugh, it's still coming up blank.

Okay, here is the beast with its slippers off. REVIEWS_TABLE was the original table/query which works fine. (Table with customer reviews).
SCROD_TABLE, has some additional reviews from the owner that need to be added in, if there are any for the user picked. I now have this:

-----
$sql = "SELECT user_id, user_rating, review_comment, post_closed
FROM " . REVIEWS_TABLE .
" LEFT JOIN " . SCROD_TABLE . " ON " . REVIEWS_TABLE . ".user_id = " . SCROD_TABLE . ".user_id
WHERE user_id = " . $user_selected .
" AND (post_closed = 1 OR post_closed = 100)
ORDER BY topic_time DESC"
-----

Which comes up empty.....................SIGH

jeffery1493
Starting Member

8 Posts

Posted - 2007-03-21 : 22:20:18

Sorry, topic_time was also in the SELECT statement. Wish I could edit my posting.......

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 22:31:53

click on the EDIT Reply button just on top of your post to edit it.

KH

jeffery1493
Starting Member

8 Posts

Posted - 2007-03-21 : 22:35:33

I only have two buttons, "Show Profile" and "Reply with Quote". There's nothing else there.
Maybe I need to earn the right to edit........too new in the forums...........

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 22:42:48

there are a rows of button just above the post itself. on the same row as the "Posted - 03/21/2007 : 22:20:18". Of-course you can only edit your own post.

KH

jeffery1493
Starting Member

8 Posts

Posted - 2007-03-21 : 22:48:00

Thats the row where I've only got those two buttons

What could possibly be wrong with this outer join?

I would assume you can do an outer join, and then select a certain number of rows from the original table. It has to be possible.

I couldn't see much use for an outer join, otherwise..........

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 22:57:54

post_closed is from which table ?

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 23:01:16

Try placing the post_closed condition in the ON part.


SELECT    r.user_id, user_rating, review_comment, post_closed
FROM      REVIEWS_TABLE r
LEFT JOIN SCROD_TABLE s 
ON        r.user_id = s.user_id
AND (
               post_closed = 1 
          OR   post_closed = 100
    )
WHERE r.user_id = $user_selected
ORDER BY topic_time DESC

And use table alias for ease of reference and clarity.

KH

jeffery1493
Starting Member

8 Posts

Posted - 2007-03-21 : 23:02:56

All of the fields are from the REVIEWS_TABLE, which has about 30 fields, not all of them needed for this query.

The SCROD_TABLE is much smaller, it just has the fields named in this query, which are just like their corresponding fields in the REVIEWS_TABLE.
The scrod table was created just for this query. It is so that additional reviews can be added by Mr. Scrod, without having to add all the other info in the original table.

The query needs to pull ALL the reviews from the REVIEWS table, and add in the ones in the scrod table, all into one result..............

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 23:07:06

maybe you need a union operation


select ... from REVIEWS_TABLE where user_id = $user_selected
union all
select ... from SCROD_TABLE where user_id = $user_selected

KH

jeffery1493
Starting Member

8 Posts

Posted - 2007-03-21 : 23:48:21

FANTASTIC*****

Thanks khtan, you're a lifesaver.
You should come over to my website:

www.fish4tips.com

It's fairly new, but we hope to have people like you answering questions for tip money!
Sign up as a Club Member and I'll give you a free month-
(I'll just post this question for $5 bucks and you can have it)
thanks!

JEFFERY1493

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources