sql abfrage sortiert nach count in anderer tabelle

[Illuvatar]

Tach,

ich hab grade ein Problem mit einer SQL-Abfrage. Erstmal kurz zwei Beispiel-Tabellen:

jos_mt_links
[table:307a706a71]
[tr:307a706a71][td:307a706a71]link_id[/td:307a706a71][td:307a706a71]name[/td:307a706a71][/tr:307a706a71]
[tr:307a706a71][td:307a706a71]1[/td:307a706a71][td:307a706a71]link_1[/td:307a706a71][/tr:307a706a71]
[tr:307a706a71][td:307a706a71]2[/td:307a706a71][td:307a706a71]link_2[/td:307a706a71][/tr:307a706a71]
[tr:307a706a71][td:307a706a71]3[/td:307a706a71][td:307a706a71]link_3[/td:307a706a71][/tr:307a706a71]
[/table:307a706a71]
jos_mt_reviews
[table:307a706a71]
[tr:307a706a71][td:307a706a71]rev_id[/td:307a706a71][td:307a706a71]link_id[/td:307a706a71][td:307a706a71]text[/td:307a706a71][/tr:307a706a71]
[tr:307a706a71][td:307a706a71]1[/td:307a706a71][td:307a706a71]1[/td:307a706a71][td:307a706a71]toll[/td:307a706a71][/tr:307a706a71]
[tr:307a706a71][td:307a706a71]2[/td:307a706a71][td:307a706a71]2[/td:307a706a71][td:307a706a71]wundervoll[/td:307a706a71][/tr:307a706a71]
[tr:307a706a71][td:307a706a71]3[/td:307a706a71][td:307a706a71]2[/td:307a706a71][td:307a706a71]große Klasse[/td:307a706a71][/tr:307a706a71]
[/table:307a706a71]
Ich will jetzt SELECT * FROM jos_mt_links machen, allerdings sortiert nach der Anzahl Reviews, also wie oft die link_id in Reviews vorkommt.
Das Resultset sollte also so aussehen:
[table:307a706a71]
[tr:307a706a71][td:307a706a71]link_id[/td:307a706a71][td:307a706a71]name[/td:307a706a71][/tr:307a706a71]
[tr:307a706a71][td:307a706a71]2[/td:307a706a71][td:307a706a71]link_2[/td:307a706a71][/tr:307a706a71]
[tr:307a706a71][td:307a706a71]1[/td:307a706a71][td:307a706a71]link_1[/td:307a706a71][/tr:307a706a71]
[tr:307a706a71][td:307a706a71]3[/td:307a706a71][td:307a706a71]link_3[/td:307a706a71][/tr:307a706a71]
[/table:307a706a71]

Mein (n00biger? ) Versuch sieht so aus:

SELECT l.* from jos_mt_links AS l
ORDER BY
(SELECT Count(*) FROM jos_mt_reviews AS r
WHERE l.link_id = r.link_id)
LIMIT 0, 30

Es wird ein Syntaxfehler near "(SELECT Count(*) FROM jos_mt_reviews AS r WHERE l.link_id = r.link_id)" angezeigt :?

Wie mache ich das richtig? Danke

 

[André Uhres]

Ich denke ohne das limit müsste es funzen:

                   "SELECT l.* from jos_mt_links AS l ORDER BY (SELECT Count(*) FROM " +
                    "jos_mt_reviews AS r WHERE l.link_id = r.link_id) desc"

 

[Illuvatar]

Nein, an dem Limit liegt es nicht, afaik kann man eben nicht nach einem Subselect sortieren.

 

[André Uhres]

Nein, an dem Limit liegt es nicht, afaik kann man eben nicht nach einem Subselect sortieren.

Ich hab's mit Derby getestet. Der meckert beim LIMIT. Wenn der weg is, dann funzt es.
Er spuckt jedenfalls genau das raus was du willst:

/*
 * SimpleApp.java
 */

