Help needed building a query

Databases
1

Hi all,

Im not very experienced with SQL so please bare with me on this one.

I have written a query which performs a search for my users, and works well:


SELECT mytable.pk, permission, venue, country, date, starttime, endtime, 
SUM( quantity ) AS totalSpeciesQty, SUM( weight ) AS totalSpeciesWeight 
FROM mytable INNER JOIN species 
ON mytable.pk = species.mytableFK 
WHERE venue LIKE '%%' 
AND country = 'United Kingdom'
AND (mytable.permission = 'public' OR mytable.username = 'foobar')
GROUP BY mytable.pk 
ORDER BY date DESC, 
starttime DESC LIMIT 0, 10

The main table (mytable) is structured as follows:


pk 	recordType 	venue 	date 	starttime 	endtime 	permit 	weather 	region 	country 	permission 	username 	zoom
10 	Coarse 	roughmoor 	2010-01-03 	08:00:00 	06:00:00 	Day Licence 	Sunny 	South West 	United Kingdom 	friends 	foobar 	18
9 	Coarse 	French Weir 	2010-01-03 	02:00:00 	03:00:00 	Day Licence 	Sunny 	South West 	United Kingdom 	private 	foobar 	18
8 	Coarse 	French Weir 	2010-01-03 	02:00:00 	02:00:00 	Day Licence 	Sunny 	South West 	United Kingdom 	private 	mattius1981 	18
6 	Fly 	Burton Springs Fishery 	2010-01-01 	02:00:00 	05:00:00 	Day Licence 	Sunny 	South West 	United Kingdom 	public 	foobar 	18
7 	Fly 	Burton Springs Fishery 	2010-01-03 	03:00:00 	04:00:00 	Day Licence 	Sunny 	South West 	United Kingdom 	public 	foobar 	18

Currently results are only returned for “public” records, or records owned by the user (as originally intended).

Where it gets tricky for me, is that I would also like results returned created by “friends” of the user.

I have an intermediary table which maps friends something like the following:



username_FK 	friend_username_FK        
foobar 	        user_a 	
foobar 	        user_b 	
foobar 	        user_c

My question is, how would I get records marked “friends only” as per the permission field in the main table?

I hope that made sense :slight_smile:

2

Perhaps it could be done as a separate query, added as recommendations
where instead of
OR mytable.username = ‘foobar’
you would have
OR mytable.username in (SELECT friend_username_FK FROM otherTable WHERE username_FK =‘foobar’)

I think.

3

scrap that, I got it !


SELECT mytable.pk, permission, venue, country, date, starttime, endtime,
SUM( quantity ) AS totalSpeciesQty, SUM( weight ) AS totalSpeciesWeight
FROM mytable INNER JOIN species
ON mytable.pk = species.mytableFK
WHERE venue LIKE '%%'
AND country = 'United Kingdom'
AND (mytable.permission = 'public' OR mytable.username in (SELECT friend_username_FK FROM friend_map_friend WHERE username_FK = 'foobar'))
AND mytable.permission != 'private'
OR mytable.username = 'foobar'
GROUP BY mytable.pk
ORDER BY date DESC,
starttime DESC LIMIT 0, 10
4

hey Dr John,

thanks so much for your kind help.

Looks like with your mod its almost there, only im getting my friends private records as well, which I dont want. :slight_smile:

The mySQL so far:


SELECT mytable.pk, permission, venue, country, date, starttime, endtime,
SUM( quantity ) AS totalSpeciesQty, SUM( weight ) AS totalSpeciesWeight
FROM mytable INNER JOIN species
ON mytable.pk = species.mytableFK
WHERE venue LIKE '%%'
AND country = 'United Kingdom'
AND mytable.permission = 'public'
OR (mytable.username in (SELECT friend_username_FK FROM friend_map_friend WHERE username_FK = 'foobar')
OR mytable.username = 'foobar')
GROUP BY mytable.pk
ORDER BY date DESC,
starttime DESC LIMIT 0, 10
5

thats really great advice, thankyou david :slight_smile:

6

That will work, but if you add a different permission, you’ll have to go back in and filter them out as well.

If you do it this way, you’re filtering for exact values (which is optimized a little faster than filtering out result sets so as your amount of data increases, the speed difference will be more noticeable). I also used some more parenthesis to hopefully make the conditions a little clearer (so you’re not going back in six months and going “now why in the world did I do that?”)


SELECT f.pk
 , permission
 , venue
 , country
 , DATE
 , starttime
 , endtime
 , SUM(quantity) AS totalSpeciesQty
 , SUM(weight) AS totalSpeciesWeight
 FROM mytable f
INNER JOIN species s ON f.pk = s.mytableFK
WHERE venue LIKE '%%'
 AND country = 'United Kingdom'
 AND (f.username = 'foobar' OR
     (f.permission = 'public' OR   
   (f.permission = 'friend' and 
    f.username IN (SELECT friend_username_FK FROM friend_map_friend WHERE username_FK = 'foobar')))
GROUP BY f.pk
ORDER BY DATE DESC, starttime DESC 
LIMIT 0, 10