Hot answers tagged

14

They are different. In the first option you get 2 times Table2 into your query. Once as t2 and once as t3. Both have a different content and you must put them somehow back together. To me this is more an OR instead of an AND. In the second option you get only the Table2 rows that meet both criteria. Suppose you have Table2 with the following content: | ...


5

The issue of the amounts that are multiplied is easy to solve. You just have to use a derived table that does the calculation (group by) first and then join that (derived table) to the other one, that stores the hierarchical structure. The derived table to use: ( SELECT userid, SUM(amount) AS sele_descendant_amount FROM webineh_user_buys ...


2

I looked at the table structures and as Aaron had previously pointed out TimeSheets does not have any indexes and the other tables do not have any nonclustered indexes. I created the two databases and their tables and generated the estimated execution plan and I get the same execution plan that you are currently getting. I created a clustered index on ...


2

SQL is not making up rows. If that join is producing more rows than you expect then figure it out. Are you sure ts.JobSubTypeId is a task code? That name does not sound like a task code. Try this - it will show you were the volume is coming from In your query each count is a row -- this is the raw count from Timesheets SELECT ts.JobSubTypeId, count(*...


2

mdbo and @ are hints it's MS Sql Server. Use a splitter table valued function of choice, Jeff Moden's one http://www.sqlservercentral.com/articles/Tally+Table/72993/ performs very good. So select * from Test.dbo.Test1 a INNER JOIN @TestTable T on T.Name in (select item from [DelimitedSplit8K](a.Name, ',')) But may I say the best way to solve the ...


2

You have to do with users the same thing you're doing with days: WITH days AS ( SELECT generate_series(current_date-7, current_date, '1d')::date AS day ), eves AS ( SELECT user_id, created_at::date AS full_day, COUNT(*) as evs FROM logged_events WHERE logged_events.created_at >= current_date-6 GROUP BY user_id, full_day ), ...


1

Would it be better to use a NOT IN or NOT EXISTS query? Yes, you would most likely benefit from an IN() (or EXISTS()) instead of the join and IS NULL because you're effectively only using the tableREPORTS data as a filter. This should work for what you need to do: SELECT [Call_Num], [Call_Cust_Num], [Call_Cust Name], [Call_Status]...


1

Sounds like you're trying to insert rows to a table that already exists, in which case you'll need something like... INSERT INTO new_records SELECT * FROM new_table t JOIN new_record_ids r ON(r.id = t.id) --this assumes the result of the select has the same column layout as new_records... --which seems unlikely. replace (SELECT *...) with (SELECT column1, ...


1

Thank you @Greg for answering the question. The problem required that the SQL statement fully qualify the columns in the where clause by referencing the tables for the columns. In this case. I needed m_econ_fred_source=attrib_id. The complete solution is: SELECT [update], [value] FROM m_econ_fred_source JOIN s_econ_fred ON m_econ_fred_source.param_id = ...


1

Try this SELECT a.descendant_id , CASE a.path_length WHEN 0 THEN null ELSE a.ancestor_id END ancestor_id , CASE a.path_length WHEN 0 THEN null ELSE a.path_length END path_length FROM webineh_prefix_nodes_paths AS a JOIN ( SELECT descendant_id, MIN(CASE path_length WHEN 0 THEN 1000 ELSE path_length END) min_path FROM ( SELECT a.* ...


1

That should give you what you want: SELECT UT.Name, T1.*, T2.membership_no as 'Unmatching in membership_no Second table' FROM T1 INNER JOIN UT ON T1.user_id = UT.user_id INNER JOIN T2 ON T1.user_id = T2.user_id and T1.membership_no != T2.membership_no


1

Unless mysql is different from mssql I don't think that is the correct query SELECT c.id AS 'id', count(o.customer_relacional_id) orders, o.created_at FROM customer c LEFT JOIN order_2016 o ON (o.customer_relacional_id = c.id) GROUP BY o.customer_relacional_id ORDER BY o.created_at DESC this turns it into a regular join GROUP BY o....


1

So from what I can infer, your desired output is a column of all the dates from all 3 tables, with the 3 tables joined onto it. So something like Select dates.date, tbl1_col, tbl2.col, tbl3.col from (select distinct Date from table1 union select distinct date from table2 union select distinct date from table3 )dates --Now we have ...


1

SELECT A.id, BFather.codigo, BFather.Name, BSon.codigo, BSon.Name FROM TABLEA A JOIN TABLEB BFather ON A.codigoFatherTABLEB = BFather.codigo JOIN TABLEB BSon ON A.codigoSonTABLEB = BSon.codigo AND BSon.idPadre = BFather.id


1

SELECT p.*, c2.* FROM Parent p INNER JOIN Child c ON c.parent_id = p.id INNER JOIN Child c2 ON c.parent_id = c2.parent_id WHERE c.first_name LIKE '%abc%' OR c.last_name LIKE '%abc%'



Only top voted, non community-wiki answers of a minimum length are eligible