Tag Info

New answers tagged

0

Please try the following. If you need most recent date for every song over all meeting instances: select song_id, max(meeting_date) from song_instances si join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id group by song_id If you're interested only in particular meeting (meeting_id = 4): select song_id, max(meeting_date) ...


1

The query you need is: SELECT camp.Campiagn_Id, camp.Campiagn_Name, camp_list.Campign_Id, camp_list.EmalList FROM [table1] AS camp LEFT JOIN [table2] AS camp_list ON (camp.Campiagn_Id = camp_list.Campiagn_Id); This will show all campaigns with all emails lists for each campaign. It will show also gampaign with no emails ...


0

I finally figured that joining these tables would not bring a solution. I solved it doing this: SELECT S.schaal, W.wnr, W.wnaam, W.salaris FROM Werknemer W, S_schaal S WHERE W.salaris >= S.ondergrens && W.salaris <= S.bovengrens;


0

Perhaps creating some denormalized data in advance would speed up the process. That, where possible, could reduce the number of rows involved in your ultimate query. This could mean a persistent table that you maintain. But it could also be implemented as a temp table (e.g. #criteria) that could then be joined more simply to get your final results. There ...


3

select l.email_list_id, l.email_list_name, count(d.email_uniq_id) as full_count, count(case when d.blacklist = 0 then d.email_uniq_id end) as white_count, count(case when d.blacklist = 1 then d.email_uniq_id end) as black_count from tbl_email_list as l left join [tbl_email-details] as d on d.email_list_id = l.email_list_id group by ...


1

Try something like this: SELECT el.emali_list_name AS EmailList ,COUNT(*) AS EmailsCount ,SUM(CASE WHEN ed.blacklist = 1 THEN 1 ELSE 0 END) AS BlackList4ListCouint ,SUM(CASE WHEN ed.blacklist = 0 THEN 1 ELSE 0 END) AS WhiteList4ListCouint FROM [tbl_email_list] AS el LEFT JOIN [tbl_email-details] AS ed ON (el.email_list_id = ed.email_list_id) GROUP BY ...


3

You half miss the connection between the two tables. The ...relid columns must match, too: SELECT attname, c.* FROM pg_attribute a JOIN pg_constraint c ON attrelid = conrelid -- this was missing AND attnum = ANY (conkey) WHERE attrelid = 'test_table'::regclass::oid;


0

ypercube is correct in pointing that the output which you claim would be different than the actual output. As the subquery is going to display the total value unless add another filter condition on fk_id_anexa. SELECT a2a.ID, a2a.fk_id_anexa, a2a.PET1, a2a.PET2, a2a.ALT1, a2a.ALT2, (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS tab2 where TIP = 'PET' and ...


3

There are several ways to do this. Either remove the join and convert the subqueries to correlated: SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2, (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b WHERE b.TIP = 'PET' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA ) AS PET3, (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b WHERE b.TIP = ...


1

You may add the WHERE clause in the LEFT JOIN so not joining rows are still in the result set. SELECT `performers`.`hash`, `performers`.`alias`, `performers`.`date_updated`, `performers`.`status`, IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`, `images`.`image_hash_file` FROM `performers` LEFT JOIN `images` ON ...


3

Try to execute the query with gather_plan_statistics hint. Then use dbms_xplan to display exec plan. You will see E-rows(estimated) and A-rows(Actual). This is where I would start, to check whether the optimizer is wrong in it's assumptions or not. Also check v$sql_plan of the running query and check whether TEMP space is really used or not. Especially on ...


0

Relationships are used for describing how the tables are well, related and for enforcing this such as satisfying a foreign key from one table requires a matching entry in another. A join is used to realize those relationships. In a join you are telling the database what fields to match and how when it is executed. In a relationship you are telling the ...


0

I edited the Query to the following: EXPLAIN UPDATE jobcontrol.actual_values a JOIN jobcontrol.processing p ON a.para_id = p.para_id SET a.value = (select p.value from jobcontrol.processing p WHERE a.para_id = p.para_id ORDER BY t_ns DESC LIMIT 1) The JOIN ON makes the recordset smaller (DB checks only the parameters which are also in ...


0

Following is the SQL Server version in steps to work on IPs Range this might help you to convert in SqL Step 1: Create function [usp_stringpart] in your Database Create By: Aasim Abdullah Description: Function takes a string and section number of string which is required and separator, which separates different sections of given string Create FUNCTION ...


0

Starting at the end - if you have, say, 20 large tables of 10 millions rows each at 200 bytes per row that works out at just under 40GB. Add twice as much for indexes and your entire database will fit into memory. With sensibile indexes in place you really shouldn't worry too much about joining multiple tables. Joins are what relational databases do, it's ...


0

What you describe is frequently called a "joining table" - a many to many connection. Athough the word "associative" is readily understandable in the context, I would use the term "joining" table. You can put either a PRIMARY KEY or a UNIQUE KEY on the fields (case_id, tag_id) - no need for a PRIMARY KEY with id - it's superfluous.


1

For that many-to-many 'relation' table, don't bother to have an id. Simply have PRIMARY KEY(case_id, tag_id), INDEX(tag_id, case_id) Note that one of those works perfectly for one of your SELECTs, the other works perfectly for the other. (Use ENGINE=InnoDB.) To get (tag-1, tag-2, tag-3), use GROUP_CONCAT(). FOREIGN KEYs are optional; I would not ...


0

Recommend you use parentheses to make sure: FROM ( a JOIN b ) RIGHT JOIN c or FROM a JOIN ( b RIGHT JOIN c ) depending on which is more correct.


3

When you use a column from a table on the "wrong"* side of an OUTER join, the join usually becomes equivalent to an INNER join. * "wrong": right side of a LEFT join and left side of a RIGHT join. So, the solution is usually to move the condition from the WHERE to the ON of the outer join. Like this: SELECT cre.id, rer.idChambre -- ...


1

As far as your query goes, it seems you've followed SQL best practices so there are no easy, significant performance boosts I could find. Now, sorry to state the obvious, but make sure you have enough indexes and that you are using those indexes, and check if all your stats are up to date. Database Engine Tuning Advisor is a great tool to check both of ...


2

Wrong interpretation of the error. You had a power failure (or some other crash). You are using MyISAM (you should use InnoDB). Those caused the "Incorrect key file for table". CHECK TABLE TBL_REPORT R; CHECK TABLE TBL_REPORT_PROXY; If either says to "repair", then perform REPAIR TABLE. If that does not work, look at this value: SHOW VARIABLES LIKE ...


1

PROPOSED QUERY SELECT IFNULL(B.uid,'') left_uid, IFNULL(C.uid,'') right_uid FROM relation A LEFT JOIN data B ON A.left = B.id LEFT JOIN data C ON A.right = C.id ; I did it as LEFT JOIN rather than INNER JOIN in case data is missing from data table YOUR SAMPLE DATA drop database if exists gumkins; create database gumkins; use gumkins ...



Top 50 recent answers are included