where and

I have a table plasmid_table with key plasmid_id (not shown), which is associated an antibiotic table (also not shown) with primary key antibiotic_id by way of the table below named plasmid_to_antibiotic_table.


```
plasmid_fk antibiotic_fk
14			1
15			1
16			1
17			1
18			1
19			1
20			1
26			4
26			5
```

I want to find the sole record that contains both antibiotics 4 and 5, so I did a search as follows

```SELECT plasmidName_col FROM plasmid_table 
INNER JOIN plasmid_to_antibiotic_table ON plasmid_table.plasmid_id = plasmid_to_antibiotic_table.plasmid_fk 
INNER JOIN antibiotic_table ON plasmid_to_antibiotic_table.antibiotic_fk = antibiotic_table.antibiotic_id 
WHERE (antibiotic_table.antibiotic_id = 4 AND antibiotic_table.antibiotic_id = 5)
```

This doesn't work, of course because antibiotic_table.antibiotic_id can't both be 4 and 5 in the same record.

The solution I discovered is to to replace the WHERE clause with
WHERE antibiotic_table.antibiotic_id IN (4,5)
Group BY plasmidName_col
having count(antibiotic_table.antibiotic_id) = 2

However, it's going to get complicated because I need searches like these across multiple intermediate tables and even merely adding the second intermediate table like so

INNER JOIN plasmid_to_antibiotic_table ON plasmid_table.plasmid_id = plasmid_to_antibiotic_table.plasmid_fk 
INNER JOIN antibiotic_table ON plasmid_to_antibiotic_table.antibiotic_fk = antibiotic_table.antibiotic_id 

INNER JOIN plasmid_to_fluoro_tag_table ON plasmid_table.plasmid_id = plasmid_to_fluoro_tag_table.plasmid_fk 
INNER JOIN fluoro_tag_table ON plasmid_to_fluoro_tag_table.fluoro_tag_fk = fluoro_tag_table.fluorotag_id 

causes the having clause to fail to return any results and I haven't even gotten to making the compound having clause yet for this second set of tables.

plasmid_table = recipe table
antibiotic_table = ingredients table
plasmid_to_antibiotic_table = recipeingredients table

SELECT p.plasmid_id, p.plasmidName_col
FROM plasmid_table p
WHERE EXISTS
(
    SELECT *
    FROM plasmid_to_antibiotic_table pa
    INNER JOIN antibiotic_table a
        ON a.antibiotic_id = pa.antibiotic_fk 
    WHERE pa.plasmid_fk  = p.plasmid_id
    AND a.antibiotic_id = 4
)
AND EXISTS
(
    SELECT *
    FROM plasmid_to_antibiotic_table pa
    INNER JOIN antibiotic_table a
        ON a.antibiotic_id = pa.antibiotic_fk 
    WHERE pa.plasmid_fk  = p.plasmid_id
    AND a.antibiotic_id = 5
)
AND EXISTS 
(
    SELECT *
    FROM plasmid_to_fluoro_tag_table pf
    INNER JOIN fluoro_tag_table f
        ON f.fluorotag_id  = pf.fluoro_tag_fk 
    WHERE pf.plasmid_fk  = p.plasmid_id
    AND f.fluorotag_id  = 4
)
AND EXISTS
(
    SELECT *
    FROM plasmid_to_fluoro_tag_table pf
    INNER JOIN fluoro_tag_table f
        ON f.fluorotag_id = pf.fluoro_tag_fk 
    WHERE pf.plasmid_fk  = p.plasmid_id
    AND f.fluorotag_id  = 5
)


SELECT plasmidName_col FROM plasmid_table
INNER JOIN plasmid_to_antibiotic_table ON plasmid_table.plasmid_id = plasmid_to_antibiotic_table.plasmid_fk 
INNER JOIN antibiotic_table ON plasmid_to_antibiotic_table.antibiotic_fk = antibiotic_table.antibiotic_id 
WHERE antibiotic_table.antibiotic_id IN (4,5)
Group BY plasmidName_col
having count(distinct antibiotic_table.antibiotic_id) = 2
---------------------Ñ
SELECT DISTINCT plasmid_id,plasmid_table.plasmidName_col FROM plasmid_table

INNER JOIN plasmid_to_antibiotic_table ON plasmid_table.plasmid_id = plasmid_to_antibiotic_table.plasmid_fk 
INNER JOIN antibiotic_table ON plasmid_to_antibiotic_table.antibiotic_fk = antibiotic_table.antibiotic_id 

INNER JOIN plasmid_to_fluoro_tag_table ON plasmid_table.plasmid_id = plasmid_to_fluoro_tag_table.plasmid_fk 
INNER JOIN fluoro_tag_table ON plasmid_to_fluoro_tag_table.fluoro_tag_fk = fluoro_tag_table.fluorotag_id 

WHERE antibiotic_table.antibiotic_id IN (4,5)
Group BY plasmidName_col
having count(antibiotic_table.antibiotic_id) = 2


Problem contributor maybe an outside contributor although it doesn't seem we rcord this for transgene. perhaps it should
as a result, author field and contributor may not be the same.

overview,
research, shift 14 to the right
publications,
personnel,
life in the lab,
join us
resources

body {
        margin-right: 14px;
    }

185
178 is landing page distance

next question how do we find out who the current author is
it seems we get it from the $_SESSION['user'] variable

if (isset($_SESSION['user']) {
	author_fk = $_SESSION['user']
	editor_fk = $_SESSION['user'];
}


search will search them both, but how does it do that because right now 
we have 
LEFT JOIN contributor_table ON truestrain_table.authorID_col = contributor_table.contributor_id ';
LEFT JOIN contributor_table ON truestrain_table.editorID_col = contributor_table.contributor_id ';

turn these innner joins to left joins so we don't miss either one
we need two functions in search to search across these tables

now how do we save the editor