VB & Database, PRB: Left join behaves like a inner join.

Loui
#1 / 10

PRB: Left join behaves like a inner join.

I am trying to retrieve information coming from two tables in Access.
The complexity comes from the result.
I must always have the records from the left table even if the data is not
present in the right table.

At this point a left join will work.
But the problem occurs when I add a field in my where statement.

This is a example.
I have two tables:
One for the property description and the other with the name of the
property.
This structure is needed for the multi-language issue in my app.

SELECT Properties_Desc.*, Properties_Name.*
FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
Properties_Name.PropDescID
WHERE (((Properties_Name.Language)=1));

The Where clause makes the join behave like a inner join.

I have tried a different left join with a second criteria but Access
doesn't seem to support it.

The only workaround I have found, is to change my query like this:

SELECT Properties_Desc.*, (Select Name from Properties_Name where
Properties_Name.Language)=1) as Name
FROM Properties_Desc;

This method is surely slower, but I did not test it because I am missing
some very important piece in my query.
I must be able to order the query by the Name. Access won't let me make the
Order by clause with the name, or at least I wasn't able too. The only way I
was close to getting something to work is to put back to left join. This way
I had the ordering working but the performance was slow.

The SQL Server profiler gave me a 18 mseconds for the second join parameter
and my access monster took 70mseconds.

Is there any way to fix my problem?
Change the query?
Some modification on the tables?
I can't just change to SQL Server. 8(

P.S.
I am using VB 6 ,ado 1.5 and a access database.

Sat, 11 Aug 2001 03:00:00 GMT

#2 / 10

PRB: Left join behaves like a inner join.

Fri, 19 Jun 1992 00:00:00 GMT

Peter Mund
#3 / 10

PRB: Left join behaves like a inner join.

Your WHERE clause refers to Properties_Name.Language which will be Null if
there is no Properties_Name row for a particular Properties_Desc row.

Peter Mundy

Quote:

>I am trying to retrieve information coming from two tables in Access.
>The complexity comes from the result.
>I must always have the records from the left table even if the data is not
>present in the right table.

>At this point a left join will work.
>But the problem occurs when I add a field in my where statement.

>This is a example.
>I have two tables:
>One for the property description and the other with the name of the
>property.
>This structure is needed for the multi-language issue in my app.

>SELECT Properties_Desc.*, Properties_Name.*
>FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
>Properties_Name.PropDescID
>WHERE (((Properties_Name.Language)=1));

>The Where clause makes the join behave like a inner join.

>I have tried a different left join with a second criteria but Access
>doesn't seem to support it.

>The only workaround I have found, is to change my query like this:

>SELECT Properties_Desc.*, (Select Name from Properties_Name where
>Properties_Name.Language)=1) as Name
>FROM Properties_Desc;

>This method is surely slower, but I did not test it because I am missing
>some very important piece in my query.
>I must be able to order the query by the Name. Access won't let me make the
>Order by clause with the name, or at least I wasn't able too. The only way
I
>was close to getting something to work is to put back to left join. This
way
>I had the ordering working but the performance was slow.

>The SQL Server profiler gave me a 18 mseconds for the second join parameter
>and my access monster took 70mseconds.

>Is there any way to fix my problem?
>Change the query?
>Some modification on the tables?
>I can't just change to SQL Server. 8(

>P.S.
>I am using VB 6 ,ado 1.5 and a access database.

Sat, 11 Aug 2001 03:00:00 GMT

Guido Lederman
#4 / 10

PRB: Left join behaves like a inner join.

Try

SELECT Properties_Desc.*, Properties_Name.*
FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
Properties_Name.PropDescID
WHERE Properties_Name.Language=1 or Properties_Name.Language=Null;

--
Guido Ledermann
BGT Bretten

Sun, 12 Aug 2001 03:00:00 GMT

Loui
#5 / 10

PRB: Left join behaves like a inner join.

Thank you Peter for replying.

Unfortunately, your answer doesn't really solve my problem or at least it
solve only half my problem.
I will try to explain better.
If anybody can help I would be very grateful.

I need to get all the records from the left table even if there is a record
on the right table with the wrong language.

For example the table would have this:

Properties_Desc:

ID KEY
1 PropKey1
2 PropKey2
3 PropKey3

Properties_Name
PropDescID Language Name
1 0 Key1Language0
1 1 Key1Language1
2 0 Key2Language0

My query needs to return all the Property Description(Properties_Desc) with
there names if they exist.

For Language = 1
It would return this:

PropKey1 Key1Language1
PropKey2 Null
PropKey3 Null

