DB networking with indefinite variables

[jackr1909]

Hi everyone,

 

i have a 2 tables, one of which contains a list of friends. My username = jack. Any number of those records may or may not contain my username jack in one of two rows (adder or addee). I want to select the highest, second highest, etc (all in seperate query's), id from the other table (links) that was posted by a username (column 'postedby') that is on the same mysql row as my name (in either the adder or the addee columns) and the column (in the table friends 'approved' = yes.

 

Any help would be greatly appreciated, i am a n00b at mysql-networking.

 

Thanks,

 

Jack

[kickstart]

Hi

 

Brief play. Something like this will get you the latest posts by others you are friends with (not tested so excuse any typos):-

 

SELECT *
FROM
(SELECT adder AS Person, addee AS Friend
FROM FriendsTable
WHERE approved = 'yes'
UNION
SELECT addee AS Person, adder AS Friend
FROM FriendsTable
WHERE approved = 'yes') PersonSubselect
INNER JOIN links
ON PersonSubselect.Friend = links.postedby
INNER JOIN (SELECT postedby, MAX(id) AS MaxId FROM links GROUP BY postedby) LinksSubselect
ON links.postedby = LinksSubselect.postedby AND links.id = LinksSubselect.MaxId
WHERE PersonSubselect.Person = 'jack'

 

Finding the 2nd highest, etc, is a bit more difficult.

 

All the best

 

Keith

[kickstart]

Hi

 

Bit more of a play. I have briefly tested this and it appears OK, but not 100% sure.

 

This is using a subselect to get a list of all the approved friends, irrespective of whether they are the adder or adde.

 

It then joins the result of that against another subselect. That subselect is using a variable to provide a sequence number of each persons posts in descending order (the set statements at the top initialise the variables required for this). The WHERE clause then narrows it dow to the person whose friends you are interested in and that it is the 2nd entry you care about (or 3rd, or 4th, etc).

 

set @Person = '';
set @num = 1;
SELECT *
FROM
(SELECT adder AS Person, addee AS Friend
FROM FriendsTable
WHERE approved = 'yes'
UNION
SELECT addee AS Person, adder AS Friend
FROM FriendsTable
WHERE approved = 'yes') PersonSubselect
INNER JOIN (SELECT postedby, id, @num := if(@Person = postedby, @num + 1, 1) AS PostedSequence, @Person := postedby AS dummy FROM links ORDER BY id DESC) LinksSubselect
ON PersonSubselect.Friend = LinksSubselect.postedby
WHERE PersonSubselect.Person = 'jack'
AND LinksSubselect.PostedSequence = 2

 

All the best

 

Keith

[jackr1909]

Thanks a lot, kickstart,

 

it works fantastically,

 

Jack