Sql Query Question

[angelica]

Hi,

I have 3 tables from which I need to access this information. Tables are: HARDWARE & EQUIPMENT_INFO & LEGER:

Hardware Fields are:

Serial No
Equip_Code
SchoolID
Schooltype
Make
Model

Equipment_Info Fields are:

Equip_Name
Equip_Code

LEGER Fields are:

File No
Serial no
Supplier
Value

The user will have a listbox that displays the Equip_name.On clicking the selection I need to make a connection query on the Access Database.


I need to
SELECT * FROM HARDWARE WHERE SCHOOLTYPE = P AND WHERE LISTBOX1.selection = ((INNER join HARDWARE AND EQUIP_INFO WHERE Serial No: S.No) and (INNER join LEGER AND EQUIPMENT_INFO WHERE Inventory_no = Equip_Code))

Never done a detailed query. Can someone tell me If I have the right code for the query. Thanks for your help

[pvbangera]

Which Is The Foreign Key Of Leger Table? Could You Please Give Me Relationship Details Of These Three Tables?

[pvbangera]

I think this could help

strSQL = "SELECT Hardware.*, Equipment_Info.*, Leger.* from Hardware, Equipment_Info, Leger where Equipment_Info.Equip_Code = Hardware.Equip_Code and Leger.[Serial No] = Hardware.[Serial No] and SCHOOLTYPE = 'P'"

Regards

[angelica]

Hi pvbangera ,

Thanks very much for comng in to help me. Guess rather stupid of me not to include the foreign keys and primary keys. Maybe this will change the query you had sent me. one other thing does the Table.Field in the SQL can be used as code in the VB.NET sqlstring.Then I need to make the Listbox.Selected.Item to the query

Only just starting doing ADO.NET so dont mind me if its an elementary question. Thanks once again for helping me.

The foreign and primary keys are these:

Hardware foreign key is SerialNo (I also have Equip_Code field which is a foreign key here, but I dont know whether this is redundant since it is repeated in the Leger Table

Leger primary key is SerialNo and foreign key is Equip_Code

Equip_Info has Equip_Code as a primary key.