Query with join and multiple WHERE conditions not working

[stephena]

I've got a problem with a query that I am putting together. I have two related tables. One containing keywords (Assigned_Subject_Keywords_Table) related to records in the first table (Title_table). I am trying to search for records in the Title_table that may be assigned to multiple subject keywords using an AND operator. Each time I run the query it doesn't return any records. However if I substitute the AND operator for a OR operator it works fine. However this is obviously not what I am after. I need to return only those records assigned to multiple subject keywords.

SELECT DISTINCTROW Title_table.* FROM Title_table LEFT JOIN Assigned_Subject_Keywords_Table ON Title_table.TitleID = Assigned_Subject_Keywords_Table.TitleID WHERE Assigned_Subject_Keywords_Table.SubjectID = 220 AND Assigned_Subject_Keywords_Table.SubjectID = 84

Many thanks in advance for your assistance.

 

[DCrake]

OR is the correct operator to use AND implies it has one or more values in the same record field.

 

[Brianwarnock]

Whilst the OR is correct the approach is wrong, what you need to do is an aggregate query and count the Title_table.TitleID selecting where Count>1

Brian

 

[stephena]

Thanks Gentlemen. I think I conceptually understand what you are talking about. I'm not sure how to put that together into a query. Can you help me out with an example.

Regards
Stephen

 

[stephena]

Hi Just wondering if anyone can help me out with this query. I am unsure how to incorporate the aggregate function into the query. Any assitance would be very much appreciated.

 

[Brianwarnock]

Ok first query call it QryCount

SELECT  Title_table.TitleID Count(*) as CountofTitleID
 FROM Title_table
Group By TitleID 
Having Count(*) >1

Second Query
QryRows

SELECT Title_table.* 
FROM Title_table Inner Join QryCount On Title_Table.TitleID= QryCount.TitleID

Final query the one you run

SELECT QryRows.* FROM QryRows LEFT JOIN Assigned_Subject_Keywords_Table ON QryRows.TitleID = Assigned_Subject_Keywords_Table.TitleID WHERE Assigned_Subject_Keywords_Table.SubjectID = 220 AND Assigned_Subject_Keywords_Table.SubjectID = 84

All of this is air code and I did wonder if it is possible to combine the 2nd and 3rd queries but with no testing decided to keep it simple

Brian

 

[stephena]

Thanks Brian,

With a few twqeaks I managed to get your query to work. However I am still getting the same Result when doing using an AND operator in your third query. It behaves teh same way as my original query where no results are returned using the AND operator. Whereas it behaves as expected using an OR operator. I can't for the lif of me figure out why it behaves in this manner.

I must admit I hadn't considered doing several queries and feeding input from on to another. It's given me a few ideas. I'll post the results if I manage to crack it.

In the meantime I don't suppose you can explain why the AND operator returns no results?

 

[Brianwarnock]

The AND would require that a field in 1 record had 2 values which is impossible, the Or says that the field can have either value which is what you are after, and as I write this I realise that I have not actually answered your problem, I have looked for multiple Titlid which may all have the same keyword!!

Thinks!!

Brian

 

[Brianwarnock]

Remember this is air code.
The first query now selects only titleid s that meet your criteria, but it counts the number of occurences per titleid of each value and only selects those where the titleid has atleast 1 of each value

first query call it QryCount

SELECT  Title_table.TitleID, Sum(iif(Assigned_Subject_Keywords_Table.SubjectID=220,1,0)) as count220, Sum(iif(Assigned_Subject_Keywords_Table.SubjectID=84,1,0)) as count84
FROM Title_table LEFT JOIN Assigned_Subject_Keywords_Table ON QryRows.TitleID = Assigned_Subject_Keywords_Table.TitleID WHERE Assigned_Subject_Keywords_Table.SubjectID = 220 AND Assigned_Subject_Keywords_Table.SubjectID = 84
Group By TitleID
Having  Sum(iif(Assigned_Subject_Keywords_Table.SubjectID=220,1,0)) >0 And Sum(iif(Assigned_Subject_Keywords_Table.SubjectID=84,1,0)) >0

Second Query
QryRows

SELECT Title_table.* 
FROM Title_table Inner Join QryCount On Title_Table.TitleID= qrycount.TitleID

Now this is the final query the one you run

Brian

 

[stephena]

Thanks Brian,

If I had of read DCRake's response properly I would have realised what was wrong with the AND operrator. It makes sense now. Your query works a treat with a couple of minor alterations. See below. I've bolded the changes.

SELECT Title_table.TitleID, Sum(iif(Assigned_Subject_Keywords_Table.SubjectID=220,1,0)) AS count220, Sum(iif(Assigned_Subject_Keywords_Table.SubjectID=84,1,0)) AS count84
FROM Title_table LEFT JOIN Assigned_Subject_Keywords_Table ON Title_table.TitleID = Assigned_Subject_Keywords_Table.TitleID
WHERE Assigned_Subject_Keywords_Table.SubjectID = 220 OR Assigned_Subject_Keywords_Table.SubjectID = 84
GROUP BY Title_table.TitleID
HAVING Sum(iif(Assigned_Subject_Keywords_Table.SubjectID=220,1,0)) >0 And Sum(iif(Assigned_Subject_Keywords_Table.SubjectID=84,1,0)) >0;

Now I've got to see if it will work in amongst the rest of the code that I have written. As this query is part of a form that allows the user to build up complex queries. Which had been working fine until on user told me that they couldn't query using one abject keyword AND another. Anyhow thanks for your help it is greatly appreciated.

 

[Brianwarnock]

LOL

The And I put in which you correctly replaced with an OR was caused by an oversight when copying and pasting to avoid typos with my pathetic typing skills, I feel quite embarrassed.

The Title_Table qualifications should have been there for clarity, guess I'm a bit lazy.

Brian

 

[stephena]

Thanks again for your help Brian.