Tag Info

New answers tagged

0

Inner Join: An inner join produces a result set that is limited to the rows where there is a match in both tables for what we're looking for. If you don't know which kind of join you need, this will usually be your best bet. Left Outer Join: Add to Where ... and establishment_id is not null and department_id is not null And sometimes just to make ...


6

A WHERE-condition on the inner table will change the the result to an Inner Join instead. You need to move this condition to the ON: SELECT Confessions.ID, Confessions.WriteTitle, Confessions.WriteArea, Confessions.POST_DATE, Votes.Type FROM Confessions LEFT OUTER JOIN Votes ON Confessions.ID = Votes.PostID AND Votes.IP_Check = :IP_Check WHERE ...


0

If you dont mind about dirty read then you might either use NOLOCK hint or READUNCOMMITTED TRANSACTION ISOLATION. This could improve your query's performance in case this query is continuously blocked by other session. Otherwise your could look for what is the Max Degree of Parallelism is set for your server. If it is set to 1 then you could increase it. ...


2

You're permitted to do multiple joins in a single statement. You can do this even when joining on the same table by using aliases. I created a very quick example which is similar to your case. I just used ids instead of a time, and I stored the whole name as a single field; you'll just have to concatenate the first and last name on your own ;) Schema and ...


-2

If that query runs for five hours, then i suspect there could be an improper join between tables which could cause duplicated records. Check your Conditions you used to make a join.


0

join on P kills the left join on P2 moc.WRK_LOC_REG_UKHP is not null kills 3 left joins from GAPDatabase.waStock t inner join GAPDatabase.mpStockStatus tss on tss.REF_STAT_UKHP = t.REF_STAT_UKHP and t.APPR_DT between '1/1/2015' and getdate() inner join GAPDatabase.Progress p on t.cust_REF = p.cust_REF inner ...


3

The first thing I would check is the fnGetUSD -function. It looks like currency conversion, and you're now calling the function 5 times for every single row. That can be a huge performance issue. At least look into changing it to a inline table valued function (the multi-statement function will not help) or adding the calculation directly to this view. For ...


0

I would suggest to filter earlier. Your where clause has some values which can be filtered in a subquery, which will preserve a join of columns which weren't used and thrown away at the end. This will normally speed things up. Another point could be the table order and the type of table joins. But to get throw this I would need the execution plan. Another ...


1

You can try both of this: SELECT a.customer_id, b.value, b.attribute_id FROM a JOIN b ON a.customer_id = b.customer_id WHERE b.attribute_id = 2 or: SELECT a.customer_id, b.value, b.attribute_id FROM a JOIN (SELECT value, attribute_id FROM b WHERE attribute_id = 2) as b ON a.customer_id = b.customer_id


4

why is the query optimizer not able to use the index for deleting when the subquery version, while it is while using the join version? Because the optimizer is/was a bit dumb in that regard. Not only for DELETE and UPDATE but for SELECT statements as well, anything like WHERE column IN (SELECT ...) was not fully optimized. The execution plan usually ...


2

here is the answers for two of your questions Optimizer is not able to use index because the where clause changes for every row. The delete statement will look something like this after it pass the optimizer delete from VARIABLE_SUBSTITUTION where EXISTS ( select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY.BUILD_KEY = ...


0

If joining is "optional", then it is not an inner join, by definition. Consider investigating left joins.


0

The problem may be that you have multiple columns of the same name in the result (2x cp). When using SELECT * you would return both. The simple fix for the query at hand would be USING in the join clause, which only keeps one instance of the joined column in the result (and seems smarter in this particular case anyway): SELECT * FROM traffic_counts s1 ...


1

Here is an example. Declare @PriceGroup TABLE ( id INT ,Catalog_Code INT ,Name VARCHAR(50) ) INSERT INTO @PriceGroup (id,Catalog_Code,Name) VALUES (1,1,'PriceGroup1'),(2,1,'PriceGroup2'),(3,1,'PriceGroup3') Declare @Price TABLE ( id INT ,articol_id INT ,catalog_code INT ...


0

I splitted the problem into two basic SELECTs, one for members and the another one for guests and I join both later. MySql's GROUP_CONCAT aggregate funtion is very useful here too. http://sqlfiddle.com/#!9/58786/1 SELECT TT1.day as day ,TT1.booking_id as booking_id ,TT1.member_id as member_id ,CASE WHEN TT2.guest_ids IS NULL THEN 0 ELSE ...


2

To get the desired result from your sample table, select tgroup, name, id from ( select tgroup, name, id, row_number() over (partition by tgroup order by id) rn from (<<your subselect>>) ) where rn = 1 I've renamed group to tgroup as group is a reserved word in SQL. SQLFiddle As for your real query, please first make it ...


2

Your query SELECT b.* FROM a,b LEFT JOIN c ON hour(c.datetime) = a.hour; means (parentheses added) SELECT b.* FROM a, (b LEFT JOIN c ON hour(c.datetime) = a.hour); and that means a is not visible in the join. You may need to shuffle it around a bit SELECT b.* FROM (a LEFT JOIN c ON hour(c.datetime) = a.hour), b; http://sqlfiddle.com/#!9/4114a/2 ...


4

I assume you have multiple entries in shows, views table for each user. When joining all three together, you get more rows than you expect: if for some user you have x rows in table shows and y rows in table views, then the result set contains x*y rows for that user, so you sum each entry in shows y times, and each entry in views table gets counted x times. ...


3

Presumably, you want to see a single entry for each unique person/address/email/website combination. If so, try this: SELECT (person.FirstName + ' ' + person.LastName) as FullName , ISNULL(Person.isClient, '') , ISNULL(Person.UDF1, '') , ISNULL([Address].City, '') , ISNULL([Address].[state], '') , PersonAddress.Person , ...



Top 50 recent answers are included