Difficult SELECT Statement

[n0nick]

I got three tables:

-----------------------------------
tbl_depart
primary key: DEPART_ID
name
-----------------------------------
tbl_depart_weekday
primary key: DEPART_WEEKDAY_ID
foreign key: DEPART_ID
foreign key: WEEKDAY_ID
-----------------------------------
tbl_weekday
primary key: WEEKDAY_ID
weekdayName
-----------------------------------

I want to make a SELECT statement that returns this data when it runs:

DEPART_ID | name | monday | tuesday | wednesday | thursday | friday
-------------------------------------------------------------------
1 | dep1 | 0 | 1 | 1 | 0 | 1

The value 0 or 1 for monday-friday represent if the there is a post in 'tbl_depart_weekday' with 'DEPART_ID=1' and 'WEEKDAY_ID=1,2,3..' for this row

I hope you understand my question..

Thanks a lot!

-ismar-

 

[rac2]

This is what I call the "rows into columns" requirement. Search the SQL forums on that phrase.

Can there be 0 or 1 rows in tbl_depart_weekday for each department, or can there be 0 or many rows?

If it is 0 or 1 rows -

SELECT d.DEPART_ID, d.name,
       CASE 
         WHEN wd1.DEPART_WEEKDAY_ID IS NULL THEN 0
         ELSE 1
       END AS "Monday",
       CASE 
         WHEN wd2.DEPART_WEEKDAY_ID IS NULL THEN 0
         ELSE 1
       END AS "Tuesday"

FROM tbl_depart d
LEFT JOIN tbl_depart_weekday wd1 
          ON wd1.DEPART_ID = d.DEPART_ID 
         AND wd1.WEEKDAY_ID = 1
LEFT JOIN tbl_depart_weekday wd2 
          ON wd2.DEPART_ID = d.DEPART_ID 
         AND wd2.WEEKDAY_ID = 2

All of the departments in tbl_depart will have a row in the result. The LEFT JOIN requires a row even when there are no rows in tbl_depart_weekday for that department.

Of course you will need a LEFT JOIN for each day of the week.

If there may be 2 or more rows in tbl_depart_weekday for a department then you will need to do more to obtain a single row per department. I think SELECT DISTINCT etc. might do that. If not then you will need to write a subquery on tbl_depart_weekday that yields a single row per day and department.

Something like -

SELECT DEPART_ID, WEEKDAY_ID, COUNT(*)
FROM tbl_depart_weekday
GROUP BY DEPART_ID, WEEKDAY_ID

That will have 0 or 1 row for every combination of department and week day. Use the subquery instead of tbl_depart_weekday in the first query.

 

[n0nick]

Ok, thanks a lot! I will take closer look on you code! But I think that i can do this much easier with a loop in C# ( ASP.NET ). Beacuse i'm not just displaying the data, but I also make it possible to add/remove a depart_weekday in GridView ( ASP.NET ).

But again, thank you very much!

 

[rac2]

Yes, it is better to do that kind of thing in the presentation rather than in the retrieval.