Tag Info

New answers tagged

0

I hope that you put values according to SQL standard, so it it can be queried. Then you can use where not exists clause to filter out according to your requirement. For example select id from items where not exists(select id from activities); Hope it helps.


2

I assumed you were using a pl/pgsql block (probably a function). For the generic case, you can use record, it can basically take a row of any structure, often used like DECLARE i record; BEGIN FOR i IN SELECT this, that, something, else FROM some_table LOOP ... END LOOP; There is also a possibility of defining a view: CREATE VIEW ...


1

Thanks for your help. I changed my query like this and it's working finde. 😊 SELECT DATES.DAT, RET.RETURN_AMOUNT, ORD.ORDER_AMOUNT FROM ( SELECT TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT FROM all_objects WHERE ROWNUM <= ...


1

You could join to the 2 tables separately (and GROUP BY) and then join them together: WITH dates (dat) AS ( SELECT TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat FROM all_objects WHERE ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy') - TO_DATE('02.05.2014','dd.mm.yyyy') + 1 ) , ret ...


0

SELECT `Label`, `Value`, ID FROM table1 LEFT JOIN table2 USING(Label, Value);


2

Option 1 will always force a O(N) operation (a table/index scan) which disqualifies this option for significant amounts of data. You must use option 2 if you want a solution that scales with the amount of data. If there are very few rows (such as 3 or so) option 1 might actually be faster. I doubt that we are talking about that case here.


2

Definitely you want to use option 2. Not only will your queries be faster (= is always faster than like) but you can also index on that keyword field for even faster queries AND your storage space will be significantly reduced since you are not storing the long keyword strings for each website.


0

I was able to achieve the update of all records my table, using the below set of commands : DECLARE @MemberNames TABLE(ID VARCHAR(50), Person VARCHAR(255)) INSERT INTO @MemberNames SELECT P.ID, u.NAME FROM dbo.tblProjects AS P CROSS apply dbo.fnSplitString(P.TeamMembers, ',') AS S LEFT OUTER JOIN Users u ON s.splitdata = u.EMAILID DECLARE ...


1

I would do this: SELECT d.currency, d.paymenttype, SUM(CASE WHEN d.paymenttype = 0 THEN d.amount ELSE 0 END) AS [Type0], SUM(CASE WHEN d.paymenttype = 1 THEN d.amount ELSE 0 END) AS [Type1], SUM(CASE WHEN d.paymenttype = 2 THEN d.amount ELSE 0 END) AS [Type2] FROM dbo.data AS d GROUP BY d.currency, d.paymenttype


7

If the list of payment types and currencies are known, you can do this with CTEs. with paymenttypes as ( select 0 as ptype, Union select 1 as ptype, Union select 2 as ptype ), currencies as ( select 'Aaa' as currency union select 'Bbb' as currency ), sourcedata as ( select currency, ptype as paymenttype, 0 as amount from currencies, ...


0

Try adding index on combincation of tableB (col2, ,col1) because selection is happening on col2. This should work.


0

Not simple: SELECT b.pers, t.description FROM BaseTable AS b CROSS JOIN ( SELECT DISTINCT test FROM TestTable WHERE grp = 'G1' ) AS d LEFT JOIN TestTable AS ti ON ti.grp = 'G1' AND ti.test = d.test AND ti.type = b.type LEFT JOIN TestTable AS t ON t.grp = 'G1' AND t.test = ...


0

According to given sample data there is "Type" which is common in both table Select B.PERS, T.DESC from BaseTable B right outer Join Testtable T on ( T.type is null or B.Type=T.type or B.Type is null) and T.Grp = 'G1' where T.DESC<>'' order by B.PERS asc


1

Without knowing what data you are starting with in each table, what is being produced and what is wrong/what you expected - I can only make some general observations to help you: 1)Code Formatting - Format your code better, use table aliases to shorten your code or at least tidy it up a bit. You will find it easier to read and therefore debug. 2)At a ...


0

Create Table CityWriter (City varchar(20), Writer varchar(20)) Insert into CityWriter values ('New York', 'Dylan'), ('Chicago', 'Pierce'), ('Los Angeles', null), ('Portland', 'Steinbeck') Create Table CityPark (City varchar(20), Park varchar(20)) Insert into CityPark values ('New York', 'Park1'), ('Chicago', 'Park2'), ('Los Angeles', 'Park3'), ...


1

Is it common to spread data of a single user operation across multiple rows? Think of the "entities": User, Review, Rating. A "user" may go to the restaurant many times, and give a "review" each time. The "review" may give ratings for multiple things, such as 'food' and 'location'. These are "Many-to-one" relationships, so Ratings has a review_id ...


2

Let's start by considering the downpayments and payments together. WITH AllPayments AS ( SELECT * FROM TableB UNION ALL SELECT * FROM TableC ) Now get the total and latest date. , YearlyDetails AS ( SELECT TableAID, YEAR([Date]) AS TheYear, SUM(Amount), MAX([Date]) AS LastDate FROM AllPayments GROUP BY TableAID, ...


0

If your attributes are more-or-less fixed, and you don't mind doing a code release when the attribute list changes, then unrolling them with JOINs and a COUNT would do it, too. select p.Id, a1.field_value, a2.field_value from People as p left outer join Attributes as a1 on a1.person_id = p.Id and a1.field = 'HairColor' left outer join ...


0

Here's a take using T-SQL syntax, which you could change to use LIMIT 1 rather than TOP 1 if you really need standard syntax. Hope this helps, but it looks like @ypercube has you covered either way. SELECT TOP 1 MIN(p.Id) AS id1, MIN(a.Id) AS id2 FROM dbo.People AS p CROSS JOIN dbo.Attributes AS a WHERE ...


3

One way to do this (check the SQLfiddle): select p1.id as id1, p2.id as id2 from people p1 join people p2 on p1.first_name = p2.first_name and p1.last_name = p2.last_name and p1.id < p2.id where not exists ( select 1 from ( select * from attributes a1 where a1.person_id = p1.id union all ...


3

Use a VALUES expression to generate an ad-hoc table and join to the other tables to get the respective IDs. INSERT INTO groupmember (person_id, group_id) SELECT p.person_id, g.group_id FROM ( VALUES ('alice'::varchar, 'girls'::varchar) , ('bob','boys', , ('alice','coolkids') , ('bob','coolkids') ) x (username, group_name) JOIN person p ...


0

This might be the ugliest thing I've written in a long time, but it returns your results. SELECT Amount.Id, Amount.Name, Amount.Amount, MaxOrder.MaxDate FROM ( SELECT a.Id, a.Name, SUM(Amount) AS Amount, a.[Year] FROM ( SELECT Id, Name, SUM(Amount) AS Amount, YEAR(Date) as [Year] FROM TableA as a ...


2

Build a table with n=1..5 and the comments (positive, etc); lets call it labels Get the counts (5 or fewer) Put together: SELECT labels.n, labels.comment, IFNULL(x.ct, 0) AS "count" FROM labels LEFT JOIN ( your big query to get the counts ) x ON x.n = labels.n ORDER BY labels.n The principle is "FROM (stuff you want all of) LEFT ...


2

No way! Every 'end' is not optimizable because of the leading wildcard. That means that if there are, say, a mere 3K 'end' entries in match, then there will be over a billion (350K * 3K) tests to perform! The query can be partially optimized by INDEX(type, string) -- in `match` INDEX(name) -- in `user` SELECT ... FROM user JOIN match ON ...


1

I would try breaking this down into three subqueries and then join the results using full outer joins. select distinct coalesce(a.name, b.name, c.name) from ( select user.name from [user] inner join match on user.name = match.string where match.type = 'exact' ) a full outer join ( ...


0

You can do this by putting the GROUP BY in a subquery. Here is a solution: CREATE TABLE #1 (ID int, TotalAmount decimal(20,2)); CREATE TABLE #2 (ID int, Individualamount decimal(20,2)); INSERT #1 VALUES (100, 1000), (101, 500); INSERT #2 VALUES (100, 500), (100, 300), (100, 100), (101, 200), (101, 300); SELECT a.ID , a.TotalAmount , ...


0

Teradata's optimizer quite possibly will re-write Method 3 to the same query plan as Method 2. Method 1 will result in an INNER JOIN because the qualification on the RIGHT table shouldn't be in the WHERE clause but the ON clause of the OUTER JOIN condition. If you were to place an aggregate step such as a DISTINCT or GROUP BY in the derived table of Method 3 ...


0

To get this result... 'mentor_id'|'# of people'|'# of distinct interactions' I think this is what you need... WITH X as ( SELECT mentor_id, count(*) AS [Num_of_mentees] FROM mentee_table GROUP BY mentor_id ), Y as ( SELECT mentor_id, count(*) AS [Num_of_interactions] FROM inter GROUP BY mentor_id ) SELECT DISTINCT(m.mentor_id) -- Ideally you ...


0

I see what your problem is you are duplicating the interactions because you are basically just trying to join those two tables together in your query. What you want to do is a correlated subquery to get the answer of how many interactions each mentor has had. Something like this. select a.mentor_id, count(distinct(a.mentee_id)) as UniqueMenteeCt,b.InterCt ...


0

You're basically talking about two entirely different technologies, used for two different purposes. A relational database stores information, commonly in a normalized fashion which is most suited for day-to-day processing and storage of business information. Given this storage model, you need joins to.. well, join those relational tables together when you ...



Top 50 recent answers are included