1

I'm not sure if I was precise enought in my question so I'll explain the situation.

I got 2 tables in which I need to do an left outer join to get the data even where there's no match. I did a query which is working fine except for one discrimination in the where statement:

SELECT TableA.Type, SUM(TableB.HreReelles) AS HreReellesTotales, TableB.NoProjet_Short 
FROM TableA 
LEFT OUTER JOIN TableB ON TableA.IDType = TableB.IDType  
WHERE TableA.Categorie = 'Electrique' 
GROUP BY TableB.NoProjet_Short,TableA.Type

Now I realized I also needed to get only the record for a specific project BUT by keeping the all the "TableA.Type" from the OUTER JOIN even if there is no match in the other table. If I only add the "AND" statement, it removes them from the returned records. I'll post screenshot if necessary ;).

Oh and I'll need to put this in a OleDbCommand when finalized.

Thanks Simon

EDIT: Added picture. I don't need what's in red, but I need the rest including the empty cells (which comes from the LEFT OUTER JOIN).

Expected data

Taryn
  • 242,637
  • 56
  • 362
  • 405
Simon
  • 75
  • 1
  • 1
  • 7

2 Answers2

3

With a left join query, you should filter rows in the "right" table in the on clause of the join.

SELECT TableA.Type, SUM(TableB.HreReelles) AS HreReellesTotales, TableB.NoProjet_Short 
FROM TableA 
LEFT OUTER JOIN TableB ON TableA.IDType = TableB.IDType  
  AND TableB.ColumnName = 'SomeValue'
WHERE TableA.Categorie = 'Electrique' 
GROUP BY TableB.NoProjet_Short,TableA.Type
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
George Mastros
  • 24,112
  • 4
  • 51
  • 59
  • Seems like the "AND" in the OUTER JOIN is not supported by Access database: I'm using an Access 2000 database. I tried both in Access and executed within an OleDbCommand and got the same error: "Join expression not supported." – Simon Mar 20 '13 at 16:58
  • I don't usually answer Access questions, because I know the syntax is different and I am not as familiar with it. Is it just me, or were the access tags added later? – George Mastros Mar 20 '13 at 19:47
  • @G Mastros Now that you are asking I have a bit of doubt in my mind. I did not put the ms-access-2000 tag but I'm pretty sure ms-access was there. But thanks for your help, being able to add the AND statement to a JOIN is not something I was aware of. – Simon Mar 20 '13 at 19:54
1

I think you may need to go the long way around:

SELECT TableA.Type, SUM(b.HreReelles) AS HreReellesTotales, 
       b.NoProjet_Short 
FROM TableA 
LEFT JOIN (SELECT * FROM TableB 
  WHERE TableB.ColumnName = 'SomeValue') b
ON TableA.IDType = b.IDType  
WHERE TableA.Categorie = 'Electrique' 
GROUP BY b.NoProjet_Short,TableA.Type
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Thank you very much it finally worked. I was just trying to apply a join between 2 select but wasn't able to do that. You saved me again @Remou :). – Simon Mar 20 '13 at 18:32
  • Thanks for picking up my slack! – George Mastros Mar 20 '13 at 19:48
  • Well it seems to only work in Access, not with OleDb objects. It says it can't locate TableA. – Simon Mar 20 '13 at 20:22
  • It should work.You mean you are using this in C# or VB.Net? That query has nothing specific to MS Access, just Jet/ACE. Are you sure you have a tableA and you are not working from a copy of the database? Visual Studio defaults to copying to the project folder. – Fionnuala Mar 20 '13 at 20:26
  • Try "Select ID From tableA" to test. – Fionnuala Mar 20 '13 at 20:27
  • 1
    I notice that I had `Tableb.NoProjet_Short`, `SUM(TableB.HreReelles)` and `GROUP BY Tableb.NoProjet_Short`, but TableB is now in a subquery, so that should read `b.NoProjet_Short`, `SUM(TableB.HreReelles)` and `GROUP BY b.NoProjet_Short` but these error would say cannot fond tableB, not tableA and the code would not work in Access. – Fionnuala Mar 21 '13 at 00:17
  • @Remou I'm using VB.Net and Jet 4.0 as provider. – Simon Mar 21 '13 at 12:30
  • Okay, give me a minute and I will test. – Fionnuala Mar 21 '13 at 12:31
  • BTW the ACE engine is free from http://www.microsoft.com/en-us/download/details.aspx?id=13255 and you may prefer to use that with .Net – Fionnuala Mar 21 '13 at 12:33
  • I tested the above with VB.Net and Jet using a stored query, and it runs for me. It really is a very simple, common enough query. – Fionnuala Mar 21 '13 at 13:05
  • @Remou I mofidied a bit my poorly designed database and got a simpler (to me at least) query. However I now miss the SUM of HreReelles because it comes from a third table, and join 3 tables is not something I'm familiar with. About ACE, would I need to install it on the computer where I will deploy the application? – Simon Mar 21 '13 at 14:18
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26647/discussion-between-remou-and-simon) – Fionnuala Mar 21 '13 at 14:24