>>>>Hi,
>>>>
>>>>I forgot again how to create query that contains group of records of parent table and then details of child. I think this is done with either subquery or derived query but I can't remember which one and how.
>>>>
>>>>Here is an example.
>>>>Table 1 parent with columns FLD1, FLD2
>>>>
>>>>Table 2 is a child table with a FK column FLD1 (matching FLD1 in Table1)
>>>>
>>>>I want to create a query that groups by Table 1 FLD1, FLD2 and contains all columns of table Table2
>>>>
>>>>For example,
>>>>
>>>>
>>>>select Table1.FLD1, Table1.FLD2 -- add all columns from Table2 -- from Table2 group by Table1.FLD1, Table1.FLD2
>>>>
>>>>
>>>>TIA.
>>>
>>>Then all fields from Table2 should be in aggregate functions.
>>>But if you give me an example data and desired result it will be easier for me to answer :-)
>>
>>When you are saying aggregated functions it would be MAX(FldA), MAX(FldB), and so on, right?
>>
>>Here is an example.
>>
>>Table SCHEDULE. has column EQ_ID (equipment ID), EMPL_ID (employee ID) and a bunch of other not important columns.
>>
>>Table EQUIPMENT has column EQ_ID, BUILDING, TYPE, and a bunch of other important columns.
>>
>>I want the resulting query to have
>>
>>EQ_ID, EMPL_ID, EQUIPMENT.BUILDING, EQUIPMENT, TYPE, .. and so on GROUP BY SCHEDULE.EMPL_ID, EQ_ID.
>>
>>That is, I want to see detailed information for each EMPLOYEE. Hopefully my explanation is clear. Thank you.
>
>
>The you don't need a GROUP you need ORDER?
>
>SELECT SCHEDULE.EMPL_ID,
> EQUIPMENT.*
>FROM SCHEDULE
>INNER JOIN EQUIPMENT ON EQUIPMENT.EQ_ID = SCHEDULE.EQ_ID
>ORDER BY SCHEDULE.EMPL_ID
>
Maybe I didn't clarify it correctly (sorry). The SCHEDULE table has records that have the same EMPL_ID and EQ_ID and they should be grouped. here is an example of SCHEDULE
EMPL_ID EQ_ID
ABC 001
ABC 001
ABC 001
CBS 002
CBS 002
NBC 003
NBC 003
The difference between these records (that have the same EMPL_ID and EQ_ID) is in other columns. But for this purpose I need to group so that only one record for a particular EMPL_ID and EQ_ID is displayed.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham