mysql - Add additional joins to already joined table in SQL -



mysql - Add additional joins to already joined table in SQL -

i have existing query using joins (thanks radar) data.

working sql select ifnull(f.field_full_name_value, 'no value'), u.name, u.uid, n.title, n.nid, a.timestamp, d.field_video_duration_value duration db_node_view_count bring together db_node n on a.nid = n.nid bring together db_field_data_field_video_duration d on n.nid = d.entity_id bring together db_users u on a.uid = u.uid , u.uid <> 1 left bring together db_field_data_field_full_name f on u.uid = f.entity_id order u.uid desc

what want want extend query show roles of db_users u. there table called db_roles, contain role names primary key rid. sec table db_users_roles, contains matching uid (from db_users u) , rid show user selected role.

so did under on a.uid = u.uid, added join db_users_roles ur on u.uid = ur.uid bring together db_role r on ur.rid = r.rid. works fine shows duplicate rows. thought why it's happening?

select ifnull(f.field_full_name_value, 'no value'), r.name, u.name, u.uid, n.title, n.nid, a.timestamp, d.field_video_duration_value duration db_node_view_count bring together db_node n on a.nid = n.nid bring together db_field_data_field_video_duration d on n.nid = d.entity_id bring together db_users u on a.uid = u.uid left bring together db_users_roles ur on u.uid = ur.uid left bring together db_role r on ur.rid = r.rid , u.uid <> 1 left bring together db_field_data_field_full_name f on u.uid = f.entity_id order u.uid desc update

with help joe, here slight update:

select ifnull(f.field_full_name_value, 'no value'), group_concat(r.name), u.name, u.uid, n.title, n.nid, a.timestamp, d.field_video_duration_value duration db_node_view_count bring together db_node n on a.nid = n.nid bring together db_field_data_field_video_duration d on n.nid = d.entity_id bring together db_users u on a.uid = u.uid left bring together db_users_roles ur on u.uid = ur.uid left bring together db_role r on ur.rid = r.rid , u.uid <> 1 left bring together db_field_data_field_full_name f on u.uid = f.entity_id grouping f.field_full_name_value order u.uid desc

some users in (drupal) database may have more 1 role. original query had (simplified bit) 1 row per node, modified query duplicate rows each role of user.

you might want modify query include grouping n.nid, u.uid , alter select field list include group_concat(r.name) rather r.name.

mysql join

Comments

Post a Comment

Popular posts from this blog

java Multi query from Mysql using netbeans -

java Multi query from Mysql using netbeans - i have java code retrieve info mysql appropriate code in netbeans.the code below. want multi query such illustration finding total number of products , on.could help me please. in advance import java.sql.*; public class javamysqlselectexample { public static void main(string[] args) { seek { string mydriver = "com.mysql.jdbc.driver"; string myurl = "jdbc:mysql://localhost/sample"; class.forname(mydriver); connection conn = drivermanager.getconnection(myurl, "root", "mypasscode1"); string query = "select * products"; statement st = conn.createstatement(); resultset rs = st.executequery(query); while (rs.next()==true) { int code = rs.getint("code"); string productname = rs.getstring("product_name"); string producttype = rs.getstring("product_type"); system.out.format(" %d, %s, %s\n", code, pr
Read more

c# - DotNetZip fails with "stream does not support seek operations" -

c# - DotNetZip fails with "stream does not support seek operations" - i using dotnetzip in c# unzip stream follows: public static void unzipfromstream(stream stream, string outdir) { //omit seek grab block using (zipfile zip = zipfile.read(stream)){ foreach (zipentry e in zip){ e.extract(outdir, extractexistingfileaction.overwritesilently); } } } stream obtained using webclient client = new webclient(); stream fs = client.openread(url); however, got next exception exception during extracting zip stream system.notsupportedexception: stream not back upwards seek operations. @ system.net.connectstream.get_position() @ ionic.zip.zipfile.read(stream zipstream, textwriter statusmessagewriter, encoding encoding, eventhandler`1 readprogress) on server side(asp.net mvc 4), returning filepathresult or filestreamresult both caused exception. should obtain stream differently on client side? or how create server homec
Read more

c++ - StartServiceCtrlDispatcher don't can access 1063 error -

c++ - StartServiceCtrlDispatcher don't can access 1063 error - i write on c++ service , have error, , don't can fixed it. my service main function int main (int argc, tchar *argv[]) {dword f; for(int i=0;i<113;i++) f = getlasterror(); servicepath = lptstr(argv[0]); outputdebugstring(_t("my sample service: main: entry")); //installservice(); service_table_entry servicetable[] = { {service_name, (lpservice_main_function) servicemain}, {null, null} }; // startservice(); if(!startservicectrldispatcher(servicetable)) { f = getlasterror(); //addlogmessage("error: startservicectrldispatcher"); } else if( memcmp(argv[argc-1],"install",7)) { installservice(); } else if( memcmp(argv[argc-1],"remove",6)) { removeservice(); } else if( memcmp(argv[argc-1],"start",5)) { startservice(); } else if( memcmp(argv[argc-1],"stop",4))
Read more