too many results from one table and other problems

[cyberpac9]

ok, i'm not the strongest SQL programmer, so i can't figure this out. i'm working on a report with the following source code:

SELECT vio_access_mid.safety_survey_date, vio_access_mid.violation, vio_access_mid.comments, vio_chemical_mid.violation AS chem_vio, vio_chemical_mid.comments AS chem_comm, vio_chemical_mid.safety_survey_date AS chem_date

FROM vio_access_mid INNER JOIN vio_chemical_mid ON vio_access_mid.lab_id = dbo.vio_chemical_mid.lab_id

WHERE EXISTS
      (SELECT ehs_labs.lab_id, tbl_list_building.building_desc, tbl_list_department.department_desc, ehs_labs.lab_room, ehs_list_lab_type.lab_type_desc, ehs_princ_inv.princ_inv_fname, ehs_princ_inv.princ_inv_lname
FROM ehs_labs INNER JOIN tbl_list_building ON ehs_labs.building_id = tbl_list_building.building_id INNER JOIN tbl_list_department ON ehs_labs.department_id = tbl_list_department.department_id INNER JOIN ehs_list_lab_type ON ehs_labs.lab_type_id = ehs_list_lab_type.lab_type_id INNER JOIN ehs_princ_inv ON ehs_labs.lab_id = ehs_princ_inv.lab_id
WHERE (ehs_labs.building_id = '0146') AND (ehs_labs.lab_room = '5678'))

AND (vio_access_mid.safety_survey_date = '6/1/2005') AND (vio_chemical_mid.safety_survey_date = '6/1/2005')

when i run the code, i get the correct amount of records (3) from vio_access_mid, but i also get 3 from vio_chemical_mid when in reality there are only 2 records. i will eventually add other vio_*_mid tables that will have a variety of results (some may have 0 while others might have 4 or 5, etc).

i would eventually like to be able to include the info from the WHERE EXISTS clause in my results, but one thing at a time...

anyone have suggestions on how to get the correct info from each vio_*_mid table?

 

[cyberpac9]

ok...so i've taken a closer look at what i have and what i should be getting....i was a little off yesterday because of an oversight on my part. here's what i have:

vio_chemical_mid
----------------
[B]lab_id     safety_survey_date    violation_id   violation    comments[/B]
d15cc     6/1/2005                  52c2d        Leaks       NULL
49e65     6/1/2005                  dee9f         broken      NULL
44685     6/2/2005                  6b9c8        Leaks       NULL

vio_access_mid
----------------
[B]lab_id     safety_survey_date    violation_id   violation    comments[/B]
49e65         6/1/2005               0d20b        unlocked       NULL
d15cc         6/1/2005               1aa60        blocked        NULL
49e65         6/1/2005               76662        blocked       NULL
93f59          6/26/2005             1e229        unlocked       NULL

what i was trying to get from my original query was the information from the above two tables whose "lab_id" had a "building_id = 0146" and "lab_room = 5678" from the table ehs_labs. in the table ehs_labs, the "lab_id" should be equal to "d15cc" (i can look at the table and find it by searching for the "building_id" of 0146 and found it should be d15cc.)

thus, i only want to show the 1 result from each table (and if other tables have 4 results with a lab_id=d15cc, then i'd like to show those as well.)

however, i'm getting 3 results and it appears as though it is finding the date to be more important than the lab_id. it is behaving like you said, it is doing a x times y. i get one result with d15cc from each table and the other two results use the two entries from vio_access_mid and the one from vio_chemical_mid where the lab_id=49e65...these two results should not be shown, only the d15cc. if you refer to the tables above, i'll try to explain my results using the violation_id:

[B]access violation_id    chemical violation_id[/B]
     1aa60                   52c2d
     0d20b                   dee9f
     76662                   dee9f

i may not be going at the query the correct way. like i said, i have several of these vio_*_mid tables. i'm trying to create a report for a specific date, building, and room that lists all the violations for that day...one table might produce 1 result, another 6 results and the rest none. but all the tables will vary in their answers. what would be the best way to attack this problem? i'm thinking this is very complex but i don't know i only know the basics of SQL.

thanks for your assistance with this.

 

[cyberpac9]

i figured i was going at this the wrong way...you're correct when you say i have one table with several 1-to-many relationships.

the main table is the ehs_labs with 1-to-many relationships to my vio_*_mid tables. let's see if i understand you correctly and start down the right path (finally): since ehs_labs is the main table i should have that as the main source for the report. then, with subreports created by the relationships between ehs_labs and the vio_*_mid tables?

REPORT
ehs_labs info
Sub-report1
vio_access_mid info
Sub-report2
vio_chemical_mid info

and so on....thus, sub-report1 will "link" to the REPORT by the 1-to-many relationship between these tables (ehs_labs and vio_access_mid). the same for sub-report2, it will "link" to the REPORT by the 1-to-many relationship between ehs_labs and vio_chemical_mid.

does that sound like i'm heading down the right path? i hope so, cause this thing has been driving me nuts.

 

[cyberpac9]

I think you've goit it

thank you so much...i knew there was an easier way (actually a correct way) but i sorta had tunnel vision...i couldn't see anything but my incorrect solution.