fetching unrelated rows in JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

fetching unrelated rows in JOIN

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: fetching unrelated rows in JOIN

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re:[SOLVED] fetching unrelated rows in JOIN

Post 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]
Post Reply