Tag Info

New answers tagged

0

Join conference to person_conference, selecting the person key from person_conference. Do the same for publication and person_publication. Make the two queries sub queries in a from clause and join the two on the person key. Edit: You would want to do something like this: SELECT p.person_id, p.name, a.conference, b.publication FROM ...


0

Instead of doing any LEFT JOIN, try this query PROPOSED QUERY SELECT IFNULL(name,'Total') name, SUM(IF(type='dog',quant,0)) dogs, SUM(IF(type='cat',quant,0)) cats FROM ( SELECT AA.name,BB.type,SUM(BB.quant) quant FROM customers AA INNER JOIN purchases BB ON AA.id = BB.owner_id GROUP BY AA.name,BB.type ) A GROUP BY name WITH ROLLUP; ...


0

Perhaps it is easiest to see why this is happening by removing the aggregate functions: SELECT c.name , dogs.quant , cats.quant FROM customers AS c LEFT JOIN purchases AS dogs ON c.id=dogs.owner_id AND dogs.type = 'dog' LEFT JOIN purchases AS cats ON c.id=cats.owner_id AND cats.type = 'cat' +------+-------+-------+ ...


1

Here are my notices: Make sure that creator exists only in table1, or add [... WHERE table1.creator=..] It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key. From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned ...


0

You are on the right track. Don't let perceived complexity derail the design, as it is basically the standard way to represent many-to-many relationships. These tables are also a good place to store metadata or attributes of the entity relationship. Your reasoning is mostly correct: there are data integrity benefits to using a DBMS-generated key in the ...


0

In my experience, the round-robin joins on both legs of the query certainly make for an expensive plan. It depends on a few issues, but consider using these options to help you: Use a temporary or a work table on Server A named #DataTable_ServerB_local to store the results from Server B. If possible create a stored procedure Prepare_Datatable_ServerB_Data ...


0

Try ordering the table by start_IP ranges then put a clustered index into the table. The idea is you want to have your query only search the relevant uster for the match instead of doing a table scan. Since I assume the IP start < IP end you can use this knowledge to omit those ranges that fall above below the IP your searching for.


1

You also need a table with customers. The customers table needs to be CROSS JOINed with the calendar table to get the list of all possible dates per customer. This SQL Fiddle demonstrates the query that gets the desired result. Please note that your database schema may have a table with customers, and you may not need a customers CTE I have created. Just ...


1

If you want 14 rows in the result (6+8), you need to use UNION of the two queries you already have. Just take care for both to have the same number of columns (and same datatypes in the respective columns.) It will be something like: SELECT u.*, -- list of wanted columns from the users table 'Video' AS type ...



Top 50 recent answers are included