Why oh why SQL do you mock me?, please help

bobbytables

Lets say I have two tables {A, B}

A = {pk, fieldA, fieldB, etc}
B = {pk, A.pk, field}

The second column in table B being a foreign key to the primary key of table A.

What I'm trying to get is a result set with the following

A.pk, A.fieldA, (single random selection from B.field)

MySQL DB btw.

I really appreciate any help you can give me with this, as I am having a serious brain mush day.

Sparks

What, you mean like this?

SELECT A.pk, A.fieldA, B.field from A LEFT JOIN B ON A.pk = B.A.pk

bobbytables

Problem sorted, thank you Sparks for your help.

/scratches beard, now what the hell am I going to have for lunch?

jimmycrackcorm

Except you'll get multiple results.

If you want a random result then you're going to make it more complicated by also including a rowId as the subquery and selecting the top 1 where rowId >= random( count of the result set)

sigh....

Sparks

I thought when he said single random selection from B's fields, he just meant any field rather than an actually random() selection...