0

I am trying to get a person's groups baseed on whether they are a member, invited, host, admin, mod or requested to join on MYSQL.

I have tried the query below:

 var qSelect = "SELECT DISTINCT g.id, g.title, media.media_link, g_host.member_type, g_admins.member_type, g_mods.member_type, g_members.member_type, g_invites.member_type, g_requests.member_type FROM Groups g, Media media, Group_Hosts g_host, Group_Admins g_admins, Group_Moderators g_mods, Group_Members g_members, Group_Invites g_invites, Group_Requests g_requests" +
                "WHERE media.group_id = g.id AND " +
                "((g_host.user_id = ? AND g_host.group_id = g.id) OR " +
                "((g_admins.user_id = ? AND g_admins.group_id = g.id) OR " +
                "((g_mods.user_id = ? AND g_mods.group_id = g.id) OR " +
                "((g_members.user_id = ? AND g_members.group_id = g.id) OR " +
                "((g_invites.user_id = ? AND g_invites.group_id = g.id) OR " +
                "((g_requests.user_id = ? AND g_requests.group_id = g.id))" +
                "ORDER BY g.id DESC";

But get the following error:

get my groups error: Error: ER_PARSE_ERROR: 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 ' Media media, Group_Hosts g_host, Group_Admins g_admins, Group_Moderators g_mods' at line 1

I am using NodeJs.

Appreciate any help or direction.

Also, if the design of the query is going to be slow or bad in performance, please let me know. I am expecting there to be over 100k rows in each of these tables, but each user should have only about 10 in each.

Leo
  • 23
  • 1
  • 5
  • I think you're trying to do a `JOIN` here.. That's not the way to join or even comma-join.. but it's also seem to be too much of a condition. Have you tried the plain query directly from db? Maybe using tools like SQLyog, HeidiSQL etc.? – FanoFN May 23 '20 at 01:51
  • All in all there are 8 tables that I can count in there. I think you should start by building the plain query first and only convert it to nodejs query when you already figure out the exact query operation that you want. – FanoFN May 23 '20 at 01:56
  • Group is a reserved word... – Shadow May 23 '20 at 02:04
  • I changed it @Shadow to g instead – Leo May 23 '20 at 03:13
  • Please update your question to current information. – Wilson Hauck May 24 '20 at 13:14

1 Answers1

1

I couldn't post this as comment.. so try this

SELECT 
    DISTINCT 
    group.id
    ,group.title
    ,media.media_link
    ,g_host.member_type
    ,g_admins.member_type
    ,g_mods.member_type
    ,g_members.member_type
    ,g_invites.member_type
    ,g_requests.member_type 
FROM 
    Groups group
    LEFT JOIN (select * from Media where user_id = ?) media on media.group_id = group.id
    LEFT JOIN (select * from Group_Hosts where user_id = ?) g_host on g_host.group_id = group.id
    LEFT JOIN (select * from Group_Admins where user_id = ?) g_admins on g_admins.group_id = group.id
    LEFT JOIN (select * from Group_Moderators where user_id = ?) g_mods on g_mods.group_id = group.id
    LEFT JOIN (select * from Group_Members where user_id = ?) g_members on g_members.group_id = group.id
    LEFT JOIN (select * from Group_Invites where user_id = ?) g_invites on g_invites.group_id = group.id
    LEFT JOIN (select * from Group_Requests where user_id = ?) g_requests on g_requests.group_id = group.id
ORDER BY group.id DESC;
Ganesh Chandrasekaran
  • 1,578
  • 12
  • 17
  • I get this error: get my groups error: Error: ER_PARSE_ERROR: 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 'g LEFT JOIN (SELECT * FROM Media WHERE media.group_id = g.id)LEFT JOIN (SELECT *' at line 1 – Leo May 23 '20 at 03:13
  • group is keyword.. g.id ,g.title from groups g – Ganesh Chandrasekaran May 23 '20 at 03:14
  • I know I switched everything to g from group – Leo May 23 '20 at 03:16