help with sql query ?

Question

Widget Table: serial, name, description
Attributes Table: WidgetSerial, color

I have a table of widgets with unique serial numbers. I have another table with attribute information for widgets, but each widget can have multiple attributes.

How can i get a list of all widgets to include one field from one record in the attributes table. I am currently doing a JOIN but since there are multiple attributes for each widget, I get widgets listed multiple times.

So I am stuck. Do I need a union or something?

Answer 1

Since there is a 1:many relationship between a widget and it's attributes, you need a way to identify which attribute should be selected.



This would work, if max(color) gave you the row you wanted.



select WdigetSerial , name, max(color)

from Attributes a inner join Widget b on a.WidgetSerial = b.Serial

group by WidgetSerial, name

Answer 2

you probably need a subquery to select a distinct set of widgets from the attributes table, or you have to change your JOIN criteria so there is a unique join between the tables.



So in other words you only join against the attributes table on a single attribute. But it is much more likely you need a subquery.

Answer 3

I think what you are looking for is the group_concat function. Try this:



select widget.serial, widget.name, widget.description,

group_concat(attributes.color)

from widget, attributes

where widget.serial=attributes.WidgetSerial

group by widget.serial



This will return one row in your query set for each row in the widget table. If there are multiple rows in the attribute table for a particular widgetserial, then these will be grouped in the result set.