Large SQL queries

meigwil

Hi,

I'm trying to build a calendar app for work.

The calendar works with the days of the week as rows, and the various departments' as columns. Only one entry is needed per dept. per day.

Presently my database setup is a table for each department, with 'date' (in the format 2006-06-06) and 'activity' as the fields.

My first attempt had a loop of the days of the week, within which was a loop with a mysql_query for each dept. This of course is very (very) inefficient.

What I want is a query that can get all records from all tables (or selected tables, but that's for later) that have a specific date. I think that if possible, a date range would be best, but I'm not sure if this is possible.

I've tried this unsuccessfull:

$sql = "SELECT * FROM 
  table1, table2, table3, table4, table5 
  WHERE table1.date_id = '2007-06-06' 
  OR table2.date_id = '2007-06-06' 
  OR table3.date_id = '2007-06-06' 
  OR table4.date_id = '2007-06-06' 
  OR table5.date_id = '2007-06-06' ";

This produced no record sets (even though the data existed) so I know that the query is incorrect.

Can anyone help?

Thanks,

Mei

shu

I think you are approaching the problem the wrong way. Complicated tasks can be simplify by a normalized database structure. Instead of creating a table for each department (which is incredibly inefficient), you should create a single table that hold all departments. A structure like:

department

deptID (PK)
deptName
....

activity

actID (PK)
deptID (FK)
activityName
activityDate
....

With a structure like this, you can accomplish your goal easily with a query like:

SELECT a.*, b.deptName FROM activity a, department b
WHERE a.deptID=b.deptID AND b.activityDate='2007-06-06'

Hope this helps...

meigwil

Thanks, that's just what I need.

I had been thinking about the DB design for a long time, and this solution is much better one.

Your way also adds scope to extend the app as well.

Is there a way of querying the date for a date range, instead of more than one OR? Would BETWEEN work here? e.g.

AND d.date_id BETWEEN '2006-06-06' AND '2006-06-13'

Thanks again,

Mei

cgraz

Yes, you could use BETWEEN there.