I have been trying to write a mySQL query to retrieve records from two or more tables. Each table has a field for groupID. I want to be able to select certain fields from each table and return them as a single record.
SELECT affiliation.groupID,affiliation.group_name,church_gate.groups,church_gate.churches,e_matters.website_address,e_matters.website_password FROM affiliation,church_gate,e_matters WHERE affiliation.groupID=church_gate.groupID OR affiliation.groupID=e_matters.groupID OR affiliation.groupID='Clenorcon';
This works as long as there is an entry for that groupID in every table. However, if one of the tables does not have an entry, nothing is returned.
How can I change this script so that it returns the values in the other tables which do have entries and just ignores the on without entries?
Look at the mysql documentation on Left, Right and Outer joins, but to use this successfully one of your tables must always have an entry in it, please tell me one of the tables is mandatory
As an aside if doing equi-joins like the SQL you posted don't use OR's you should really use AND's
SELECT affiliation.groupID,affiliation.group_name,church_gate.groups,church_gate.churches,e_matters.website_address,e_matters.website_password FROM affiliation,church_gate,e_matters WHERE affiliation.groupID=church_gate.groupID AND affiliation.groupID=e_matters.groupID AND affiliation.groupID='Clenorcon';
I have tried this using AND in the query and I have also tried using inner joins. There will always be information in one of the tables. The problem is that if I have 3 tables and there are only entries in 2 of them, mySQL returns an empty set instead of returning the records from the 2 tables which do have entries.
Do the 2 tables you talk about having entries always have records and it is the 3rd table which may or may not have a related record? If that is the case then using Left/Right joins will work, if not then you may have to look at running more than 1 query to get what you expect.
There is only one table which will definitely have entries in it. There can be up to 10 tables queried at the same time. I need it to return the records from tables with entries and ignore the rest.
I have looked at left, right and inner joins. I think these might be the answer but I have only seen examples of them used on two tables. Can they be used on 3 or more tables and if so whats the syntax?
SELECT
FROM table1
LEFT OUTER JOIN table2 ON (table1.ID = table2.ID)
RIGHT OUTER JOIN table3 ON (table1.ID = table3.ID);
Is one example
In this case LEFT OUTER JOIN gets all records from the table specified on the left (table1.ID=table2.ID) therefore all records from table1. RIGHT OUTER JOIN gets all records from the table specified on the right (table1.ID=table3.ID) therefore all records from table3.