SOQL query- for querying two object tables

Question

I have used the following query in my custom controller, but apex compiler is not allowing this. How can I recreate this query?

Error: Compile Error: expecting a colon, found EventSchedule__c.Name

Integer capacity= [
    SELECT EventSchedule__c.Capacity__c
    FROM EventSchedule__c, Ticket__c
    WHERE Ticket__c.EventSchedule__c = EventSchedule__c.Name
    AND Ticket__c.Date_Time__c=:listStrings.get(i)
    AND Ticket__c.Events__c=:listStrings2.get(j)
];

Answer 1

Between objects EventSchedule__c and Ticket__c needs a Lookup relationship or Master-Detail relationship,as Ticket__c.EventSchedule__c(Lookup field) = EventSchedule__c.Id.
Now you can run apex like the following code:

List<Ticket__c> tickets = [
  SELECT EventSchedule__r.Capacity__c
  FROM Ticket__c
  WHERE Ticket__c.Date_Time__c=:listStrings.get(i)
  AND Ticket__c.Events__c=:listStrings2.get(j)
  LIMIT 1
];
if(!tickets.isEmpty()) {
    Integer capacity = (Integer)tickets[0].EventSchedule__r.Capacity__c;
}

Answer 2

SOQL fieldExpression Syntax and SOSL WHERE conditionExpression terms can only have this form:

fieldName comparisonOperator value

and value has to be:

A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.

This means you cannot compare two fields: you can only compare a field with a value that is a constant or a bound external value (using the :).

So you will have to change your approach and inject the name via an Apex bind variable.