Tag Info

New answers tagged

-1

SELECT SUM(t1.QUALITY)from t1 LEFT OUTER JOIN t2 ON t2.PRODUCT_ID = T1.PRODUCT_ID WHERE t2.MATERIAL = 'metal';


0

You can make it with a simple JOIN: SELECT SUM(table1.quality) as qlty, GROUP_CONCAT(table1.product_id ORDER BY table1.product_id ASC) AS all_products_id_matching FROM test.table1 JOIN test.table2 ON (table2.product_id=table1.product_id) WHERE table2.material='metal'; OR a subquery: SELECT SUM(table1.quality) as qlty FROM test.table1 WHERE ...


3

I'm not sure if I understand the relation between the tables but if I did this should work: SELECT SUM(quality) FROM table1 WHERE product_id IN (SELECT product_id FROM table2 WHERE material = 'metal')


0

Select a.* From table1 a Left join table2 b on a.id=b.id Where b.material="metal" I'm assuming ID is the common denominator, and not your product id.


0

you can also achieve this result by using Pivot as below. ;with cte as ( SELECT sq.survey_cat_id, sd.Store_id,answer FROM survey_details sd INNER JOIN survey_question sq ON sd.ques_id = sq.ques_id ) Select * from cte PIVOT( COUNT(answer) FOR answer IN ([excellent], [very ...


0

What you must do is increase the join_buffer_size. What is join_buffer_size ? The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full ...


1

I've modified your query and I used CASE statement to COUNT: Query: select survey_categories.survey_cat_id, survey_question.ques_id, survey_details.store_id, count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent, count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good, count(CASE WHEN ...


4

I am not entirely certain that I understand the semantics of your database design but foreign keys have a special meaning in a database. A foregin key, investors_id in the Users table, is a constraint saying that if a value appears in the investors_id column in the Users table, then that value must appear once in the Investor table id column. My ...


2

Another way would be to use EXCEPT like this: SELECT u.id AS user_id, s.id AS song_id FROM users AS u CROSS JOIN songs AS s EXCEPT SELECT user_id, song_id FROM songs_played ; The result would be all u.id, s.id pairs that are not found in songs_played.


3

You can use not exists clause to do this. SELECT u. ID AS user_id, s. ID AS song_id FROM songs s, users u WHERE NOT EXISTS ( SELECT 1 FROM user_songs us WHERE us.user_id = u. ID AND us.song_id = s. ID ) ORDER BY 1, 2


0

At a glance, your method of enforcing the integrity of the lifecycle as you have done seems like a good idea. Using the same key for each table would make it possible to have an entry in a later stage without an entry in an earlier stage. Carr's Law of Data Integrity (which, of course, is just a version of Murphy's Law) is: if it is possible, it is ...


-4

If the group by clause is missing (could be a copy & paste error) , this could be the reason


0

With the JOINS you can use the Primary Key and Foreign Key to link the tables together, but to answer your question in regards to using a bunch of different views, I would almost say that disqualifies the purpose of a view. I would create a view called Inventory_Lifecycle and include all of the tables involved to obtain all the information you would need to ...


0

Will it work for you ? SELECT a.id as question_id, a.costperlead, a.costperlead *(b.numYes+b.numPossible) as revenue, b.numYes,b.numNo,b.numPossible FROM questions a LEFT JOIN ( SELECT a.question_id, count(case when a.response='Yes' then 1 end)AS numYes, count(case when a.response='No' then 1 end) AS numNo, count(case when a.response = 'Possibly' then 1 ...


3

Is it possible? Sure. Is there likely to be an improvement in performance? No. If there is a change in performance (barring cases where you discover that a join is missing or otherwise fix a query), it'm more likely that the old implicit join syntax will be more efficient. But that's pretty unlikely. Behind the scenes, when you have a query using the ...


1

I hope to have fully understood your request. With this query you get a concatenation of tables (a UNION) with all records. SELECT parent_id, target_id, start_date, end_date, NULL AS jobposition_id FROM study UNION ALL SELECT parent_id, target_id, start_date, end_date, jobposition_id FROM job You can play with UNION to obtain the merging concept you have ...


1

Your query uses SELECT * ... which will select every column from the tables (in the order that they exist within the tables). So if you have: CREATE TABLE t1 ( id bigserial primary key, -- bleh - never use just "id" .. this is only for example desc text ); CREATE TABLE t2 ( id bigserial primary key, t1_id bigint references t1 (id), desc text ); ...


2

Select pr.Color, count(*), sum(sr.UnitPrice * sr.OrderQty) as total from Production.Product as pr Join Sales.SalesOrderDetail as sr on sr.ProductID=pr.ProductID WHERE pr.Color IS NOT NULL GROUP BY pr.Color HAVING sum(sr.UnitPrice * sr.OrderQty) >50000 ;


1

You can use the SYS_CONNECT_BY_PATH hierarchical function to generate a list of parent IDs from the tree root to the current record, and the level pseudo column to count the number of members in the list (+1). In the next stage, the regexp_substr function extracts the Nth path element from the ath for display as the Nth parent_Id: var nth number exec :nth ...


0

Here is another option that only takes a single pass over the data by performing the left join and then, for each resulting row, tacking on the NULL row if necessary. SELECT Results.* FROM #Table1 T1 LEFT OUTER JOIN #Table2 T2 ON T1.Key1 = T2.Key1 CROSS APPLY ( SELECT T1.C1, T1.C2, T2.C1 AS T2_C1, T2.C2 AS T2_C2 UNION ALL SELECT T1.C1, ...


1

According with your last edit: SELECT F.*, C.* FROM db1.Fields AS F JOIN db1.Content AS C ON (C.ID = F.ID AND C.ForUser = F.ForUser AND C.ForCategory = F.ForCategory) WHERE `the field you want to filter`=`the value you want to search`; You can filter with Any field in Fields or Content.


2

I see 3 ways to do this but all involve a UNION ALL: your version`: SELECT T1.C1, ....., T1.CN, T2.C1, ..., T2.CM FROM Table1 T1 JOIN Table2 T2 ON T1.Key1 = T2.Key1 UNION ALL SELECT T1.C1, ..., T1.CN, NULL, ... NULL FROM Table1 T1 ; slightly changing the second part: SELECT T1.C1, ....., T1.CN, T2.C1, ..., T2.CM ...


3

The first of all, you should have try first with some query. # First table CREATE TABLE t1( DAD_ID int(6) auto_increment, employee_id INT(6), CALL_START datetime, CALL_END datetime, SWITCH CHAR(2), DISP_ID int(4), DNIS CHAR(6), DISP_DESC VARCHAR(75), PRIMARY KEY (`DAD_ID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; # Second table CREATE ...


4

Is it still outer join? Or is it becomes inner join? It is still an outer join: SQL Fiddle Oracle 11g R2 Schema Setup: create table tab_a(num integer); create table tab_b(num integer); insert into tab_a(num) values(1); insert into tab_a(num) values(2); insert into tab_b(num) values(2); insert into tab_b(num) values(3); Query 1: select * from ...


3

I know that I should test my SQL script as well, but I don't have time to make a test before leaving for work! I can make a SQL Fiddle later, but for now it seems that no one has quite answered the question correctly. SELECT COALESCE(t1.cod, t2.cod) cod, t1.text1, t2.text2 FROM t1 FULL OUTER JOIN t2 ON t1.cod = t2.cod ORDER BY cod; If you want to test ...


2

select t1.cod, t1.text1, t2.text2 from t1 full outer join t2 on t1.cod=t2.cod


1

Use "full outer join". Your query should like, select * from t1 full outer join t2 on t1.cod=t2.cod


0

To get the output you mentioned, you may run the following query. Basically, it is the same as yours with GROUP BY added: SELECT `new`.`SRPartsID` AS `SRPartsID`, `new`.`RepairID` AS `RepairID`, `new`.`SRNo` AS `SRNo`, `new`.`DateReceived` AS `DateReceived`, `new`.`ShipmentDate` AS `ShipmentDate`, ...


8

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or in the start of the function definition, declaring how you want such conflicts to be resolved (the 3 possible values are error (the default), use_variable and use_column): CREATE OR REPLACE FUNCTION ...


5

Use a FULL [OUTER] JOIN, combined with two rounds of window functions: SELECT ts , min(foo) OVER (PARTITION BY foo_grp) AS foo , min(bar) OVER (PARTITION BY bar_grp) AS bar FROM ( SELECT ts, f.foo, b.bar , count(f.foo) OVER (ORDER BY ts) AS foo_grp , count(b.bar) OVER (ORDER BY ts) AS bar_grp FROM foo f FULL JOIN bar b ...



Top 50 recent answers are included