Combine two tables

[WaveOut]

I have two tables that contain the following fields:

Commodities Table
ID
Company_ID
Type


Sub_Commodities Table
ID
Commodities_ID
Sub_Type

I need a sql statment that will join these two tables together and group them by Commodities.type.

Thanks,
David

 

[jbenson001]

The grouping depends on what columns you want to select and if you are going to use an aggregate function. what columns do you want for your output?

 

[WaveOut]

I would like to have the columns Type and Sub_Type.

 

[jbenson001]

Here is the code, however I don't think you are going to get the expected results.

Select c.Type, s.Sub_Type
From Commodities c
   Inner Join Sub_Commodities s ON
      c.Id = s.Commodities_ID

 

[WaveOut]

Let me restate what I said:

I would like to have the columns Type and Sub_Type and group it by Type

Thanks,
David

 

[jbenson001]

You cannot group by just Type if you want to see both columns, you would need some aggregate funtion in the select. A group by clause needs to contain all columns in the select except for aggregate functions. The above code would then need to be:
[code
Select c.Type, s.Sub_Type
From Commodities c
Inner Join Sub_Commodities s ON
c.Id = s.Commodities_ID
Group by c.Type, s.Sub_Type
[/code]

 

[WaveOut]

jbenson001,
Thanks for the help. I used the following sql code:

SELECT     c.Type, s.Sub_Type
FROM         dbo.Commodities c INNER JOIN
                      dbo.Sub_Commodities s ON c.ID = s.Commoditie_ID
WHERE     (c.Company_ID LIKE '55')
GROUP BY c.Type, s.Sub_Type

It returned something like this:

Bus Metal enclosed Non-Seg Phased Bus
Bus Metal enclosed Segregated Phase Bus

Pumps Oil Pumps
Pumps Water Pumps
Pumps Fuel Pumps

What I'm needing is something like this:

Bus Metal enclosed Non-Seg Phased Bus
Metal enclosed Segregated Phase Bus

Pumps Oil Pumps
Water Pumps
Fuel Pumps

Notice the Type only appears once. Maybe I'm going about this in the wrong way.

Thanks,
David

 

[jbenson001]

In sql thats what you will get, the type for each sub_type combination. I don't know a way to eliminate it using SQL. You can use a 3rd party reporting tool such as Crystal Reports.