is this possible with selects and joins?

**Bill Karwin** · Jul 23 '05, 06:51 AM

Re: is this possible with selects and joins?

gnog wrote:[color=blue]
> now i wanne generate a result like this:
>
> bugid | bugname | fieldname(1) | fieldname(2) | fieldname(3) | etc
> 1 | testbug | fieldvalue | fieldvalue | fieldvalue | etc[/color]

I don't think this is practical to do in the format you describe in the
general case.

To do this, you'd need to join the bugs table to the custom_fields table
once for each field, and I assume since they are custom fields that
there is potentially an unbounded number of them. Also, you don't
specify that every bugid has the same set of custom fields (one might
expect this to be the case, but I'm not assuming it based on your schema).

You'd need an extra outer join for _each_ custom fieldvalue, and another
join for the matching fieldname. You'd also need a way to ensure that
you don't get repeated fields from the subsequent joins.

SQL doesn't support multi-way joins when you can't predict how many
terms you have, or when you have a variable number of terms per bugid in
this case.

SQL also doesn't support dynamic column aliases, so you wouldn't be able
to use "AS" to label the v1.fieldvalue columns based on the strings
found in f1.fieldname.

The only suggestion I have is that you first query all the distinct
custom field id's, and then based on the result, write code in your
application to build a query specific to your set of custom fields.

SELECT b.bugid, b.bugname,
v1.fieldvalue AS 'foo',
v2.fieldvalue AS 'bar'
FROM bugs AS b
LEFT OUTER JOIN custom_field_va lue AS v1
ON (b.bugid = v1.bugid AND v1.fieldid = 123)
INNER JOIN custom_fields AS f1
on v1.fieldid = f1.fieldid;
LEFT OUTER JOIN custom_field_va lue AS v2
ON (b.bugid = v2.bugid AND v2.fieldid = 456)
INNER JOIN custom_fields AS f2
on v2.fieldid = f2.fieldid
....etc.

But note that there's a hard limit to the number of tables joined. I
think it's 32 on typical systems. So you can support a maximum of 15
custom fields using this method, since you also need to join with the
bugs table. In any case, it's not very scalable.

The way I would do this would be to fetch the data in rows:

SELECT b.bugid, b.bugname, f1.fieldname, v1.fieldvalue
FROM bugs AS b
LEFT OUTER JOIN custom_field_va lue AS v1
ON b.bugid = v1.bugid
INNER JOIN custom_fields AS f1
on v1.fieldid = f1.fieldid;

Then I'd write code in my application to filter through the result set
and reformat the data into a tabular arrangement before output.

Regards,
Bill K.

is this possible with selects and joins?

is this possible with selects and joins?

Comment