Issue with Left Join query - 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)

Issue with Left Join query

Author

Topic

SpeshulK926
Starting Member

16 Posts

Posted - 2007-11-02 : 14:04:05

I am trying to do a crazy left join query and I'm having some issues... First off, is there a better way than all the Left Joins how I have it?

It only returns 1 row, when I know there are more than that. I'm not really sure what info I can give you to allow you to help, so if you need more info, please let me know. Thanks for your help!

table_r has r_id, i_id, b_id, r_timestamp
table_rl is linked to table_r by the r_id
table_rt_i is linked to table_r by the i_id
table_rt_b is linked to table_r by the b_id
table_u.u_id is linked to table_rl.m_id

What info should I be giving the query besides the r_id to make it show me what data I need? Any help or a push in the right direction would be appreciated!

SELECT * FROM table_r AS r 
LEFT JOIN table_rl AS rl ON r.r_id = rl.r_id 
LEFT JOIN table_rt_i AS i ON r.i_id = i.i_id 
LEFT JOIN table_rt_b AS b ON r.b_id = b.b_id 
LEFT JOIN table_u AS u ON rl.m_id = u.u_id 
WHERE r.r_id=1193764660
GROUP BY r.r_timestamp ASC

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 14:18:01

there is nothing in the left joins that would reduce the records in the resultset, so double check there are multiple values in table_r with r_id = 1193764660

as for the left joins if you know records always match in certain table combinations then you can use a derived table. for example if for every record in table_rl there is one or more in table_u

you could do this for possibly better performance...

SELECT *
FROM table_r AS r
(

SELECT
rl.<fields>
,u.<fields>
FROM table_rl AS rl
JOIN table_u AS u ON rl.m_id = u.u_id

) AS rl_u ON r.r_id = rl_u.r_id

LEFT JOIN table_rt_i AS i ON r.i_id = i.i_id
LEFT JOIN table_rt_b AS b ON r.b_id = b.b_id
WHERE r.r_id=1193764660
GROUP BY r.r_timestamp ASC

SpeshulK926
Starting Member

16 Posts

Posted - 2007-11-02 : 14:30:34

I know there are more than 1 records in table_r with an r_id of 1193764660

If it helps any table_u is a user table. So, table_rl only needs to connect to it to find the username associated.

The other tables are just lookup tables for each field. So, as long as there is more than 1 r_id that exists, it should at least come back with 10+ lines of null data if it couldn't find the rest, right?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 14:42:11

joins always create more columns, but only create more rows if there is a more than one match

start with the simplest form of the statement looking at the records then slowing build it up

like so...

SELECT * FROM table_r AS r
WHERE r.r_id=1193764660
ORDER BY r.r_timestamp ASC--assume GROUP was a typo

SELECT * FROM table_r AS r
LEFT JOIN table_rl AS rl ON r.r_id = rl.r_id
WHERE r.r_id=1193764660
ORDER BY r.r_timestamp ASC

SELECT * FROM table_r AS r
LEFT JOIN table_rl AS rl ON r.r_id = rl.r_id
LEFT JOIN table_u AS u ON rl.m_id = u.u_id
WHERE r.r_id=1193764660
ORDER BY r.r_timestamp ASC

SELECT * FROM table_r AS r
LEFT JOIN table_rl AS rl ON r.r_id = rl.r_id
LEFT JOIN table_u AS u ON rl.m_id = u.u_id
LEFT JOIN table_rt_i AS i ON r.i_id = i.i_id
WHERE r.r_id=1193764660
ORDER BY r.r_timestamp ASC

SELECT * FROM table_r AS r
LEFT JOIN table_rl AS rl ON r.r_id = rl.r_id
LEFT JOIN table_u AS u ON rl.m_id = u.u_id
LEFT JOIN table_rt_i AS i ON r.i_id = i.i_id
LEFT JOIN table_rt_b AS b ON r.b_id = b.b_id
WHERE r.r_id=1193764660
ORDER BY r.r_timestamp ASC

SpeshulK926
Starting Member

16 Posts

Posted - 2007-11-02 : 15:43:12

You hit the nail on the head man. The grouping was the problem.

It's sort of pulling the right data now. I just see 3 of each entry... I'm going to do what you said and do sub-selects instead of doing huge left joins, etc..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources