Select records based on criteria and NOT based on other

[jeff5656]

I hope I can explain this.  I want to select records that match certain criteria as seen here:

 

$sql = "Select * from $DB_TBLName, $DB_TBLName2, $DB_TBLName3 WHERE $DB_TBLName.id = $DB_TBLName2.case_id and $DB_TBLName2.procedure_id = '12'  and $DB_TBLName.status = 'Closed' ";

 

So I want records with a procedure_id of 12.  But some records have more than one procedure_id associated with it.  I want to exclude records that have a procedure_id of 82, EVEN if they ALSO have a procedure_id of 12.  How do I modify the above code to make that happen? thnx

[trq]

How exactly can there be two procedure_id fields within the smae table?

[jeff5656]

It's an array.  A subject can have many different procedures done that day so the record has many different procedure_id's.  If one of those id's happens to be 82, I want the whole record (identified in table 1) to be not included.

 

Are you sure this shouldn't go under php help since I need the php syntrax to do this. Or at least please mirror it to that forum because I always get quick (and correct) replies in that forum compared to others.  Maybe move it after someone there correctly solves it (which is usually in less than an hour). thanks

[trq]

Are you sure this shouldn't go under php help since I need the php syntrax to do this. Or at least please mirror it to that forum because I always get quick (and correct) replies in that forum compared to others.  Maybe move it after someone there correctly solves it (which is usually in less than an hour). thanks

 

You need help with the query, its not php related.

 

Can we see your table structure? I'm still not sure hwo this is setup.

[jeff5656]

Ok here are the 3 tables.

Table 1 ($DB_TBLName)

CREATE TABLE `cases` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `user_id` int(11) unsigned NOT NULL default '0',
  `procedure_date` date NOT NULL,
  `arrival_time` time default NULL,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `mrn` int( unsigned zerofill NOT NULL,
  `dob` varchar(16) NOT NULL,
  `patient_location` enum('Inpatient','Outpatient') NOT NULL default 'Inpatient',
  `patient_location_value` varchar(16) NOT NULL,
  `ordering_staff` varchar(65) NOT NULL,
  `ordering_fellow` varchar(65) NOT NULL,
  `procedural_staff` varchar(65) NOT NULL,
  `procedural_fellow` varchar(65) NOT NULL,
  `blds_needed` enum('none','CBC','Coags') NOT NULL,
  `xray` enum('Yes','No') NOT NULL default 'No',
  `ct` enum('Yes','No') NOT NULL default 'No',
  `consent` enum('Yes','No') NOT NULL default 'No',
  `patient_called` enum('Yes','No') NOT NULL default 'No',
  `status` enum('Open','Closed') NOT NULL default 'Open',
  `added_date` date NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `procedure_date` (`procedure_date`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2653 ;

 

Table 2 ($DB_TBLName2):

CREATE TABLE `case_procedure` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `procedure_id` int(11) unsigned NOT NULL default '0',
  `case_id` int(11) unsigned NOT NULL,
  `custom_text` varchar(128) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `case_id` (`case_id`),
  KEY `procedure_id` (`procedure_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=58014 ;

 

Table 3 ($DB_TBLName3):

CREATE TABLE `procedures` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `parent_id` int(11) unsigned NOT NULL default '0',
  `label` varchar(64) NOT NULL,
  `is_custom` tinyint(4) unsigned NOT NULL default '0',
  `pos` tinyint(4) unsigned NOT NULL default '0',
  `level` tinyint(4) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=113 ;

 

Hope this is what you were asking for.  Let me know if you need other things.

[trq]

Ok, I think I can see what your doing. Have you dried....

 

$sql = "Select * from $DB_TBLName, $DB_TBLName2, $DB_TBLName3 WHERE $DB_TBLName.id = $DB_TBLName2.case_id and $DB_TBLName2.procedure_id = '12'  &&  $DB_TBLName2.procedure_id != 12 and $DB_TBLName.status = 'Closed' ";

[jeff5656]

&&  $DB_TBLName2.procedure_id != 12 

 

you mean

&&  $DB_TBLName2.procedure_id != 82

right? 

 

[trq]

Oops, yeah, thats what I meant.

[jeff5656]

Ok I tried that and I still get records that contain both a procedure_id of 12 and 82.  I had tried this before with AND instead of && with the same result.

[fenway]

Re-write that query with proper JOIN syntax and we'll take it from there.

[jeff5656]

Should I re-post this under php help forum now because I don't know how the proper JOIN syntax would be written.  Is the way I wrote it wrong?  If so, how? Thanks.

[fenway]

Should I re-post this under php help forum now because I don't know how the proper JOIN syntax would be written.  Is the way I wrote it wrong?  If so, how? Thanks.

JOIN syntax is a mysql issue... how are your three tables related?

[jeff5656]

Well the first table contains the patient name and record number as well as other info (see above).  Table 2 contains what procedures each patient has and table 3 is a table listing the procedures ("label") with an id ("procedure_id").

Can you tell me what the proper JOIN syntax is?

[fenway]

I was confused by your query which has variables for the table names.

 

You want to start with something like this:

SELECT *
FROM cases AS c
INNER JOIN case_procedure AS cp ON ( c.id = cp.case_id )
INNER JOIN procedures AS p ON ( p.id = cp.procedure_id )
WHERE cp.procedure_id = '12'

 

As you can see, your query was missing a join condition on what you call table "3", which likely resulted in duplicate records -- why was it there?