SQL uncontrollable Options

[edson1314]

I already expressed in a previous post (if possible delete) and I did so very awkward.
That is a better presentation of my problem:
I have 3 tables:
- Members (site members)



- Categories (categories of members: developer, designer, etc. ..)



- Skills (the skills of members in each category (php / mysql developer in 2D and 3D design, etc ...)




My goal is: (in the context of a search by jurisdiction, category etc ...)
Display for each member who responds to his nickname search criteria, skills, date of postulation etc. ...
The problem occurs at this time of reflection, I have attached the 3 tables in my SQL but the script does not do what I want!


Code: SQL

CODE

SELECT co.competence, co.type, co.time, co.note, co.id, ca.available ace available, ca.refs, m.pseudo, ca.id AS CategoryID,
ca.Class, DATE_FORMAT(datepostulation, "% D /% m /% Y") AS date, m.Class AS droitsmembre, m.id AS idmembre
FROM Categories AS ca LEFT JOIN Member AS m ON m.id=ca.idmembre
LEFT JOIN skills AS co ON co.type="Spe" AND co.CategoryID=ca.id WHERE m.valid=1 AND ca.state=1 AND ca.Class="Developer"

The results of this query looks like this:
Image utilisateur
Yet this does not suit me since the application displays one line per jurisdiction and not by individual. There's there is an inconsistency in the statement?

Thank you in advance!
T'ry.

[wgabrie]

Let's see, I've not used SQL before, but I want to try looking it up.

So, You've used:
1. SELECT. I think you selected more entries than you defined using "AS". Could this perhaps be a problem?
2. AS - which is an: Alias.
3. JOIN specifically a LEFT JOIN
4. WHERE

From your description perhaps you are pulling the database tables in the wrong order. Or perhaps you need to focus on the "individuals" table first then join the other tables to it.

I'm not sure where you're going wrong.

CODE

SELECT
        co.competence,
        co.type,
        co.time,
        co.note,
        co.id,
        ca.available ace available,
        ca.refs,
         m.pseudo,
        ca.id
AS
        CategoryID,
        ca.Class,
        DATE_FORMAT(datepostulation, "% D /% m /% Y") AS date,
        m.Class AS droitsmembre,
        m.id AS idmembre

FROM Categories AS ca

        LEFT JOIN Member AS m
         ON m.id=ca.idmembre

        LEFT JOIN skills AS co
         ON co.type="Spe"
        AND co.CategoryID=ca.id

WHERE
     m.valid=1
AND ca.state=1
AND ca.Class="Developer"

I wish I could be helpful and remember the link where I read about sorting SQL statements, about a week ago, but I can't remember it or find it on W3schools.com right now. The closest I could find is ORDER BY, but that isn't it.

This post has been edited by wgabrie: Nov 26 2010, 01:41 PM