Pairwise queries...?

[turkishgold]

Hi,
I'm trying to figure out how to do what I guess you would call a pairwise query, where for example you have a column called Parameter that contains various codes for water quality parameters. Then, you have a column in the Access General Date format for Dates those parameters were sampled.

How would you write a query that would return records showing only, in this case, the records associated with two (or perhaps more than two) specific parameter codes (in the Parameter column as text strings) if they were sampled on the same Date/Time?

This has to be fairly simple but the logic is escaping me...

thanks
Tom

 

[georgedwilkinson]

select * from
mytable a inner join mytable b
on a.mydefiningkey = b.mydefiningkey
where
a.sampledt = b.sampledt
;

 

[Brianwarnock]

2 queries
query1 group by datefield
where but don't show on the parameter field aA or B or C and Count(*) with a criteria equal to the number of parameters being checked for.

then Join query1 back to the table on datefield . and same criteria in parameter field should pull the records.

Brian

 

[turkishgold]

select * from
mytable a inner join mytable b
on a.mydefiningkey = b.mydefiningkey
where
a.sampledt = b.sampledt
;

Not quite, I didn't give quite enough detail... the Parameters (in your example, mydefiningkeys a and b) and the DATES fields are in the same tables, respectively.

It's a normalized structure with a table called Sites, which has a 1:n relationship with the Samples table, which has a 1:n relationship with the Results table (one site has many samples, and each of those samples has many results)

The sample table contains: the Site ID (FK), the DT_TM, and the Sample_ID (PK).

The result table contains: the Sample_ID (FK), Record (PK), the Result Value, and the Parameter Code.

So, with what I was describing in my first post, we have two criteria - the first being only records that are one of two particular parameter codes (Parameter field in result table), and then of those records, only the records that were sampled on the same Date (DT_TM field in sample table)

Seems like this would need to be some sort of nested query, just not sure on syntax at this point....

2 queries
query1 group by datefield
where but don't show on the parameter field aA or B or C and Count(*) with a criteria equal to the number of parameters being checked for.

then Join query1 back to the table on datefield . and same criteria in parameter field should pull the records.

Brian

I don't understand what you mean...not sure if that would get me to where I want to go

thanks!
Tom

 

[georgedwilkinson]

After your further description, it seems even simpler to me.

Are you saying that you want all the results or a certain type of a given sample? If that is true, a group by approach like Brian seemed to be suggesting would probably be better. If you just want 2 test results on the same line from the same sample, my approach is correct, you'll just need to fiddle around with the table names and joins.

 

[turkishgold]

After your further description, it seems even simpler to me.

Are you saying that you want all the results or a certain type of a given sample? If that is true, a group by approach like Brian seemed to be suggesting would probably be better. If you just want 2 test results on the same line from the same sample, my approach is correct, you'll just need to fiddle around with the table names and joins.

I would like fields from both the sample table and result table returned in the query results: the sample ID, the parameter code, the site ID, and the Date/Time.

Is there any way to do it w/o just sorting/grouping the results? As in only returning the records that meet the criteria of having one of two parameter codes, and then having matching dates (there will only be 2 records per date/time in this case if just interested in two parameter codes)? Otherwise, you end up with records for sample dates for one parameter that don't have an associated other parameter on that same date.

Regarding Brianwarnock's suggestion, I'm just having trouble deciphering the sql logic behind his post...parts of it I understand but then other parts are somewhat choppy in translation...

I realize in my lack of sleep I made an error in my description, however, I'm interested in sites (field in sample table) that have both parameter codes on the same date (there could be many instances of having a sample on a date with both parameter results)

apologies, i'm running on 4 hours of sleep...

thanks much for your help with this

Tom

 

[georgedwilkinson]

Ok, it's still not clear to me exactly what you want because what you describe you want is somewhat different than the fields you seem to be saying are in the result set.

You want to show whatever information (no problem) from a sample where there are 2 different "parameters" (which I assume mean test types). However, in your description of the fields, you only include 1 "parameter". My assumption from your description is that you want all the described fields plus the 2 parameters that occurred for that same sample: sample ID, parameter code 1, parameter code 2, the site ID, and the Date/Time

Please straighten me out.

 

[turkishgold]

Ok, it's still not clear to me exactly what you want because what you describe you want is somewhat different than the fields you seem to be saying are in the result set.

You want to show whatever information (no problem) from a sample where there are 2 different "parameters" (which I assume mean test types). However, in your description of the fields, you only include 1 "parameter". My assumption from your description is that you want all the described fields plus the 2 parameters that occurred for that same sample: sample ID, parameter code 1, parameter code 2, the site ID, and the Date/Time

Please straighten me out.

