Left Join

[gerkintrigg]

Hi.
I wondered how to do an SQL left join with a normal right join. what's the protocol for that?

I need to do a standard query (which works) and then have a OR command containing the SQL equivalent of "people in the members table, who don't have anything uploaded to the pictures table" but I dunno how to do it.

Any suggestions?

I have looked at previous Left join examples (as I think that's what's needed) but none of them work.

[utexas_pjm]

If I understand you correctly, you want to use a an outer join like this:

[code]
SELECT m.* FROM members AS m
    LEFT OUTER JOIN uploads as u USING (userid)
    WHERE uploadname IS NULL
[/code]

Best,

Patrick

[gerkintrigg]

Patrick,
That certainly sounds much more promising than my previous solutions! ;o) I'll give it a go and let you know if it works.
Thanks a lot!

Neil

[gerkintrigg]

I'm trying to select all members that have an activated picture and also all members that have not uploaded a picture, but not members who have uploaded a picture and not had it activated.

I'm using this code:
[code]$sql= "SELECT pictures.user_id,
pictures.id,
pictures.url
FROM pictures, member
WHERE (member.id=pictures.user_id
&& pictures.active='y'
&& pictures.primary='y'
".$preferences.") [/code]
That bit's working fine...
Then I need to add something that says "and members who've not uploaded a picture".

Ideally in the same query.

Thanks for the suggestion, but I don't know how to do both.

[shoz]

[code]
SELECT
members.id, pictures.id, pictures.url
FROM
member
LEFT JOIN
pictures
ON
member.id = pictures.user_id
WHERE
(pictures.active='y' && pictures.primary='y')
OR
(pictures.user_id IS NULL)
$preferences
[/code]

I'm assuming that $preferences doesn't have anything to do with the WHERE clause.

[gerkintrigg]

actually $preferences was part of the where clause...
My SQL statement now looks like this (after processing the preferences variable):

[code]SELECT
member.id, pictures.id, pictures.url
FROM
member
LEFT JOIN
pictures ON member.id = pictures.user_id
WHERE
(pictures.active='y'
&& pictures.primary='y'
&&(member.sex='f')
&&(member.orientation='straight')
&& (member.age BETWEEN 26 AND 32)
&& (member.area='England-Southwest'))
OR (pictures.url IS NULL)[/code]

There seems to be an error, but I'm not too great at SQL. URGH...
thanks for your help so far though

[shoz]

[quote author=gerkintrigg link=topic=124335.msg515413#msg515413 date=1169997488]
actually $preferences was part of the where clause...
My SQL statement now looks like this (after processing the preferences variable):

[code]SELECT
member.id, pictures.id, pictures.url
FROM
member
LEFT JOIN
pictures ON member.id = pictures.user_id
WHERE
(pictures.active='y'
&& pictures.primary='y'
&&(member.sex='f')
&&(member.orientation='straight')
&& (member.age BETWEEN 26 AND 32)
&& (member.area='England-Southwest'))
OR (pictures.url IS NULL)[/code]

There seems to be an error, but I'm not too great at SQL. URGH...
thanks for your help so far though
[/quote]

Looks like a "(" is missing from the start of the clause but after seeing what $preferences contains (ie limits based on the members table) the following should be what you're looking for.

[code]
SELECT
members.id, pictures.id, pictures.url
FROM
member
LEFT JOIN
pictures
ON
member.id = pictures.user_id
WHERE
($preferences)
&&
(
    (pictures.active='y' && pictures.primary='y')
    OR
    (pictures.user_id IS NULL)
)
[/code]

Post the error messages  if you get any.

[gerkintrigg]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') && ( (pictures.active='y' && pictures.primary='y') OR

the problem is that the preferences variable can be empty, depending on whether users set their preferences or not.

[shoz]

A quick fix would be to test if the variable contains anything and only add it to the WHERE clause if it does.
[code]
$query = 'SELECT
members.id, pictures.id, pictures.url
FROM
member
LEFT JOIN
pictures
ON
member.id = pictures.user_id
WHERE ';
if (isset($preferences) && $preferences != '')
{
    $query .= "($preferences) && ";

}
$query .= '(
    (pictures.active='y' && pictures.primary='y')
    OR
    (pictures.user_id IS NULL)
) ';
[/code]

[gerkintrigg]

Sorry, still a no-go... just returns and empty result set...

[shoz]

Post the query string after all variables have been expanded.