SQL Query

[CuChulla1nn]

Hey there,

I have my website running with Userspice and I like it a lot. Now I want to display a table with data from a database. I understand how to execute basic querys but mine is a bit special. Could you help me transform it into the right format to fit into Userspice?

<pre>

Code:

SELECT
    X.datum,
  MAX(X.schicht1) AS schicht1,
  MAX(X.schicht2) AS schicht2,
  MAX(X.schicht3) AS schicht3,
  MAX(X.schicht4) AS schicht4,
  MAX(X.schicht5) AS schicht5,
  MAX(X.schicht6) AS schicht6,
  MAX(X.schicht7) AS schicht7,
  MAX(X.schicht8) AS schicht8,
  MAX(X.schicht9) AS schicht9,
  MAX(X.schicht10) AS schicht10
    FROM
      (SELECT
            datum,
        CASE WHEN s.schichtID = 1 THEN u.username ELSE NULL
                END AS schicht1,
            CASE WHEN s.schichtID = 2 THEN u.username ELSE NULL
                END AS schicht2,
            CASE WHEN s.schichtID = 3 THEN u.username ELSE NULL
                END AS schicht3,
            CASE WHEN s.schichtID = 4 THEN u.username ELSE NULL
                END AS schicht4,
            CASE WHEN s.schichtID = 5 THEN u.username ELSE NULL
                END AS schicht5,
            CASE WHEN s.schichtID = 6 THEN u.username ELSE NULL
                END AS schicht6,
            CASE WHEN s.schichtID = 7 THEN u.username ELSE NULL
                END AS schicht7,
            CASE WHEN s.schichtID = 8 THEN u.username ELSE NULL
                END AS schicht8,
            CASE WHEN s.schichtID = 9 THEN u.username ELSE NULL
                END AS schicht9,
            CASE WHEN s.schichtID = 10 THEN u.username ELSE NULL
                END AS schicht10
            FROM
              schichten s
            LEFT JOIN users u
              ON s.userID = u.id
        ) X
        WHERE DATE
          (datum) >= DATE(NOW())
        GROUP BY X.datum

</pre>


I would appreciate the help.

Best regards,
Yannick