Yes, there is only one Parameter column that contains many different parameter codes - it represents the particular water quality parameter for the result value in each row/record. Something similar to this might be an "Automobile" table that contains a single field called "Make", which has a slew of different car types like Nissan, Honda, Oldsmobile, etc. In the final product database, there will be a standalone Parameter table with descriptions of the parameters, which would have a 1:n relationship with the Result Table (maybe that's where this was getting confusing)

I'm interested in sites (locations on the earth) that have instances of result values for two different parameter codes on the same date (I want to know which sites have samples containing results for pH AND temperature on the same date).

The date/time field is in the Sample table, and the Parameter field is in the Result table

The sample table contains these Fields/Columns (only occurring once each): the Sample_ID (PK), Site ID (FK), the DT_TM(date/time field).

The result table contains these Fields/Columns (only occurring once each): Record (PK), Sample_ID (FK), Parameter Code (FK), Result Value.

Let me know if this is still unclear....it's hard to convey in type sometimes with this stuff!

thanks
Tom

 

[turkishgold]

OK, in addition to my last post, these three separate SQL statements get me what I want. What I was hoping was a way to integrate these together (and to not have to create whole new full tables) into one query:

SELECT r.Sample_ID, s.DT_TM, r.Param, r.Result_Value INTO Temp
FROM QW_Field_Result as r INNER JOIN QW_Field_Sample as s
ON r.Sample_ID = s.Sample_ID
WHERE r.Param = '00010';

SELECT r.Sample_ID, s.DT_TM, r.Param, r.Result_Value INTO Elev
FROM QW_Field_Result as r INNER JOIN QW_Field_Sample as s
ON r.Sample_ID = s.Sample_ID
WHERE r.Param = '00054';

SELECT Temp.Sample_ID
FROM Temp INNER JOIN Elev ON Temp.Sample_ID = Elev.Sample_ID
WHERE Temp.DT_TM = Elev.DT_TM;

Is there a way to accomplish what these three SQL statements do, but in one SQL statement (and without having to create totally new data subset tables)?

I'd be interested to know just for my own learning of SQL - I've been learning as I go

thanks much
Tom

 

[georgedwilkinson]

Tom,

It sounds like everything you've suggested can be accomplished with my first answer.

(This is air code)

SELECT
  a.Sample_ID as Sample, 
  a.DT_TM as SampleDate, 
  b.Param as Param10,
  b.Result_Value as Result10,
  c.Param as Param54, 
  c.ResultValue as Result54
FROM
  QW_FieldResult as a
INNER JOIN 
  QW_Field_Sample as b
ON
  a.Sample_ID = b.Sample_ID
INNER JOIN 
  QW_Field_Sample as c
ON
  a.Sample_ID = c.Sample_ID
WHERE 
  b.Param = '00010'
AND 
  c.Param = '00054'
;

Is there more to it than that?

You can constrain it further by site by adding that table to the join and putting the site number in the where clause.

 

[turkishgold]

Tom,

It sounds like everything you've suggested can be accomplished with my first answer.

(This is air code)

SELECT
  a.Sample_ID as Sample, 
  a.DT_TM as SampleDate, 
  b.Param as Param10,
  b.Result_Value as Result10,
  c.Param as Param54, 
  c.ResultValue as Result54
FROM
  QW_FieldResult as a
INNER JOIN 
  QW_Field_Sample as b
ON
  a.Sample_ID = b.Sample_ID
INNER JOIN 
  QW_Field_Sample as c
ON
  a.Sample_ID = c.Sample_ID
WHERE 
  b.Param = '00010'
AND 
  c.Param = '00054'
;

Is there more to it than that?

You can constrain it further by site by adding that table to the join and putting the site number in the where clause.

I can see where you're going, but the logic in this example relative to the actual tables as I've described them from my understanding of SQL won't work:

- QW_Field_Result does not have a DT_TM field, so a.DT_TM won't work
- QW_Field_Sample does not have a Param or Result_Value field, so the SELECT "b..." and SELECT "c..." statements can't work

I'm not even sure how to adapt this to actually make it work.
My understanding is: You can't select fields from tables that don't have those fields, even if the table that actually has those fields is joined to the table you're referencing that doesn't actually have them (boy that's confusing but hopefully makes sense).

If the last statement is incorrect, please let me know (again, I'm learning every day with SQL)

thanks for your help
Tom

 

[Brianwarnock]

I did think from your first post that you only had one table.

But with names to use I would try this, air code of course.

Select r.sampleid, s.DT_TM, s.siteid,count(*) as Numberrecspergroup
From Results as r innerjoin Sample as s on r.sampleid=s.sampleid
Where r.param =”00054” or r.param=”00010”
Group by s.siteid, r.sampleid,s.DT_TM
Having Count(*)=2

This selects all of the sampleid that have those codes on the same date for a site..


Brian

 

[turkishgold]

I did think from your first post that you only had one table.

But with names to use I would try this, air code of course.

Select r.sampleid, s.DT_TM, s.siteid,count(*) as Numberrecspergroup
From Results as r innerjoin Sample as s on r.sampleid=s.sampleid
Where r.param =”00054” or r.param=”00010”
Group by s.siteid, r.sampleid,s.DT_TM
Having Count(*)=2

This selects all of the sampleid that have those codes on the same date for a site..


Brian

That's close but not quite....but I only care about the Samples that have BOTH of those parameters on the same date (shown by the third SQL statement I posted)...I'd like to replicate the results of running those three separate SQL statements by only having to run one SQL statement. I created a table for each of the two parameters which respectively contained only Samples having that table's parameter, and then I made a selection by joining those tables based via the Sample_ID where the DT_TM from one matched the DT_TM from the other. I want to do this all in one w/o having to make the interim tables.

thanks
Tom

 

[Brianwarnock]

I thought that the Count(*) =2 would mean that you only selected a Group Site/sampleid/date that had both, Ah! sampleids will be unique!, remove this from the Select and Group and you should get the Site and Date, this can be joined back to your tables , with the params to get the rest of the data, but doubt that you can achieve your goals with one query, unless George's works.

Using more than one query is not unusual, but temp tables is the killer.

Brian