By adding a extra where clause like this:

SELECT Properties_Desc.*, Properties_Name.*
FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
Properties_Name.PropDescID
WHERE Properties_Name.Language) = 1 OR Properties_Name.Language) = NULL

This will only give me the 3rd row but not the second one.

I hope this example makes it more clear.

Thanks for you help.

Quote:

>Your WHERE clause refers to Properties_Name.Language which will be Null if
>there is no Properties_Name row for a particular Properties_Desc row.

>Peter Mundy

>>I am trying to retrieve information coming from two tables in Access.
>>The complexity comes from the result.
>>I must always have the records from the left table even if the data is not
>>present in the right table.

>>At this point a left join will work.
>>But the problem occurs when I add a field in my where statement.

>>This is a example.
>>I have two tables:
>>One for the property description and the other with the name of the
>>property.
>>This structure is needed for the multi-language issue in my app.

>>SELECT Properties_Desc.*, Properties_Name.*
>>FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
>>Properties_Name.PropDescID
>>WHERE (((Properties_Name.Language)=1));

>>The Where clause makes the join behave like a inner join.

>>I have tried a different left join with a second criteria but Access
>>doesn't seem to support it.

>>The only workaround I have found, is to change my query like this:

>>SELECT Properties_Desc.*, (Select Name from Properties_Name where
>>Properties_Name.Language)=1) as Name
>>FROM Properties_Desc;

>>This method is surely slower, but I did not test it because I am missing
>>some very important piece in my query.
>>I must be able to order the query by the Name. Access won't let me make
the
>>Order by clause with the name, or at least I wasn't able too. The only way
>I
>>was close to getting something to work is to put back to left join. This
>way
>>I had the ordering working but the performance was slow.

>>The SQL Server profiler gave me a 18 mseconds for the second join
parameter
>>and my access monster took 70mseconds.

>>Is there any way to fix my problem?
>>Change the query?
>>Some modification on the tables?
>>I can't just change to SQL Server. 8(

>>P.S.
>>I am using VB 6 ,ado 1.5 and a access database.

Sun, 12 Aug 2001 03:00:00 GMT

Matthew Will
#6 / 10

PRB: Left join behaves like a inner join.

Guido,

The correct ANSI syntax would be:

SELECT Properties_Desc.*, Properties_Name.*
FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
Properties_Name.PropDescID
WHERE Properties_Name.Language=1 or Properties_Name.Language IS Null

Matthew

Quote:

> Try

> SELECT Properties_Desc.*, Properties_Name.*
> FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
> Properties_Name.PropDescID
> WHERE Properties_Name.Language=1 or Properties_Name.Language=Null;

> --
> Guido Ledermann
> BGT Bretten

Sun, 12 Aug 2001 03:00:00 GMT

John Viesca
#7 / 10

PRB: Left join behaves like a inner join.

Louis-

In Access 97 and earlier, you have to do this with a query on a query:

qryPropertiesName1:
SELECT Properties_Name.*
FROM Properties_Name
WHERE Properties_Name.Language = 1

qrySolution:
SELECT Properties_Desc.*, Properties_Name.*
FROM Properties_Desc LEFT JOIN qryPropertiesName1 ON Properties_Desc.ID =
Properties_Name.PropDescID;

In Access 2000, you'll be able to do it with one query:

SELECT Properties_Desc.*, Properties_Name.*
FROM Properties_Desc LEFT JOIN
(SELECT Properties_Name.* FROM Properties_Name WHERE
Properties_Name.Language = 1) AS Properties_Name
ON Properties_Desc.ID =
Properties_Name.PropDescID;

--
John Viescas
author, "Running Microsoft Access 97"
http://www.amazon.com/exec/obidos/ISBN=1572313234/

Quote:

>Thank you Peter for replying.

>Unfortunately, your answer doesn't really solve my problem or at least it
>solve only half my problem.
>I will try to explain better.
>If anybody can help I would be very grateful.

>I need to get all the records from the left table even if there is a record
>on the right table with the wrong language.

>For example the table would have this:

>Properties_Desc:

>ID KEY
>1 PropKey1
>2 PropKey2
>3 PropKey3

>Properties_Name
>PropDescID Language Name
>1 0 Key1Language0
>1 1 Key1Language1
>2 0 Key2Language0

>My query needs to return all the Property Description(Properties_Desc) with
>there names if they exist.

>For Language = 1
>It would return this:

> PropKey1 Key1Language1
> PropKey2 Null
> PropKey3 Null

>By adding a extra where clause like this:

>SELECT Properties_Desc.*, Properties_Name.*
>FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
>Properties_Name.PropDescID
>WHERE Properties_Name.Language) = 1 OR Properties_Name.Language) = NULL

>This will only give me the 3rd row but not the second one.

>I hope this example makes it more clear.

>Thanks for you help.

>>Your WHERE clause refers to Properties_Name.Language which will be Null if
>>there is no Properties_Name row for a particular Properties_Desc row.

>>Peter Mundy

>>>I am trying to retrieve information coming from two tables in Access.
>>>The complexity comes from the result.
>>>I must always have the records from the left table even if the data is
not
>>>present in the right table.

>>>At this point a left join will work.
>>>But the problem occurs when I add a field in my where statement.

>>>This is a example.
>>>I have two tables:
>>>One for the property description and the other with the name of the
>>>property.
>>>This structure is needed for the multi-language issue in my app.

>>>SELECT Properties_Desc.*, Properties_Name.*
>>>FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
>>>Properties_Name.PropDescID
>>>WHERE (((Properties_Name.Language)=1));

>>>The Where clause makes the join behave like a inner join.

>>>I have tried a different left join with a second criteria but Access
>>>doesn't seem to support it.

>>>The only workaround I have found, is to change my query like this:

>>>SELECT Properties_Desc.*, (Select Name from Properties_Name where
>>>Properties_Name.Language)=1) as Name
>>>FROM Properties_Desc;

>>>This method is surely slower, but I did not test it because I am missing
>>>some very important piece in my query.
>>>I must be able to order the query by the Name. Access won't let me make
>the
>>>Order by clause with the name, or at least I wasn't able too. The only
way
>>I
>>>was close to getting something to work is to put back to left join. This
>>way
>>>I had the ordering working but the performance was slow.

>>>The SQL Server profiler gave me a 18 mseconds for the second join
>parameter
>>>and my access monster took 70mseconds.

>>>Is there any way to fix my problem?
>>>Change the query?
>>>Some modification on the tables?
>>>I can't just change to SQL Server. 8(

>>>P.S.
>>>I am using VB 6 ,ado 1.5 and a access database.

Sun, 12 Aug 2001 03:00:00 GMT

Guido Lederman
#8 / 10

PRB: Left join behaves like a inner join.

Hi Matthew,

you're right... IS Null is of course the right term.

Guido

Tue, 14 Aug 2001 03:00:00 GMT

Loui
#9 / 10

PRB: Left join behaves like a inner join.

Thank you John for the help.
Your answer is clear and simple.
I don't know why I didn't think of trying a query on a query.
The only thing that I am not certain of is how to make a query that calls a
other query with a parameter.
For example:

My sub query Prop_NameByLanguage will be:

SELECT Properties_Name.*
FROM Properties_Name
WHERE Properties_Name.Language=[Lang];

But how do I call this query in my new SQL query?

SELECT Properties_Desc.*, Prop_NameByLanguage.*
FROM Properties_Desc LEFT JOIN Prop_NameByLanguage ON Properties_Desc.ID =
Prop_NameByLanguage.PropDescID
Where Lang = 1

This query doesn't work!
You cannot put the parameter in the where clause.

Need one last little help,

So what do you purpose John? or anybody?

Thanks

Louis

Quote:

>Louis-

>In Access 97 and earlier, you have to do this with a query on a query:

>qryPropertiesName1:
>SELECT Properties_Name.*
>FROM Properties_Name
>WHERE Properties_Name.Language = 1

>qrySolution:
>SELECT Properties_Desc.*, Properties_Name.*
>FROM Properties_Desc LEFT JOIN qryPropertiesName1 ON Properties_Desc.ID =
>Properties_Name.PropDescID;

>In Access 2000, you'll be able to do it with one query:

>SELECT Properties_Desc.*, Properties_Name.*
>FROM Properties_Desc LEFT JOIN
>(SELECT Properties_Name.* FROM Properties_Name WHERE
>Properties_Name.Language = 1) AS Properties_Name
>ON Properties_Desc.ID =
>Properties_Name.PropDescID;

>--
>John Viescas
>author, "Running Microsoft Access 97"
>http://www.amazon.com/exec/obidos/ISBN=1572313234/

>>Thank you Peter for replying.

>>Unfortunately, your answer doesn't really solve my problem or at least it
>>solve only half my problem.
>>I will try to explain better.
>>If anybody can help I would be very grateful.

>>I need to get all the records from the left table even if there is a
record
>>on the right table with the wrong language.

>>For example the table would have this:

>>Properties_Desc:

>>ID KEY
>>1 PropKey1
>>2 PropKey2
>>3 PropKey3

>>Properties_Name
>>PropDescID Language Name
>>1 0 Key1Language0
>>1 1 Key1Language1
>>2 0 Key2Language0

>>My query needs to return all the Property Description(Properties_Desc)
with
>>there names if they exist.

>>For Language = 1
>>It would return this:

>> PropKey1 Key1Language1
>> PropKey2 Null
>> PropKey3 Null

>>By adding a extra where clause like this:

>>SELECT Properties_Desc.*, Properties_Name.*
>>FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
>>Properties_Name.PropDescID
>>WHERE Properties_Name.Language) = 1 OR Properties_Name.Language) = NULL

>>This will only give me the 3rd row but not the second one.

>>I hope this example makes it more clear.

>>Thanks for you help.

Tue, 14 Aug 2001 03:00:00 GMT

John Viesca
#10 / 10

PRB: Left join behaves like a inner join.

Louis-

Take the

WHERE Lang = 1

out of your outer query. Access will prompt you for the value of Lang when
you run the outer query.

--
John Viescas
author, "Running Microsoft Access 97"
http://www.amazon.com/exec/obidos/ISBN=1572313234/

Quote:

>Thank you John for the help.
>Your answer is clear and simple.
>I don't know why I didn't think of trying a query on a query.
>The only thing that I am not certain of is how to make a query that calls a
>other query with a parameter.
>For example:

>My sub query Prop_NameByLanguage will be:

>SELECT Properties_Name.*
>FROM Properties_Name
>WHERE Properties_Name.Language=[Lang];

>But how do I call this query in my new SQL query?

>SELECT Properties_Desc.*, Prop_NameByLanguage.*
>FROM Properties_Desc LEFT JOIN Prop_NameByLanguage ON Properties_Desc.ID =
>Prop_NameByLanguage.PropDescID
>Where Lang = 1

>This query doesn't work!
>You cannot put the parameter in the where clause.

>Need one last little help,

>So what do you purpose John? or anybody?

>Thanks

>Louis

>>Louis-

>>In Access 97 and earlier, you have to do this with a query on a query:

>>qryPropertiesName1:
>>SELECT Properties_Name.*
>>FROM Properties_Name
>>WHERE Properties_Name.Language = 1

>>qrySolution:
>>SELECT Properties_Desc.*, Properties_Name.*
>>FROM Properties_Desc LEFT JOIN qryPropertiesName1 ON Properties_Desc.ID =
>>Properties_Name.PropDescID;

>>In Access 2000, you'll be able to do it with one query:

>>SELECT Properties_Desc.*, Properties_Name.*
>>FROM Properties_Desc LEFT JOIN
>>(SELECT Properties_Name.* FROM Properties_Name WHERE
>>Properties_Name.Language = 1) AS Properties_Name
>>ON Properties_Desc.ID =
>>Properties_Name.PropDescID;

>>--
>>John Viescas
>>author, "Running Microsoft Access 97"
>>http://www.amazon.com/exec/obidos/ISBN=1572313234/

>>>Thank you Peter for replying.

>>>Unfortunately, your answer doesn't really solve my problem or at least it
>>>solve only half my problem.
>>>I will try to explain better.
>>>If anybody can help I would be very grateful.

>>>I need to get all the records from the left table even if there is a
>record
>>>on the right table with the wrong language.

>>>For example the table would have this:

>>>Properties_Desc:

>>>ID KEY
>>>1 PropKey1
>>>2 PropKey2
>>>3 PropKey3

>>>Properties_Name
>>>PropDescID Language Name
>>>1 0 Key1Language0
>>>1 1 Key1Language1
>>>2 0 Key2Language0

>>>My query needs to return all the Property Description(Properties_Desc)
>with
>>>there names if they exist.

>>>For Language = 1
>>>It would return this:

>>> PropKey1 Key1Language1
>>> PropKey2 Null
>>> PropKey3 Null

>>>By adding a extra where clause like this:

>>>SELECT Properties_Desc.*, Properties_Name.*
>>>FROM Properties_Desc LEFT JOIN Properties_Name ON Properties_Desc.ID =
>>>Properties_Name.PropDescID
>>>WHERE Properties_Name.Language) = 1 OR Properties_Name.Language) = NULL

>>>This will only give me the 3rd row but not the second one.

>>>I hope this example makes it more clear.

>>>Thanks for you help.

Tue, 14 Aug 2001 03:00:00 GMT