Tag Info

New answers tagged

1

The reason the count differs is due to multiple rows being present in tableA that have a foreign key reference to the same row in tableB. When using an inner join the SELECT produces a separate row for each of the records in tableA. The DELETE statement is only affects tableB. As mentioned in ypercube's comment, if you were to do a distinct count, the ...


2

This is tricky question, specially since 5.6, in which the optimizer has changed the way subqueries are executed. Specially, having into account that the definition of "derived table" may change person to person From 4.1 to 5.5, in many cases, either a temporary table would be created or a cross join will be done between tables, filtering later. In some ...


1

Your question is whether temporary table are stayed in memory or in file-system. Actually it is easy to check in MySQL documents tmp_table_size there are some answers already in DBA STACKEXCHANGE http://dba.stackexchange.com/a/29638/41541


0

try this SELECT [Subject] , [Type], MarksObtd FROM [Marks_Table] INNER JOIN [Subjects_Table] ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode INNER JOIN [Types_Table] ON [Marks_Table].TypeID = [Types_Table].TypeID As james added comment, I think it is a simple join and you need to change [TypeID] in select clause to [Type]


0

some speculations: It is not really true that the only difference is the oracle style outer join and ansi join. Maybe the queries are theoretically equivalent. But there are at 9 different tables in the where clause and the order of the table differs. 9 different tables means 9!=362880 different possibilities for the sequence of tables. Oracle optimizer ...


0

Based on comments you want to do an outer join, for instance, a left join specifying the condition by which you join the time_range table to another one on the ON clause: ... from schedule sched join class_teachers_section join day join class join teachers join section left join time_range ON ....Your condition goes here.... where ...


0

When you want only one row you have to group your data. As you are looking for infos of one day a GROUP on a date seems to do the trick. In your case GROUP BY day_name if you don't have a date field. And when you group you can use MIN and MAX for your time: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html


0

The counts are using the primary keys, but the joins are not. If there aren't indexes on x_user_images.user_id x_allies.to x_allies.from x_user_likes.profile_id x_trybe_owner.profile_id x_trybe_invites.to x_subgoal_post.profile_id x_ambition_owner.profile_id x_ambition_invites.to you're going to be doing a lot of table scans. I don't know how MySQL ...


3

You can get the result by using a combination of a WHERE, GROUP BY, and HAVING to return the products that have both the Book and Audio values. select p.id, p.name from products p inner join properties t on p.id = t.product_id where t.key = 'Format' and t.value in ('Book', 'Audio') group by p.id, p.name having count(distinct t.value) = 2; See SQL ...


0

The subquery computes, for each user in USERS, the latest msg_id value: SELECT * FROM MessageTable WHERE msg_id IN (SELECT (SELECT msg_id FROM MessageTable WHERE msg_to = Users.user_id OR msg_from = Users.user_id ORDER BY msg_id DESC -- or by a ...


13

You've got a bunch of different questions in here, so let's break 'em out individually. Q: If I join two tables in the same database with the above query, why is it slow? A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a ...


2

Ok, here's my two pence: SELECT header.Col1, MAX((CASE WHEN details.ordinal=1 THEN details.Col2 END)) AS row1, MAX((CASE WHEN details.ordinal=2 THEN details.Col2 END)) AS row2, -- ... and so on.. MAX((CASE WHEN details.ordinal=99 THEN details.Col2 END)) AS row99 FROM ( --- For each Col1, enumerate all the rows and return Col2 ...


0

Use plain JOIN, not LEFT SELECT SQL_NO_CACHE chars.name FROM chars JOIN inv ON (chars.pid = inv.pid) JOIN institems ON (inv.institem = institems.id) WHERE inv.institem = 10001013730 || institems.container_slot_1 = 10001013730 || institems.container_slot_2 = 10001013730 || institems.container_slot_3 = 10001013730 || ...


2

Your structure i commonly known as an Adjacency List (should perhaps be Set) Model. If you don't want to change the structure of your model, you basically have to do something similar to what you are currently doing. You can rewrite your query using joins like: SELECT q1.* FROM qa_posts q1 WHERE q1.postid=1 UNION SELECT q2.* FROM qa_posts q1 ...


0

In your query, the join has no conditions. Shouldnt be something like SELECT xxx FROM table1 JOIN table2 ON table1.column = table2.column and xxx.date between xxx to xxx . Add: Here is the explain for the test table explain select find from table1 t1 join table2 t2 on t1.find between t2.from1 and t2.to1; ...


1

I would say the statement is false, though it's difficult to prove a negative. I don't believe a CROSS JOIN could be done with sub queries SELECT * FROM T1 CROSS JOIN T2 or for that matter any join that delivers a many to many result


2

As far as I know, a JOIN [INNER JOIN], from the theoretical point of view, is a projection of a CROSS JOIN (every combination of two tables). If you can get a cross join using a subquery and apply any function on it, then you have a perfect substitute. I think you can always transform: SELECT T1.A, T2.B FROM T1 JOIN T2 ON condition(T1.C, T2.D) into: ...


3

They're two slightly different animals that can be used for the same purposes, as in your example. CROSS APPLY is your only option for "joining" table value functions and "expanding" xml documents, though. Some queries, particularly parallel queries, can exhibit vastly improved performance using CROSS APPLY, provided you have the requisite processor threads ...


1

hye i tried below query in mysql and SQL server databases its working fine, so please check once again. and tell me which database u tried. SELECT t1.id, t2.value FROM table1 t1 LEFT JOIN table2 t2 ON t2.id = t1.id id value -- -------- 1 100 2 200 3 (NULL) 4 (NULL) Thanks.


0

Try this SELECT t1.id, t2.value FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t2.id = t1.id; +----+-------+ | id | value | +----+-------+ | 1 | 100 | | 2 | 200 | | 3 | NULL | | 4 | NULL | +----+-------+


2

UPDATE Table_1 SET EmailId = (SELECT d.EmailId FROM Table_2 d WHERE Table_1.Id = d.Id ) WHERE EXISTS (SELECT d.EmailId FROM Table_2 d WHERE Table_1.Id = d.Id AND Table_1.EmailId <> d.EmailId AND d.EmailId IS NOT NULL ...



Top 50 recent answers are included