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

SQL Server Development (2000)

Query Problem

Author

Topic

gsi
Starting Member

4 Posts

Posted - 2003-04-02 : 14:37:38

Below are the view and the query that I have written, any help in fixing the query to get the output as shown will be appreciated.

Code for the view 'education_type':
----------------------------------

select Item_id,Type_id
from Table_1 e
where Type_id in
(select top 1 ev.Type_id
from Table_1 ev left join
Table_2 et on ev.event_type = et.event_type
where et.event_class = 'education'
and e.Item_id = ev.Item_id
and ev.Type_id is not null
order by ev.start_time)

The above view gets the earliest type_id, when the event_type='education' and
the type_id is not null,along with the item_id

Sample output from the view
----------------------------
Item_id Type_id

It0001 T001
IT0002 T004
It0005 T003

Here is what I need to achieve:

For the item_id that is being evaluated below I need to extract the type_id
and search the Item_type table to get further details. Now, there may be some
item_id for which there will be no records returned from the view.
Obviously, giving the AND condition as stated below does not return those records.
What I need is even if the Item_Id is not present in the view result the mail query should just
return blank information for the Description, Cost and Dimension as shown for
IT0003 & IT0004.

Final Output that is desired
----------------------------

Item_id Type_desc Type_Dim Cost

IT0001 Description1 Dim1 $100
IT0002 Description2 Dim2 $50
IT0003
IT0004
IT0005 Description5 Dim5 $75

Query that I am trying to run

SELECT A.Item_id as 'Item',B.Order_Date As 'Order Date',
C.Desc as Description,C.Cost,C.Dim as Dimension

FROM Item A, Item_Det B, Item_Type C,education_type ET

WHERE A.Invoiced_Date='4/02/2003'
AND A.Item_id =B.Item_id
AND C.Type_Id=ET.Type_Id=ET AND B.Item_id=ET.Item_id
ORDER BY A.Item_id

Thanks in advance
gsi

Bambola
Posting Yak Master

103 Posts

Posted - 2003-04-02 : 15:14:55

you are using INNER JOIN

SELECT A.Item_id as 'Item',B.Order_Date As 'Order Date',
C.[Desc] as [Description],C.Cost,C.Dim as Dimension
FROM Item A
inner join Item_Det B on A.Item_id = B.Item_id
inner join education_type ET on B.Item_id = ET.Item_id
inner join Item_Type C on C.Type_Id= ET.Type_Id
WHERE A.Invoiced_Date='4/02/2003'
ORDER BY A.Item_id

so you get only the rows exist in both tables. change it to outer join and it should give you the results you need.

Bambola.

gsi
Starting Member

4 Posts

Posted - 2003-04-02 : 15:54:44

Thanks Bambola, it works now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources