I have the following tables i want to pull information from

Members - MemID, MemFName, MemLastName
Projects - ProjName, ProjDesc
Expertise - ExpertiseID, ExpertiseDesc
Software - Soft ID, SoftDesc

There is a many to many relationship between
Members - Projects
Members - Expertise
Members - Software

These are "taken care of" by use of bridge entities which hold the primary key of each table:

br_ProjMem -> ProjId, MemID
br_MemExpertise -> MemID, ExpertiseID
br_MemSoft -> MemID, SOftID

Members - br_Memproj - Projects
Members - br_MemExpertise - Expertise
Members - br_MemSoft - Software


Now i have to do queries based on selected criteria (the ids for each table)


for example:

if i could even figure out how to select all the data from each table where the MemID = '21'.... it would be a start.

but when i try even the following sql statement:

SELECT Members.FName, Members.Initial, Members.LName, Members.Title, ECOffices.Region AS Expr1, Members.SpecialityNotes, Members.BuildAdd, ECOffices.Address, ECOffices.City, ECOFfices.Prov AS Expr2, ECOffices.PostCode, Members.phone, Members.fax, Members.email, Software.SoftDesc, Expertise.ExpertiseDesc, Projects.ProjName
FROM ECOffices, Members, Software, Expertise, br_ProjMem, br_MemSoft, br_MemExpertise, Projects

WHERE
Members.MemID = br_ProjMem.MemID AND
Projects.ProjName = br_ProjMem.ProjName AND

Members.MemID = br_MemExpertise.MemID AND
Expertise.ExpertiseID = br_MemExpertise.ExpertiseID AND

Members.MemID = br_MemSoft.MemID AND
Software.SoftID = br_MemSoft.SoftID AND

ECOffices.OfficeID = Members.OfficeID;


even without supplying any criteria, i get an empty set of records.


how should i write my sql statment so this works out right??

Thanks,

Dave

Yuck!

Use JOINS, don't simply rely on WHERE clauses to designate certain records. You're killing your server with that many WHERE clauses.

Look at the http://www.mysql.com web site for JOIN and you should use a combination of LEFT JOINS between the tables.
========================================
Buh Bye!

Cheers,
Me

No, a LEFT JOIN is different then just joining a table, and probably not what you want. If you have:

SELECT e.*, c.*
FROM Employees AS e LEFT JOIN Company AS c ON e.companyid = c.id

Then if you have employees without a matching company, they will be in the result set with c.* all populated as NULL's. Compare this to:

SELECT e.*, c.*
FROM Employees AS e, Company AS c
WHERE e.companyid = c.id

Will only give you results with matching companies. This is an important difference. There is no performance penalty for specifying the condition in the WHERE clause.

That said, you are probably better off starting small, and building your query up to see where you are going wrong. i.e. Start just getting a list of Members and Projects. Then try to add in Expertise, etc.

Cheers,

Alex
--
Gossamer Threads Inc.