Select Multiple Instances of a related field

[cactus1000]

Is it possible to write an SQL statement to export mutliple instances of a related field in a single record?

For example, ContactsTable is related to ContactHistoryTable. I want to select the contact record and export 3 columns from the ContactHistory records containing the most recent ContactHistory.Type = Phone, most recent ContactHistory.Type = Appointemnt and ContactHistory.Type = Action

 

[KarveR]

I presume they have a common element in each table , something to JOIN them on ...

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[cactus1000]

Yes, there is a common Contact ID.
My question is how to include data from the same field in the related table in multiple columns on the report. Related record with certain search parameters in col 1
Related record with different search parameters in col 2
etc...

 

[KarveR]

see join posted above.
or:
select a.stuff, b.stuff,b,other,b.somethingelse from tableA a, tableb b where a.commonthing = b.commonthing AND ....;

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[cactus1000]

I appreciate your help. Maybe I don't fully understand the JOIN command. Though I do understand the other syntax.
My problem is not getting related data from the second table, it is exporting multiple columns that contain data from the SAME field in the second table.
For Tables A and B, with B the Many Table to Table A
if I have A.commonthing = B.commonthing
and other fields A.name, A.company, B.ondate, B.x
I want to export 5 columns for each A.commonthing:
1. A.name
2. A.company
3. B.ondate with most recent date for when B.x=1
4. B.ondate with most recent date for when B.x=2
5. B.ondate with most recent date for when B.x=3

 

[r937]

multiple columns in output: very hard (especially if the number of columns is unknown)

single column in output with comma-delimited list of multiple values: trivially easy

see the GROUP_CONCAT function

r937.com | rudy.ca