Re: Trying to access data from three tables selectively

you may have more luck getting help if you post in the Access newsgroup, rather than the Oracle one...

;-)

Batool wrote:
>
> Please help!!!
>
> I have an access database and am trying to execute an SQL query that
> goes...
>
> SELECT InventoryID, ProductName, (select Category from tblDevicePC
> where tblDevicePC.InventoryID=tblInventory.InventoryID) AS CategoryPC,
> (select Category from tblDevicePeriph where
> tblDevicePeriph.InventoryID=tblInventory.InventoryID) AS
> CategoryPeriph
> FROM tblInventory
> WHERE (((select Category from tblDevicePC where
> tblDevicePC.InventoryID=tblInventory.InventoryID) Or (select Category
> from tblDevicePeriph where
> tblDevicePeriph.InventoryID=tblInventory.InventoryID))<>False);
>
> With the present query I have this output:
>
> InventoryID ProductName CategoryPC CategoryPeriph
>
> 23 Dell Inspiron Desktop
> 24 Vectra Desktop
> 25 Pavilion Desktop
> 26 Dimension Desktop
> 27 Micron Laptop
> 28 Laser Printer Printer
> 29 Deskjet Printer Printer
> 30 Scanner Scanner
> 31 Web Cam Camera
> 32 External Hard Drive Storage
> 33 External CD ROM Storage
> 34 Logitech Camera Camera
> 35 Optical Mouse Other
> 36 10-baseT Hub Network
> 37 100-baseT Hub Network
> 38 100-baseT Hub Network
> 39 Wireless Keyboard Other
> 40 Backpack Storage
> 41 DVD Drive Storage
> 42 Think Pad Laptop
> 43 Vaio Laptop
>
> *********************************************************************
> I would like to have my output in this format where category is
> selected from the relevant table PC or Peripheral based on DevicePC or
> DevicePeriph field in the inventory table
>
> InventoryID ProductName Category
>
> 23 Dell Inspiron Desktop
> 24 Vectra Desktop
> 25 Pavilion Desktop
> 26 Dimension Desktop
> 27 Micron Laptop
> 28 Laser Printer Printer
> 29 Deskjet Printer Printer
> 30 Scanner Scanner
> 31 Web Cam Camera
> 32 External Hard Drive Storage
> 33 External CD ROM Storage
> 34 Logitech Camera Camera
> 35 Optical Mouse Other
> 36 10-baseT Hub Network
> 37 100-baseT Hub Network
> 38 100-baseT Hub Network
> 39 Wireless Keyboard Other
> 40 Backpack Storage
> 41 DVD Drive Storage
> 42 Think Pad Laptop
> 43 Vaio Laptop
>
> ***************************************************************************************
>
> The tables are:
>
> tblInventory - primary key is InventoryID
>
> InventoryID DevicePC DevicePeriph ProductName EquipType
>
> 23 Yes No Dell Inspiron PC
> 24 Yes No Vectra PC
> 25 Yes No Pavilion PC
> 26 Yes No Dimension PC
> 27 Yes No Micron PC
> 28 No Yes Laser Printer Peripheral
> 29 No Yes Deskjet Printer Peripheral
> 30 No Yes Scanner Peripheral
> 31 No Yes Web Cam Peripheral
> 32 No Yes External Hard Drive Peripheral
> 33 No Yes External CD ROM Peripheral
> 34 No Yes Logitech Camera Peripheral
> 35 No Yes Optical Mouse Other
> 36 No Yes 10-baseT Hub Other
> 37 No Yes 100-baseT Hub Other
> 38 No Yes 100-baseT Hub Other
> 39 No Yes Wireless Keyboard Other
> 40 No Yes Backpack Other
> 41 No Yes DVD Drive Peripheral
> 42 Yes No Think Pad PC
> 43 Yes No Vaio PC
>
>
> ########################################################################################
>
> tblDevicePC - Primary key is ID
>
> ID InventoryID Category
>
> 5 23 Desktop
> 6 24 Desktop
> 7 25 Desktop
> 8 26 Desktop
> 9 27 Laptop
> 10 42 Laptop
> 11 43 Laptop
>
> #########################################################################################
>
> tblDevicePeriph - Primary key is ID
>
> ID InventoryID Category
>
> 5 28 Printer
> 6 29 Printer
> 7 30 Scanner
> 8 31 Camera
> 9 32 Storage
> 10 33 Storage
> 11 34 Camera
> 12 35 Other
> 13 36 Network
> 14 37 Network
> 15 38 Network
> 16 39 Other
> 17 40 Storage
> 18 41 Storage
>
> Please help me get to the bottom of it, I can feel that there is
> something small that I am missing but I am not sure what. Thanks in
> advance.
>
> Batool Fatima
Received on Tue Feb 19 2002 - 17:07:50 CET