select records in table 1 based on criteria from tabel 2

[jeff5656]

 

 

I have records from the table users:

 

$query = "SELECT *  FROM users WHERE id= '$user' ";

 

Now in another table called todo there are a bunch of records that are active or inactive (status = 'a' or 'i') for a given user. Fieldnames in todo table include "user_id" and "status"

How do I select only the users in the user table that have at least one active record from the todo table?

 

 

 

 

[Alex]

SELECT * FROM users WHERE (SELECT COUNT(user_id) FROM todo WHERE user_id = '$user' AND status = 'a' LIMIT 1) > 0

[Maq]

From your first query, if you're specifying a $user id then it will only return results for that user.

 

If I'm interpreting your question correctly then you need to join the tables, most likely on user_id from the todo table and id (or whatever it is) from the users table and add the condition for an active status.  Your query should look similar to:

 

SELECT DISTINCT u.user FROM users u LEFT JOIN todo t ON u.id=t.user_id WHERE t.status='a';

 

Only select what you need, not *.

Change u.user to u.[field_name].

[jeff5656]

Hi,

the distinct didnt seemt o work.  Here is my actual query.  This is with a different table. sorry for the confusion.  So with the following query:

$query = "SELECT DISTINCT * FROM todo LEFT JOIN todo_list ON todo.id = todo_list.todo_id WHERE todo_list.signoff_status = 'a'";

 

I am still getting records where signoff_status = 's' even though the above shows that I specifically wanted only  signoff_status = 'a'!

 

 

 

 

 

[Maq]

First you made it seem like you wanted to get information from the 'todo' and 'users' table, now there's a 'todo_list' table.  Can you show us the table definitions of the tables you want to use?

[jeff5656]

sorry about changing the user tables around. i should have used the real ones but was trying to keep it simple and it backfired.   OK here are the two tables:

 

CREATE TABLE `todo` (
  `id` int(10) NOT NULL auto_increment,
  `lname` varchar(60) NOT NULL,
  `fname` varchar(60) NOT NULL,
  `mrn` varchar(20) NOT NULL,
  `signoff_status` enum('a','s') NOT NULL,
  `save_status` enum('n','y') NOT NULL,
  `username` varchar(60) NOT NULL,
  PRIMARY KEY  (`id`)
)

 

and this is the other table - todo_list

CREATE TABLE `todo_list` (
  `id` int(10) NOT NULL auto_increment,
  `todo_id` int(10) NOT NULL,
  `todo` text NOT NULL,
  `todo_date` date NOT NULL,
  `close_reason` text NOT NULL,
  `close_date` date NOT NULL,
  `signoff_status` enum('a','s') NOT NULL,
  PRIMARY KEY  (`id`)
) 

 

What I want to do is only display records in todo where todo_list.signoffstatus is 'a'.  The todo_id in the second table is id in table 1.