Tag Info

New answers tagged

0

As a quick solution, use the <=> operator, so AND (A.course_type <=> B.course_type). As a real solution, add a column id to course and let something_about_courses show to that id column. EDIT: If you want to get rid of the NULL, you don't need to do that manually. What about UPDATE A set course_type=-1 where course_type IS NULL; UPDATE B ...


0

Try and see if this yields the result you want: SELECT some_description FROM something_about_courses A JOIN courses B ON (A.course_code = B.course_code AND A.course_session = B.course_session AND (A.course_type = B.course_type OR B.course_type IS NULL))


1

First of all, append a computed column, say A4Key, set to SUBSTRING(A4, 4, 3). This column shall be kept up-to-date either by your application code or by triggers, such as: CREATE TRIGGER ... BEFORE UPDATE ... ... SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3) ... CREATE TRIGGER ... BEFORE INSERT ... ... SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3) ... ...


0

Perhaps you are looking for this: select issues.id, array_agg(journal.notes) from issues left outer join journal on (issues.id = journal.issue_id and journal.notes != '') group by issues.id Please check this http://sqlfiddle.com/#!1/24db9/2


1

You're using po in the WHERE clause, and in effect, saying that it can't be null. Maybe use: IFNULL(po.tot2,0) instead of po.tot2


1

I assume you're talking about Relational Algebra here. A SELECT operation returns whole tuples (rows) based on some predicate. The predicate is defined in the SQL statement by the WHERE clause. The PROJECT algebra operation is where you select a subset of attributes from each tuple. The PROJECT operation is defined in the SQL statement by the columns ...


2

Try this: select patients.name AS patient_name ,NULL As nurse_primary ,m.`name` As nurse_morning ,a.name As nurse_afternoon ,e.name As nurse_evening FROM patients left join (SELECT patient_id, n.`name` AS `name` FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id where pn.shift='MORNING' AND pn.id = (SELECT MIN(id) FROM patient_nurse ...


2

It is better to use something like this: select distinct p.* from Property p inner join PropertyHasFeature fp on (fp.p_id = p.id) inner join Feature f on (fp.f_id = f.id) where f.id in (<ID of first feature>, <ID of second feature>) In this case you will not need to add the joins when the feature list is getting larger. This will select all ...


1

First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows: SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid Now you can build a join to retrieve not only sid, but all of the ...


2

The table simply does not exist. How can you verify this ? Goto the operating system and run the following cd /var/lib/mysql/crs ls -l CRS_PAIR.frm If that file does not exist, the table does not exist. If that is the case, then what's all that 246G bloated space ? You need to look the following InnoDB Architecture Take a closer look at ibdata1 ( ...


2

InnoDB Architecture Please keep in mind what goes into the InnoDB Buffer Pool 16KB Data Pages for Tables that have been accessed 16KB Index Pages for Indexes that have been accessed Changes to Secondary Indexes (could take up to 50% of buffer pool in a high-write envrironment) MySQL's idea of a JOIN Believe it or not, whether you have one big table ...


0

Seems you are running in a weakness of the query planner: The best index is sometimes not used for joining tables. Had a similar problem here: Algorithm for finding the longest prefix (Chapter "Failed attempt with text_pattern_ops") In Postgres 9.3 You could try this version with LEFT JOIN LATERAL: SELECT * FROM ( SELECT coord FROM ...



Top 50 recent answers are included