desperate for help...
Ok, I've been battling this SELECT statement for the better part of the day. The SELECT statement is this: SELECT DISTINCT request.id AS requestid, request.date, request.type, request.status, faculty.f_name, faculty.l_name, action.id AS actionid, faculty.id AS facultyid FROM faculty, request, action WHERE request.id=action.request_id AND request.requested_by=faculty.id AND request.status=0; And the results are here: http://www.geology.ohio-state.edu/test/rfatest/rfaadmin.php?closed=Closed Now, if you look at the table, at the far left cell under RFA#, you'll notice that they are all the same (2). That is because there were 4 actions performed on RFA # 2. However, I only need for the column to be displayed once. When the user clicks under 'last action' then they can see all 4 actions. But for this table, I just need to show the data once. So, could any kind soul help me out? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: desperate for help...
I rearranged the query a little: SELECT DISTINCT request.id AS requestid, request.date, request.type, request.status, faculty.f_name, faculty.l_name, action.id AS actionid, faculty.id AS facultyid FROM faculty, request, action WHERE request.id=action.request_id AND request.requested_by=faculty.id AND request.status=2; And it still does the same thing. I rechecked all the fields that I am querying and I do need all of them. Because I need to match the actions to the request(request.id=action.request_id), the requests to the faculty member who submitted the request(request.requested_by=faculty.id) and make sure I am only retrieving requests that are being worked on(request.status=2) I know it sounds complicated, but this is the only way I can think of to get the data I need. Thanks, Chris At 09:49 AM 3/7/2001 -0700, you wrote: It looks like the addition of the distinct verb should do the job if in fact your query only showed the fields on the display. From your Sql however you are selecting more fields than are displayed and some of those are different which distinct will still display. Chris Toth wrote: Ok, I've been battling this SELECT statement for the better part of the day. The SELECT statement is this: SELECT DISTINCT request.id AS requestid, request.date, request.type, request.status, faculty.f_name, faculty.l_name, action.id AS actionid, faculty.id AS facultyid FROM faculty, request, action WHERE request.id=action.request_id AND request.requested_by=faculty.id AND request.status=0; And the results are here: http://www.geology.ohio-state.edu/test/rfatest/rfaadmin.php?closed=Closed Now, if you look at the table, at the far left cell under RFA#, you'll notice that they are all the same (2). That is because there were 4 actions performed on RFA # 2. However, I only need for the column to be displayed once. When the user clicks under 'last action' then they can see all 4 actions. But for this table, I just need to show the data once. So, could any kind soul help me out? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
more help with SELECT statement
Hi, A few days ago, I asked the list about a SELECT statement. I received 2 replies that didn't really work but helped me think about the problem some more. In my database I have 4 tables. For this one query, I am matching up data from 3 of these tables and then displaying them on a website. Here is the query: SELECT request.id, request.date, request.type, request.status, faculty.f_name, faculty.l_name, action.id, faculty.id FROM faculty, request LEFT JOIN action ON request.id=action.request_id WHERE request.requested_by=faculty.id; and here is the output created by a php script with data from the above query: http://www.geology.ohio-state.edu/test/rfatest/rfadisplay.php I know it looks ok, but I am not quite sure the SELECT statement is doing what I want it to. I want the query to match up all the requests(request) with the person who requested them(faculty) and then match up all the requests to any actions that our staff might have performed on them (action). However, if no actions were performed yet, I still need those requests displayed. So basically I am matching up one table with two other tables. So if any one can examine the above SELECT statement, and let me know if it does/doesn't look ok, then that would be great. Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
interesting problem
I have a form on a webpage for a simple trouble ticket system. When requesting a computer be fixed, software be installed, etc...a faculty member can go to this website and type in the info via an HTML form. My problem is, the form needs to be submitted to two different tables. All of the faculty info(name, email, etc) goes into a faculty table. The actual description of the request goes into a request table. But I also need to insert the unique id that is given to the faculty member in the faculty table INTO the request table. Because this is how I've related the two tables. In the request table I have a foreign key(called requested_by) that is the primary key of the faculty table. Is it possible for me to get the primary key of the faculty table and insert it into the request table through the same HTML form? Thanks, chriz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
need some help here...
I'm having a extremely hard time grasping the concept of multiple tables. So far, I've been using just one table when designing a database. But now I have to design a database for a trouble-ticket system for our department. I've written out the design of the tables, but the part I don't understand is how the tables relate to each other. Do I need to use foreign keys? But if I do, I thought MySQL didn't support foreign keys? BTW, I've read most of the O'rielly mSQL/MySQL book and couldn't find my answers. Just in case it matters, I'm going to have one table full of faculty info, one of staff info, one for the trouble ticket itself, and possibly one for actions performed on the trouble ticket.
