AND OR in the same statement

[SimonIoa]

Hello i have a mysql statement and i want to add more parameters to get different results.

SELECT DISTINCT M.message,F.friend_one,F.friend_two, F.role,U.uid,U.username
    FROM users U,friends F, messages M WHERE ( U.status='1' AND F.friend_one = '88') OR (F.friend_two='88' ) AND F.role='friend'

88 is the users id so i want to show M.message from friends of 88 but not 88 itself
hints:
when one is becoming friend with other friend_one is 88 -for example- and friend_two another user id or vice versa
M table has id of user as a column of course

[jkon]

I am not sure I have understood the question but I will give it a try. Your data structure is a bit weird especially in the friends association tables , then you didn't provide the column name in messages table for the user id I will suppose it is user_id

First lets find the friends of user that has id 88

SELECT a.friend_one AS user_id FROM friends AS a WHERE a.friend_two = 88 AND a.role = 'friend' UNION b.friend_two AS user_id FROM friends AS b WHERE b.friend_one = 88 AND b.role = 'friend'

Then lets get the messages

SELECT d.user_id,d.message FROM (SELECT a.friend_one AS user_id FROM friends AS a WHERE a.friend_two = 88 AND a.role = 'friend' UNION b.friend_two AS user_id FROM friends AS b WHERE b.friend_one = 88 AND b.role = 'friend') AS c LEFT JOIN messages AS d ON d.user_id = c.user_id

And finally lets get the username

SELECT e.user_id,e.message,f.username FROM (SELECT d.user_id,d.message FROM (SELECT a.friend_one AS user_id FROM friends AS a WHERE a.friend_two = 88 AND a.role = 'friend' UNION b.friend_two AS user_id FROM friends AS b WHERE b.friend_one = 88 AND b.role = 'friend') AS c LEFT JOIN messages AS d ON d.user_id = c.user_id) AS e LEFT JOIN users AS f ON f.uid = e.user_id

I don't have the tables to test it but logically if I haven't misspelled anything should work

(I have try to have mysql in inline code but didn't worked)

[SimonIoa]

thanks for your reply the messages user_id is uid_fk. I am trying it on my db. Thats the solution i am sure but i am confused about user_id . its fri not friend

SELECT e.uid_fk,e.message,f.username FROM (SELECT d.uid_fk,d.message FROM (SELECT a.friend_one AS uid_fk FROM friends AS a WHERE a.friend_two = '99' AND a.role = 'fri' UNION b.friend_two AS uid_fk FROM friends AS b WHERE b.friend_one = '99' AND b.role = 'fri') AS c LEFT JOIN messages AS d ON d.uid_fk = c.uid_fk) AS e LEFT JOIN users AS f ON f.uid = e.uid_fk;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'b.friend_two AS uid_fk FROM friends AS b WHERE b.friend_one = '99' AND b.role = ' at line 1

[jkon]

I forgot to enter SELECT after the UNION

so it is:

SELECT e.uid_fk,e.message,f.username FROM (SELECT d.uid_fk,d.message FROM (SELECT a.friend_one AS uid_fk FROM friends AS a WHERE a.friend_two = '99' AND a.role = 'fri' UNION SELECT b.friend_two AS uid_fk FROM friends AS b WHERE b.friend_one = '99' AND b.role = 'fri') AS c LEFT JOIN messages AS d ON d.uid_fk = c.uid_fk) AS e LEFT JOIN users AS f ON f.uid = e.uid_fk;

[SimonIoa]

Yeah thats the solution. Thanks jkon!

[SeanDevoy]

It is hard to answer without knowing the table designs. But, assuming Messages has a column "user" which matches friend_one or friend_two in the friends table and there is a primary key on the friends table, it seems to me this would be a little cleaner approach:

select F1.friend_two, F2.friend_one, m.message
From Friends as F1
join Friends as F2 on F1.primary_key = F2.primary_key
join Messages as M on m.user=F1.friend_two or m.user=f2.friend_one
where F1.friend_one=88 or F2.friend_two=88 and f1.role='friend'

That assumes Friends is like:
primary_key int,
friend_one varchar(32),
friend_two varchar(32)

and

messages is like
primary_key int,
user varchar(32),
message as varchar(1000)
and other stuff like submission date.