SQL query not returning all data

[reisve]

Hi all
Can someone help me out with this? I have three tables in a database:

EquipType
"EquipTypeId","EquipTypeDesc"
"1 ","PABX"
"2 ","Servidores"
"3 ","Redes"
"4 ","type 3"

EquipVendor
"EquipTypeId","EquipVendorId","EquipVendorDesc"
"1 ","1 ","Nortel"
"1 ","2 ","Alcatel"
"2 ","3 ","HP"
"2 ","4 ","Compact"
"3 ","5 ","Krone"
"3 ","6 ","BrandRex"
"1 ","7 ","Aascom"
"1 ","8 ","Matra"

EquipModel
"EquipTypeId","EquipVendorId","EquipModelId","EquipModelDesc"
"1 ","1 ","1 ","Meridian1"
"1 ","1 ","2 ","BCM"
"1 ","2 ","3 ","View"
"1 ","2 ","4 ","OmniPCX"
"2 ","3 ","5 ","HP21"
"2 ","3 ","6 ","HP22"
"2 ","4 ","7 ","Proliant 5000"
"2 ","4 ","8 ","Proliant 7000"
"3 ","5 ","9 ","Cat5e da Krone"
"3 ","5 ","10 ","Cat3 da Krone"
"3 ","6 ","11 ","Cat6 "
"3 ","6 ","12 ","Cat7"
"1 ","7 ","13 ","nexpan"
"1 ","1 ","14 ","test"

I need to get ALL recods from the tables in a format something like this :

PABX Nortel Meridian1
PABX Nortel BCM
PABX Aastra nexpan

and so on. I need to get even the empty ones

I have this query:
"SELECT EquipType.EquipTypeId AS TypeId," & _
" EquipType.EquipTypeDesc AS TypeDesc," & _
" EquipVendor.EquipVendorId AS VendorId," & _
" EquipVendor.EquipVendorDesc AS VendorDesc," & _
" EquipModel.EquipModelId AS ModelId," & _
" EquipModel.EquipModelDesc AS ModelDesc" & _
" FROM EquipType, EquipVendor, EquipModel" & _
" WHERE EquipVendor.EquipTypeId = EquipType.EquipTypeId" & _
" AND EquipModel.EquipTypeId = EquipType.EquipTypeId" & _
" AND EquipModel.EquipVendorId = EquipVendor.EquipVendorId" & _
" ORDER BY EquipType.EquipTypeId"

This does not return EquipType EquipTypeId 4.

I have tried the INNER JOIN but but gave me sintax errors.

Can someone get me a query which will give me all records? I'm using this against a Access Database with VB6

Thanks

[si_the_geek]

The format that Access uses for joins is a little unusual, here's how you would do it:

strSQL = "SELECT EquipType.EquipTypeId AS TypeId," & _
         " EquipType.EquipTypeDesc AS TypeDesc," & _
         " EquipVendor.EquipVendorId AS VendorId," & _
         " EquipVendor.EquipVendorDesc AS VendorDesc," & _
         " EquipModel.EquipModelId AS ModelId," & _
         " EquipModel.EquipModelDesc AS ModelDesc" & _
         " FROM (EquipType " & _
         " INNER JOIN EquipVendor ON EquipVendor.EquipTypeId = EquipType.EquipTypeId)" & _
         " INNER JOIN EquipModel ON EquipModel.EquipTypeId = EquipType.EquipTypeId AND EquipModel.EquipVendorId = EquipVendor.EquipVendorId " & _
         " ORDER BY EquipType.EquipTypeId"

In order to get the data for EquipTypeId 4 to be displayed, you will need to change the Inner joins to Left joins.

[reisve]

Thanks for your reply

changed the INNER JOIN for LEFT JOIN as you sugested:

.Source = "SELECT EquipType.EquipTypeId AS TypeId," & _
" EquipType.EquipTypeDesc AS TypeDesc," & _
" EquipVendor.EquipVendorId AS VendorId," & _
" EquipVendor.EquipVendorDesc AS VendorDesc," & _
" EquipModel.EquipModelId AS ModelId," & _
" EquipModel.EquipModelDesc AS ModelDesc" & _
" FROM (EquipType " & _
" LEFT JOIN EquipVendor ON EquipVendor.EquipTypeId = EquipType.EquipTypeId)" & _
" LEFT JOIN EquipModel ON EquipModel.EquipTypeId = EquipType.EquipTypeId AND EquipModel.EquipVendorId = EquipVendor.EquipVendorId " & _
" ORDER BY EquipType.EquipTypeId"

But I'm getting an error saying "Join expression not supported"

Any Ideas?

[brucevde]

LEFT JOIN EquipModel ON EquipModel.EquipTypeId = EquipType.EquipTypeId AND EquipModel.EquipVendorId = EquipVendor.EquipVendorId

The above attempts to join the Model table using fields from two tables, which is not supported.

Change

EquipType.EquipTypeId

from the left join above to

EquipVendor.EquipTypeId

[reisve]

Thaks guys. It works