Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

select only columns when two rows have the same value 1

Status
Not open for further replies.
Dec 4, 2005
3
US
Hi everyone,

I have a simple question about a select statement that is-not-too-simple for me.

I have one sql table, named 'schedule' which stores
schedule info for all users. The table only stores
scheduling data for mon-fri, 8am-5pm. Its only a
prototype.
[tt]
+-------+---------+---------+---------+-----+---------+
|userid | mon0800 | mon0830 | mon0900 | ... | fri1700 |
+-------+---------+---------+---------+-----+---------+
| 1 | 5 | -1 | -1 | ... | -1 |
+-------+---------+---------+---------+-----+---------+
| 2 | -1 | -1 | -1 | ... | -1 |
+-------+---------+---------+---------+-----+---------+
| 5 | 1 | -1 | -1 | ... | -1 |
+-------+---------+---------+---------+-----+---------+
[/tt]

If a user is available for meetings, then -1 is stored
in the column; otherwise, the userid of whoever the user has
scheduled a meeting with is stored in the column. In the
example above, userid '1' has a meeting on monday at 8:00am
with userid '5'. Userid '1' has no meetings scheduled for
any other times. Userid '2' has no meetings scheduled.

What I need to do is make a sql query that will tell
me the common times that two userid's have available for a
meeting (The timeslots they have in common that are equal to
-1).

So if I were to run the available times query for
userid's 1 and 5 on the above dataset, I would expect the
following result:
[tt]
+---------+---------+-----+---------+
| mon0830 | mon0900 | ... | fri1700 |
+---------+---------+-----+---------+
| -1 | -1 | | -1 |
+---------+---------+-----+---------+
[/tt]

Any ideas?

Thanks a bunch!
 
any ideas? yes, you should normalize that design, so that instead of a table with a key of userid and 100 columns, you have a table with userid and timeslot as keys, and only one column

the sql will be remarkably simpler

r937.com | rudy.ca
 
Forgive my ignorance. Your suggestion does look alot cleaner.

So the table should look like
[tt]
CREATE TABLE schedule (
userid int(10) NOT NULL,
timeslot char(10) NOT NULL,
available enum('Y','N') DEFAULT 'Y' NOT NULL
)
[/tt]

Thanks again for the help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top