>>>>>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
>>
>
>let me add more to my previous example:
>
>
>EMPL_ID EQ_ID
>ABC 001
>ABC 001
>ABC 001
>ABC 003
>ABC 002
>ABC 002
>CBS 002
>CBS 002
>CBS 001
>CBS 001
>NBC 003
>NBC 003
>
Try:
SELECT SCHEDULE.EMPL_ID,
EQUIPMENT.*
FROM (SELECT DISTINCT EMPL_ID, EQ_ID FROM SCHEDULE) SCHEDULE
INNER JOIN EQUIPMENT ON EQUIPMENT.EQ_ID = SCHEDULE.EQ_ID
ORDER BY SCHEDULE.EMPL_ID
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.