group by with multiple tables

[antonyx]

is it possible to use group by with multiple tables in a query..

exmaple


SELECT field1,field2,field3,field4,field5,field6 (dif fields from dif tables)
FROM table1,table2,table3
WHERE table1.field1=table2.field2
GROUP BY field1

something like this.
i havfe tried using the group by in this fasihon and am having problems

 

[JamesLean]

Yes, you can use columns from multiple tables. If you're having problems with a particular query can you post the details?

--James

 

[PHV]

You have to use an aggregate function for each column in the SELECT list not in the GROUP BY clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[antonyx]

ok let me post the query im tryin to get workin

SELECT CampaignName,caStartDate,clName,adType,adFinished
FROM Campaign,Client,Advert
WHERE Campaign.ClientName=Client.ClientId
ORDER BY CampaignName ASC;
GROUP BY CampaignName;

 

[PHV]

1) How is Advert related to Client or Campaign ?
2) Why using a GROUP BY clause if you don't use any aggregate function ?

Can you please post your tables schema, some input samples and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[antonyx]

ok ClientId is primary key of client table
CampaignId is primary key of campaign table and foreign key is ClientId. AdvertId is primary key in advert table and foreign key is CampaignId.

a campaign consists of many adverts
a client will have a campaign

i want to group the results by campaign, so the advert details can be separated by what campaign they are in.

 

[PHV]

Something like this ?
SELECT CampaignName,caStartDate,clName,adType,adFinished
FROM Campaign,Client,Advert
WHERE Campaign.ClientID=Client.ClientId
AND Campaign.CampaignID=Advert.CampaignID
ORDER BY CampaignName;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[antonyx]

ok thanks, i dont have oracle at the moment but ill try it 2moro lunch and post what happens. i know the order by will order them by the name, but will it actually group the results, with spaces in between the lists.

 

[PHV]

group the results, with spaces in between the lists
???
Can you please post some input samples and expected result ?
I'm afraid you're talking about a report layout, not a data retrieval issue.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244