CODEWITHSUNDEEP
sqlvb.netms-accessleft-join
user2076963
user2076963

Reputation: 91

Left join including a where condition not working

I’m going nuts. It seems everyone has had this same problem by the number of results I get when I google ‘left join not working’. I’ve studied them all and despite best efforts I cannot get my specific problem to work. Please help.

I have two tables; an animals_Table and an animalMilestones_Table.

Some of the animals in the animals_Table have intake records in the animalMilestones_Table and some do not. I want to join the two tables on animalID and show all rows in the animals_Table including those that have no matching ‘Intake’ record in the animalMilestones_Table.

Here are the two things I’ve tried that I thought would give the best chance for success but alas, neither work.

SELECT animals_Table.animalID, animalMilestones_Table.milestoneType
FROM animals_Table
LEFT JOIN animalMilestones_Table ON animals_Table.animalID=animalMilestones_Table.animalID
WHERE animalMilestones_Table.milestoneType="Intake"

SELECT animalID
FROM animals_Table
LEFT JOIN (SELECT milestoneType
FROM animalMilestones_Table
WHERE animalMilestones_Table.milestoneType = "Intake")
ON animals_Table.animalID = animalMilestones_Table.animalID

Upvotes: 2

Views: 9848

Answers (7)

Gord Thompson
Gord Thompson

Reputation: 123419

It seems to me that you want a list of all [animalID] values from [animals_Table] along with a column that indicates whether or not that [animalID] has a "milestone" equal to 'Intake'. If so, then I would suggest this:

    SELECT at.animalID, amt.milestoneType
    FROM
        animals_Table at
        INNER JOIN
        animalMilestones_Table amt
            ON at.animalID = amt.animalID
    WHERE amt.milestoneType = 'Intake'
UNION
    SELECT animalID, NULL AS milestoneType
    FROM animals_Table
    WHERE animalID NOT IN (
        SELECT animalID
        FROM animalMilestones_Table
        WHERE milestoneType = 'Intake'
    )
ORDER BY 1

Come to think of it, this should work too, and may prove to be faster:

SELECT at.animalID, amt.milestoneType
FROM
    animals_Table at
    LEFT JOIN
    (
        SELECT animalID, milestoneType
        FROM animalMilestones_Table
        WHERE milestoneType = 'Intake'
    ) amt
        ON at.animalID = amt.animalID

Upvotes: 2

Access Guru
Access Guru

Reputation: 193

try out the following query. hope this may help you and let me know if this helps.. 

SELECT animals_Table.AnimalID, animalMilestones_Table.MileStoneType
FROM animals_Table LEFT JOIN animalMilestones_Table ON animals_Table.AnimalID = animalMilestones_Table.AnimalID
WHERE ((((animalMilestones_Table.MileStoneType)='Intake' Or (animalMilestones_Table.MileStoneType) like '*') Or (animalMilestones_Table.MileStoneType) Is Null)) ;

Upvotes: 0

Lee Willis
Lee Willis

Reputation: 1582

animalMilestones_Table.milestoneType will be NULL in cases where there is no record. You need to check for it being NULL or the value you are querying for.

animalMilestones_Table.milestoneType="Intake" or animalMilestones_Table.milestoneType is null

Not sure on the exact SQL syntax in Accesss

Upvotes: 1

Sid M
Sid M

Reputation: 4354

Try this

SELECT animals_Table.animalID, animalMilestones_Table.milestoneType
FROM animals_Table
LEFT JOIN (SELECT milestoneType
FROM animalMilestones_Table
WHERE milestoneType = "Intake") as animalMilestones_Table
ON animals_Table.animalID = animalMilestones_Table.animalID;

Upvotes: 0

MrMauricioLeite
MrMauricioLeite

Reputation: 383

maybe the code below work for you.

SELECT
   *
FROM
   animals_table at
LEFT JOIN animalmilestones_table amt ON at.animalID = amt.animalID AND amt.milestoneType = "Intake"

Please, try it out and tell me how it goes.

Upvotes: 0

abalos
abalos

Reputation: 1361

Does:

SELECT animals_Table.animalID, animalMilestones_Table.milestoneType
FROM animals_Table
LEFT JOIN animalMilestones_Table ON animals_Table.animalID=animalMilestones_Table.animalID
AND animalMilestones_Table.milestoneType="Intake"

Work?

Upvotes: -1

Ruslan
Ruslan

Reputation: 2797

Not sure if MS Access supports this, but in SQL you would move your condition from the WHERE clause into the LEFT JOIN, like this:

SELECT animals_Table.animalID, animalMilestones_Table.milestoneType
FROM animals_Table
LEFT JOIN animalMilestones_Table ON animals_Table.animalID=animalMilestones_Table.animalID AND animalMilestones_Table.milestoneType="Intake"

Upvotes: 0

Related Questions