Page 1 of 1

fetching unrelated rows in JOIN

Posted: Thu Feb 05, 2009 7:53 am
by kendall
hey guys,

I have a table with a set of 2 groups of information. e.g.
table: field1 field2
apple 2
bat 1
pear 2
ball 1
The data is unrelated however I need to fetch a pair of 1 from each group as a row. thus

[sql]SELECT a.field1, b.field1 FROM TABLE AS a LEFT JOIN TABLE AS b ON b.field2 = 2 WHERE a.field2 = 1[/sql]

NOW if i was to put LIMIT 1 this would have been cool. However for a limit of lets say 5 I would only get 1 result from the JOIN being REPEATED thus
bat apple....bat pear
WHAT do I need to do in order to get
bat apple.....ball pear

Re: fetching unrelated rows in JOIN

Posted: Thu Feb 05, 2009 8:50 am
by VladSun
So you need to transform this table data:

Code: Select all

x1
y1
x2
y2
x3
y3
into

Code: Select all

x1, y1
x2, y2
x3, y3
right?

Re:[SOLVED] fetching unrelated rows in JOIN

Posted: Thu Feb 05, 2009 9:00 am
by kendall
actually this worked...

[sql]SELECT a.field1 AS fruit, (SELECT b.field1 FROM TABLE AS b WHERE b.field2 = 2 ORDER BY RAND() LIMIT 1) AS sport FROM TABLE AS a WHERE a.field2 = 1 LIMIT 20[/sql]