//package jdbc;
import java.sql.*; 
import java.util.Properties; 
import javax.swing.*; 
/** 
* You need derby.jar to run SimpleApp. You can download derby.jar here: 
* [url]http://apache.root.lu/db/derby/db-derby-10.1.2.1/db-derby-10.1.2.1-lib.zip[/url] 
*/ 
public class SimpleApp extends JFrame { 
    public SimpleApp(){ 
        super("SimpleApp"); 
        setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE); 
        setSize(400,350); 
        setLocationRelativeTo(null); 
        mainPanel = new JPanel(); 
        textarea = new JTextArea(); 
        add(new JScrollPane(textarea)); 
        setVisible(true); 
    } 
    private JPanel mainPanel; 
    private JTextArea textarea; 
    /* the default framework is embedded*/ 
    public String framework = "embedded"; 
    public String driver = "org.apache.derby.jdbc.EmbeddedDriver"; 
    public String protocol = "jdbc:derby:"; 
    
    public static void main(String[] args) { 
        new SimpleApp().go(args); 
    } 
    
    void go(String[] args) { 
        /* parse the arguments to determine which framework is desired*/ 
        parseArguments(args); 
        
        textarea.append("SimpleApp starting in " + framework + " mode.\n"); 
        
        try { 
            /* 
               The driver is installed by loading its class. 
               In an embedded environment, this will start up Derby, since it is not already running. 
             */ 
            Class.forName(driver).newInstance(); 
            textarea.append("Loaded the appropriate driver.\n"); 
            
            Connection conn = null; 
            Properties props = new Properties(); 
            props.put("user", "user1"); 
            props.put("password", "user1"); 
            conn = DriverManager.getConnection(protocol + 
                    "derbyDB;create=true", props); 
            
            textarea.append("Connected to and created database derbyDB\n"); 
            
            conn.setAutoCommit(false); 
            
            /* 
               Creating a statement lets us issue commands against 
               the connection. 
             */ 
            Statement s = conn.createStatement(); 
            
            /* 
               We create a table, add a few rows, and update one. 
             */ 
            s.execute("create table jos_mt_links(link_id int, name varchar(40))"); 
            textarea.append("Created table jos_mt_links\n"); 
            s.execute("insert into jos_mt_links values (1,'link_1')"); 
            s.execute("insert into jos_mt_links values (2,'link_2')"); 
            s.execute("insert into jos_mt_links values (3,'link_3')"); 
            textarea.append("Inserted three records\n"); 
            
            s.execute("create table jos_mt_reviews(rev_id int, link_id int, text varchar(40))"); 
            textarea.append("Created table jos_mt_reviews\n"); 
            s.execute("insert into jos_mt_reviews values (1, 1, 'toll')"); 
            s.execute("insert into jos_mt_reviews values (2, 2, 'wundervoll')"); 
            s.execute("insert into jos_mt_reviews values (3, 2, 'große Klasse')"); 
            textarea.append("Inserted three records\n"); 
            
            /* 
               We select the rows and verify the results. 
             */ 
            ResultSet rs = s.executeQuery( 
                    "SELECT l.* from jos_mt_links AS l ORDER BY (SELECT Count(*) FROM " +
                    "jos_mt_reviews AS r WHERE l.link_id = r.link_id) desc" 
//                    +" LIMIT 0, 30"
                    ); 
            
            while (rs.next()) { 
                textarea.append(rs.getString(1) + " " 
                        + rs.getString(2) + " " 
                        + "\n");
            } 
            
            s.execute("drop table jos_mt_links"); 
            s.execute("drop table jos_mt_reviews"); 
            textarea.append("Dropped tables\n"); 
            
            /* 
               We release the result and statement resources. 
             */ 
            rs.close(); 
            s.close(); 
            textarea.append("Closed result set and statement\n"); 
            
            /* 
               We end the transaction and the connection. 
             */ 
            conn.commit(); 
            conn.close(); 
            textarea.append("Committed transaction and closed connection\n"); 
            boolean gotSQLExc = false; 
            
            if (framework.equals("embedded")) { 
                try { 
                    DriverManager.getConnection("jdbc:derby:;shutdown=true"); 
                } catch (SQLException se) { 
                    gotSQLExc = true; 
                } 
                
                if (!gotSQLExc) { 
                    textarea.append("Database did not shut down normally\n"); 
                } else { 
                    textarea.append("Database shut down normally\n"); 
                } 
            } 
        } catch (Throwable e) { 
            textarea.append("exception thrown:\n"); 
            
            if (e instanceof SQLException) { 
                printSQLError((SQLException) e); 
            } else { 
                e.printStackTrace(); 
            } 
        } 
        
        textarea.append("SimpleApp finished\n"); 
    } 
    
    void printSQLError(SQLException e) { 
        while (e != null) { 
            textarea.append(e.toString()); 
            e = e.getNextException(); 
        } 
    } 
    
    private void parseArguments(String[] args) { 
        int length = args.length; 
        
        for (int index = 0; index < length; index++) { 
            if (args[index].equalsIgnoreCase("jccjdbcclient")) { 
                framework = "jccjdbc"; 
                driver = "com.ibm.db2.jcc.DB2Driver"; 
                protocol = "jdbc:derby:net://localhost:1527/"; 
            } 
            if (args[index].equalsIgnoreCase("derbyclient")) { 
                framework = "derbyclient"; 
                driver = "org.apache.derby.jdbc.ClientDriver"; 
                protocol = "jdbc:derby://localhost:1527/"; 
            } 
        } 
    } 
}

Output:

SimpleApp starting in embedded mode.
Loaded the appropriate driver.
Connected to and created database derbyDB
Created table jos_mt_links
Inserted three records
Created table jos_mt_reviews
Inserted three records
2 link_2 
1 link_1 
3 link_3 
Dropped tables
Closed result set and statement
Committed transaction and closed connection
Database shut down normally
SimpleApp finished

 

[Illuvatar]

Hm zugegeben, ich hatte das nicht getestet, weil ich per phpmyadmin getestet hab, und der immer das Limit anhängt Aber ich meine, MySql hat da ein Problem, und zumindest hat EagleEye(RIP) das auch gesagt

Nun ja, dank dessen Mithilfe habe ich jetzt jedenfalls eine Lösung gefunden:

SELECT l.link_id,l.name,COUNT(r.link_id) AS col
FROM jos_mt_links l
LEFT OUTER JOIN jos_mt_reviews r
ON l.link_id = r.link_id
GROUP BY l.link_id
ORDER BY col
LIMIT 0,30

*abhak*

Edit: Hoppla, jetzt hast du so viel Testcode da auf einmal reineditiert. Ich hoffe, das war jetzt nicht zu viel Mühe für dich :roll:
Ich mach btw. PHP

 

[André Uhres]

..Hoppla, jetzt hast du so viel Testcode da auf einmal reineditiert. Ich hoffe, das war jetzt nicht zu viel Mühe für dich ..

Kein Problem. Das Ding war schon fertig für einen anderen Thread. Hab's nur e bissel angepasst :wink:

 

[Illuvatar]

Hab übrigens grade noch mal getestet wie das ohne das LIMIT ist, MySQL bringt dann immer noch nen Fehler.