INNER to OUTER 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

General SQL Server Forums

New to SQL Server Programming

INNER to OUTER JOIN

Author

Topic

Mtop
Starting Member

8 Posts

Posted - 2009-06-18 : 06:46:13

Hi SQL fans,
I am a relative newbie to sql and have the follwo=ing question

We run a script that selects data using INNER joins
SELECT ...
FROM T1429 INNER JOIN T228 ON T1429.C179 = T228.c490009000
INNER JOIN T1425 ON T228.c490008000 = T1425.C179
INNER JOIN T228 IPl ON T1429.C179 = IPl.c490008000
INNER JOIN T1431 ON IPl.c490009000 = T1431.C179
WHERE T228.C754000012 = '1'
AND T228.C754000013 = '1'
ORDER BY T1429.C200000020
This obviously ony gives us records that have links

I was hoping to get all records from T1429 by changing the first INNER to a LEFT OUTER
SELECT ...
FROM T1429 LEFT JOIN T228 ON T1429.C179 = T228.c490009000
INNER JOIN T1425 ON T228.c490008000 = T1425.C179
INNER JOIN T228 IPl ON T1429.C179 = IPl.c490008000
INNER JOIN T1431 ON IPl.c490009000 = T1431.C179
WHERE T228.C754000012 = '1'
AND T228.C754000013 = '1'
ORDER BY T1429.C200000020

but I get the same record count in both

Mtop

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 08:12:40

Need to move the where logic into the JOIN


FROM T1429
    LEFT JOIN T228 ON
        T1429.C179 = T228.c490009000
        AND T228.C754000012 = '1'
        AND T228.C754000013 = '1'
INNER JOIN T1425 ON T228.c490008000 = T1425.C179	
INNER JOIN T228 IPl ON T1429.C179 = IPl.c490008000
INNER JOIN T1431 ON IPl.c490009000 = T1431.C179
ORDER BY T1429.C200000020

Otherwise the where condition eliminates the rows that have no relation in T228

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Mtop
Starting Member

8 Posts

Posted - 2009-06-19 : 16:06:10

I knew I'd come to the right place - that worked a treat - many thanks

Mtop

Mtop
Starting Member

8 Posts

Posted - 2009-06-25 : 05:00:08

I have a very similar query but I'm not getting all rows from the first table - only rows where there is a link ....
The data I want all rows from is T1429, the linked data is in table T1433 but I have to go via table T229 to get the data.
my query is
SELECT .......
FROM T1429
LEFT OUTER JOIN T229
ON T1429.C179 = T229.c490008000
AND T229.C754000012 = '1'
AND T229.C754000013 = '1'
INNER JOIN T1433
ON T229.c490009000 = T1433.C179

but I only get 518 rows when there are 2,000 plus in table T1429

Any assitance is greatly appeciated

Mtop

Mtop
Starting Member

8 Posts

Posted - 2009-06-25 : 05:11:36

Do I "reverse" the order of the T1429 / T1433 tables and then OUTER JOIN the T1429 table to get data?

Mtop

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-06-25 : 06:04:09

[code]SELECT *
FROM T229
JOIN T1433
ON T229.c490009000 = T1433.C179
AND T229.C754000012 = '1'
AND T229.C754000013 = '1'
RIGHT JOIN T1429
ON T1429.C179 = T229.c490008000[/code]

or if you object to right joins:

[code]SELECT *
FROM T1429
LEFT JOIN
( -- brakets not necessary but recommended so query is easier to read
T229
JOIN T1433
ON T229.c490009000 = T1433.C179
AND T229.C754000012 = '1'
AND T229.C754000013 = '1'
) -- brakets not necessary but recommended so query is easier to read
-- order of ON clause is the key syntax
ON T1429.C179 = T229.c490008000[/code]

Mtop
Starting Member

8 Posts

Posted - 2009-06-25 : 08:00:00

Many thanks Ifor,
I'm almost there except that I am getting too many records out of T1429 now!

If I run

select count(*)
from T1429
where T1429.C536873947 = 'Activated'
and T1429.C400127400 = 'ASSET'

I get a record count of 5502

I obviously need the T1429.C179 = T229.c490008000 to get the records from T229 but
when I run

RIGHT JOIN T1429
on T1429.C179 = T229.c490008000
and T1429.C536873947 = 'Activated'
and T1429.C400127400 = 'ASSET'

I end up with 6276 records - 765 which do not meet the

and T1429.C536873947 = 'Activated'
and T1429.C400127400 = 'ASSET'

conditions.

I have moved the three conditions around so that each of them has been the first ON condition but I still end up with 6267 records.

Any ideas?

Mtop

Mtop
Starting Member

8 Posts

Posted - 2009-06-25 : 08:35:54

Ah ha,
I've moved it around a bit and now have
RIGHT JOIN T1429
on T1429.C179 = T229.c490008000
Where T1429.C536873947 = 'Activated'
and T1429.C400127400 = 'ASSET'

which gives me a more reasonable 5524 records

I've also noticed that the date held in the database is a numeral but is displayed in the application as dd/mm/yyyy hh:mm:ss - can I perform a conversion for the view I am creating or does that have to be performed in an exported csv / xls?
best regards

Mtop

Mtop
Starting Member

8 Posts

Posted - 2009-06-29 : 06:04:27

Checked the existing functions in the db and some nice person had already created a convert so used that !!!!

to_char(SECONDS_TO_DATE(T1425.C260000104), 'dd/mm/yyyy hh24:mi:ss') "OS - Installation Date",

Mtop

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources