Tag Info

Hot answers tagged

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 ...


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 ...


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 ...


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')


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


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 ...


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 ...


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 ...


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 ...


2

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


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.


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

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 ...


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 ...


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.


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 ); ...


1

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



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