CODEWITHSUNDEEP
sqlms-access-2010
Micael Florêncio
Micael Florêncio

Reputation: 199

Catch in query column's empty cell's rows

First of all I apologize for the confusing title, I don't know how to put it exactly.

I'm making a query, on Access 2010, to get data from 2 tables, one with normal data of an equipment and another with images (OLE objects). Marca_ (brand) and Modelo (model) are the fields I'm using to associate the equipment to the respective image.

Resumed the tables are like this:

Equipment one:

ID   Name      Marca_   Modelo  

1        Equip1   Brand1    Model1
2        Equip2   Brand2    Model2
3        Equip1   Brand3    Model3
4        Equip2   Brand4    Model4

Image one:

Image    Marca_   Modelo  

Image1   Brand1    Model1
Image2   Brand3    Model3

SELECT  [Tabela Equipamentos ULSM Geral].*, 
        [Fotografias e Manuais de Equipamentos2].FOTO
FROM [Tabela Equipamentos ULSM Geral] 
INNER JOIN [Fotografias e Manuais de Equipamentos2] 
    ON ([Tabela Equipamentos ULSM Geral].MODELO = [Fotografias e Manuais de Equipamentos2].MODELO) 
    AND ([Tabela Equipamentos ULSM Geral].Marca_ = [Fotografias e Manuais de Equipamentos2].MARCA);

What I get is something like this:

ID   Name      Marca_   Modelo  Image

1        Equip1   Brand1    Model1  Image1
2        Equip3   Brand3    Model3  Image3

Issue is some there aren't the pictures of some of the equipment, and that query only gives me the equipment with images associated to it.

So my question is, how can I also get the rows of the equipment that doesn't have anything to connect with on the images table? This is what I want:

ID   Name      Marca_   Modelo   Image

1        Equip1   Brand1    Model1   Image1
2        Equip2   Brand2    Model2
3        Equip1   Brand3    Model3   Image3
4        Equip2   Brand4    Model4

Upvotes: 0

Views: 48

Answers (1)

Lamak
Lamak

Reputation: 70648

Use a LEFT JOIN instead of an INNER JOIN. On another note, you should always apend the schema of your tables. I also recommend that you use table aliases on your queries. Oh, and lastly, try to explicitely add your columns on the SELECT instead of using *.

SELECT  T.ID, 
        T.Name,
        T.[Marca_],
        T.Modelo,
        F.[Image]
FROM dbo.[Tabela Equipamentos ULSM Geral] T -- use the right schema
LEFT JOIN dbo.[Fotografias e Manuais de Equipamentos2] F
    ON T.MODELO = F.MODELO
    AND T.Marca_ = F.MARCA;

Upvotes: 3

Related Questions