New answers tagged

0

You do not need to create a view for this. View is generally used to improve the performance of code. For this you have to find out the current month values always using the order by of year and month. Find below solution of your problem: select er.region, er.month, er.year, sum(es.sales) from employee_sales es inner join employee_region er on es.name = ...


0

To retrieve data from both table associated with foreign key i.e(common column) you have to join both the tables. if you matching data from both table then use INNER JOIN. > SELECT A.List_Of_columns,B.List_Of_columns FROM Table1 AS A INNER > JOIN Table2 as B ON A.ID=B.ID (Here Id is Common in both table). If you want matching data from both ...


0

For getting the result from the two table associated with the foreign key, you need to join the tables. Find below query as like you want the result: SELECT name, date, roll FROM A INNER JOIN B ON A.id = B.id;


1

You need to join these tables to get the result that you want. SELECT b.*, a.name FROM tableB AS b INNER JOIN tableA as A ON (b.id=a.id); This query will return everything from Table B and name from Table A where the ID from Table B is the same as the ID from Table A.


3

Joining tables is a fundamental principle of relational databases. In your case, A and B are related with the id column, which means that you can use a syntax similar to this one: SELECT a.id, a.name, a.num, b.date, b.roll FROM a INNER JOIN b ON a.id=b.id; INNER JOIN means that you'll only see rows where there are matching records in A and B. If you want ...


2

Change AND c.tar_id = 87 to WHERE c.tar_id = 87. Your soultion would work only if it was an INNER JOIN instead. With LEFT JOIN it works different. The filter is applied for the join predicate and the result is different.


2

A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher. In fact you could just make the the PK and drop ID. This is TSQL so it may be wrong for mysql SELECT S.ID , S.LASTNAME , S.FIRSTNAME FROM STUDENTS S LEFT JOIN TEACHERS T ON T.STUDENT_ID = S.ID AND T.TEACHER_ID = $teacherID ...


1

Your join should be on TEACHER_ID = STUDENTS.ID and not the ID of the TEACHERS table. Your WHERE clause should also use TEACHER_ID. See if the below works for you. SELECT S.ID , S.LASTNAME , S.FIRSTNAME FROM STUDENTS S LEFT OUTER JOIN TEACHERS T ON T.STUDENT_ID = S.ID WHERE TEACHERS.TEACHER_ID <> $teacherID


0

Ok, it take me some time but I think it works well: select clickcume nr_clicks, avg(view_b) avg_view_before from( select userid, clickcume, max(viewcume) view_b from( select a.*, b.time_v, b.viewcume from ( select userid, time as time_c, count(*) over (partition by userid order by time) as clickcume from views) a join ( select userid, ...


0

As a rule of thumb conditions on the Outer table are usually placed in WHERE, but on the Inner table in ON: SELECT DISTINCT student.Student_ID as 'Student_ID', case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID', case when Attendance.Attendance_Status is null then 'Not Present' else ...


0

Okay, the performance here is being limited by the index of "urlShort" even though the field isn't being updated. Another reason to normalise the schema so that this is no longer necessary.


-3

This will return all the lines of the two tables. You can change the aliases so that you get the column name you want. SELECT t1.col1 as name, t1.col2, t1.col3 FROM t1 UNION ALL SELECT t2.cola as name, t2.colb, null FROM t2;


1

Abandon all hope! First, there is a contradiction... You say FORCE INDEX(PRIMARY), yet PRIMARY is not an option; instead the better (actifVehicule) is being used. Please provide SHOW CREATE TABLE; it is difficult to guess at what indexes you have. Here's what is going on... It is starting with V0 and and filtering on actifVehicule=1. Since (I assume ...


0

Not sure I understand what you are asking, but NATURAL JOIN might be what you are looking for: select t1.id, t1.value, t2.value, t3.value from t1 natural join t2 natural join t3 The name of the attributes is used to determine the join condition. Personally I consider natural join dangerous because certain attributes may be named the same without having ...


3

Then it looks like an optimizer's blind spot and you should use the second query. When there is a condition joining two tables a and b: a.id = b.id and an additional condition a.id > @some_constant, seems like the optimizer uses the "index condition" for where to start the index scan on a (id) index but it doesn't use it for the second index b (id). So, ...


0

As the way your schema current sits, you'll need two parts; the main select statement and (since you are using MySQL, which doesn't support common table expressions), a view in the where clause. The view, priveView: SELECT product_id , MAX (valid_since) [validSince] FROM prices GROUP BY product_id The select statement: SELECT SUM (P.Prices) FROM ...


0

There is no natural order in a table of an RDBMS. Tables are sets that have no logical order. So your question is logical nonsense: is it possible to bind columns "as they are"? .. because "as they are" does not make sense in the context of tables of an RDBMS. Also, joining without condition is a CROSS JOIN in SQL, which produces a Cartesian product. ...


0

To me this looks like a quite typical join: select t1.id,t1.value,t2.value,t3.value from t1,t2,t3 where t1.id=t2.id and t1.id=t3.id


1

You could need brackets. Try this: SELECT Actors.FirstName,Actors.LastName FROM ((Actors INNER JOIN ActorsMovies ON Actors.ActorID= ActorsMovies.ActorID) INNER JOIN GenresMovies ON ActorsMovies.MovieID=GenresMovies.MovieID);


3

All these nested derived tables (subselects inside subselects) are useless. You can easily remove all except 1 or 2. SELECT dev_hour.hour, rtdogs.delivery_date, rtdogs.delivery_hour, rtdogs.total_offer FROM delivery_hour AS dev_hour LEFT JOIN ( SELECT sum(greatest(ifnull(quantity1,0), ifnull(quantity2,0), ...


2

The following pattern will often render very good performance, compared to self-joins and correlating subqueries: SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY CustomerID, c1, c2, c3 ORDER BY OpenOrderDate DESC ) AS _rownum FROM some_table INNER JOIN ... INNER JOIN ... WHERE ... ) AS sub WHERE ...


1

When I've had to do this in the past one easy pattern I've followed is below: WITH LatestOpenOrderDate_CTE AS ( SELECT UserId , LatestOpenOrderDate = MAX(OpenOrderDate) FROM dbo.Foo GROUP BY UserId ) SELECT L.UserId , F.OpenOrderDate , F.Bar -- Just here to show how to get the other data from the matched row , B.Buzz -- Just here ...


1

Another way to rewrite the query, without having 2 correlated inline subqueries is to have instead one LEFT JOIN in the FROM clause. The problem is that MySQL does not have LATERAL joins so it looks a bit clumsy and is quite confusing to get it right: SELECT *, topics.createdate AS TopicCreateDate, lastpost_user.username AS LastPost_UserName, ...


3

The cause of the problem was identified by @Phil in the comments: Probably because it's nested too deep You have 2 layers of nesting and the reference of table e cannot "see" through these 2 layers in MySQL. Correlated inline subquery can usually be converted to derived tables and then LEFT joined to the other tables in the FROM clause but they have ...


0

Problem 1: LIMIT is somewhat meaningless without ORDER BY to indicate which 25. Problem 2: Don't say x=null, it won't do what you think. Instead do x IS NULL. Problem 3: Do you really want a "cross join"? That is, a JOIN without an ON clause, and hence gets all combinations of the two tables? Problem 4: Well, I did not check the rest of what you ...



Top 50 recent answers are included