Virtual tables and outer joins

[sarahw]

I had a problem with some code last week, which Mike Lacey resolved for me - very kind. Unfortunately, this doesn't seem to be an end to my problem. I now need to display all units irrespective of whether there is an entry for make or drivesize. Does anyone know of a way to do this? This is part of my code as it stands at the moment.

select distinct u.area,u.unitid,u.name,lrundate,tbl_A.Make,tbl_X.Drivesize
from unit u,
(select distinct u.unitid,itval as Make
from unit u right outer join infotxt txt on u.unitid=txt.unitid
where itnameid = '2026')tbl_A,
(select u.unitid,sum(cast(dbl.itval as numeric))as DriveSize from unit u
left outer join infodbl dbl on u.unitid=dbl.unitid
inner join infoname inf on dbl.itnameid = inf.itnameid
where type = 1
and inf.itname like 'drive [a-z]: size%'
and u.area like '%ntserver%'
group by u.unitid)tbl_X
where u.type = '1'
and u.area like '%ntserver%'
and u.unitid = tbl_A.unitid
and u.unitid = tbl_X.unitid
order by u.unitid,tbl_A.Make

If someone could give me a clue it may prevent a nervous breakdown.

Thankyou in advance
Sarah

 

[THoey]

I was trying to help someone work something like this earlier in the week, but got called away from it. He ended up getting it to work using a UNION statement. I am kind of vague on those, but I thought if I mentioned it, it might trigger something in someone else's mind...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[MikeLacey]

what database is it sarah?
Mike
michael.j.lacey@ntlworld.com

 

[sarahw]

SQL Server 2000. I know that Oracle is more your thing, but I live in hope.

Sarah

 

[sarahw]

My problem seems to be more fundamental than I first thought - cannot create outer join even when not using virtual tables.

There are about 95 servers in the units table. I would expect to be able to see 95 entries in the returned recordset with null values in the itval column. Is it something to do with the requirement to specify which data type I'm interested in (inf.itnameid)in the where clause?

This is the test code that I am using.

select name,u.unitid,inf.itval from unit as u right outer join infotxt as inf on (u.unitid=inf.unitid)
where u.area like '%ntserver%'
and inf.itnameid ='2025'

Please could someone let me know if I am missing the point completely - grey hair on young women is not attractive

 

[MalcolmW]

SELECT name,u.unitid,inf.itval
FROM unit as u
RIGHT OUTER JOIN infotxt as inf on (u.unitid=inf.unitid)
WHERE u.area like '%ntserver%'
AND inf.itnameid ='2025'
ok - with a RIGHT outer join, that will preserve rows from inf even if there are no corresponding rows in u. The values for u.unitid would be null in such cases.
Except that your where clause filters for
u.area like '%ntserver%'
which makes your outer join (in effect) an inner join. Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[sarahw]

Malcolm

Thanks for responding - I now understand that I cannot return the results required using the above code. However, I do not know a different way to resolve the problem. Is there another way to achieve my goal - which is to output all units with corresponding null values for Make,Model,etc when this data is not present?

Any pointers/tips are greatfully received.

Thanks
Sarah

 

[sarahw]

Thanks for listening - I've sorted out my problem using views.

Sarah