Select Random Row from Inner Join

[peteyb]

Hi All

I have the following sql statement that returns a single row from an inner join. The problem is that it always returns the first row it finds from table2. Is there a way to return a random row from table2?


$query = "SELECT * FROM table1
INNER JOIN table2
ON table1.linkField=LEFT(table2.aField, 2)
WHERE menu='abc'
GROUP BY table1.linkField
ORDER BY table1.id";

[svidgen]

Are you using MySQL (or some other DBMS that supports RAND())?
ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:

mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1;

[peteyb]

I am using MySQL.

This only brings back one row.

This just got a little more complicated as the spec has now changed.

Is it possible to link table 2 and table 3 to table1 and the results return
A) All rows from table1,
B) Only one random row from table 2, and
C) All rows from table3?

Any help appreciated.

[svidgen]

I'm not entirely sure what kind of results you want back. What you're describing sounds like it could be done with 3 separate queries, and therefor a UNION. Am I misunderstanding?

[peteyb]

If i send you a link to the webpage and then explain.

http://www.martinluck.co.uk

On the left hand side there is the navigation menu, this is table1 data.

When you move your mouse over them a popup appears which contains an image, one specific product, which is table 2, and then under this a list of more details products under the inital product i.e. table 3.


$query = "SELECT * FROM www_navMenus
INNER JOIN www_topProducts
ON www_navMenus.toolTipPac1=LEFT(www_topProducts.pac2Link, 2)
INNER JOIN www_bossGroup2
ON www_navMenus.toolTipPac1=www_bossGroup2.pac1
WHERE menu='$link'
GROUP BY www_navMenus.toolTipPac1
ORDER BY navId";

[peteyb]

Hi All

Any ideas?

Instead of link the three tables, could I link table1 and table2, as per my original log and then whilst looping round the returned results do a subquery with the returned values from the first query?

[svidgen]

I'm still having a bit of a tough time understanding exactly what you're trying to do ...

Let me throw some statements out there about your data. You can tell me if I understand the schema at all:
table1 and table2 are related by an ID on a one-to-one basis.
table2 and table3 are related by an ID on a one-to-many basis.

And now, in regards to the page:
Each menu item corresponds to, and needs to display, data from a single row in table1, a single row in table2, and multiple rows in table3.

Are those [bolded] statements correct?

[peteyb]

Not quite.

1. table1 and table2 are related 1-Many
2. table1 and table3 are related 1-Many

3. Each menu item needs to display many rows from table1, one row at random from table2 and many rows from table3.

Is this possible?

[svidgen]

Sure, but you'll want to do this in two queries (or a UNION), depending on how you need to display your results. So, in UNION form, what you want will probably look something like this:
(
select
*
from
table1 as a
left join
table2 as b using (a.id=b.a_id)
where
a.colX='something'
) UNION (
select
*
from
table1 as a
left join
table3 as c using (a.id=c.a_id)
where
a.colX='something'
order by
rand() limit 1
)

[peteyb]

Hi

This looks like what I need but it is returning an mysql error

My Actual Code:


$query = "(
SELECT * FROM www_navMenus AS a
LEFT JOIN www_topProducts AS b USING (a.toolTipPac1=LEFT(b.pac2Link, 2))
WHERE a.menu='$link'
)
UNION
(
SELECT * FROM www_navMenus AS a
LEFT JOIN www_bossGroup2 AS c USING (a.toolTipPac1=c.pac1)
WHERE a.menu='$link'
ORDER BY
rand() LIMIT 1
)";

[peteyb]

I am doing the first query between table1 and table2 and then after some of the php/html I am doing the second query between table1 and table3. Using the Group By command to restrict the returned results accordingly.

Now, back to my initial thread, how can i bring back a single random result from table2 but still bring back all results from table1?

[peteyb]

not to worry, i have done it as three queries, the second and third sitting inside the while loop of the first and using variables to give me my wanted results.