New answers tagged join
0
What you want is called an UPDATE JOIN
First you must create a query that has the MAX(b_foo) for every a_uid:
SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid;
Making this a subquery, you can perform the UPDATE JOIN as follows:
UPDATE A INNER JOIN
(SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C
ON A.id = C.a_uid SET A.foo = C.max_b_foo;...
-2
Welcome on dba.stackexchange. Please be more specific in future request. But I still think I can extract the gist of your request:
Since too many questions concerning your request are unclear I just point you to this guide (myself not working on MS SQL):
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15
...
1
If you need to use a full outer join, use a full outer join. It exists for a reason. In general, though, it should be an uncommon tool in the toolbox.
When designing systems, you generally try to minimize the number of outer joins that you need to do. In this case, it would seem to make more sense to have a general transaction table that both ...
1
If your version of Oracle is 12 or above, you can use FETCH FIRST n ROWS ONLY (doc link):
SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF
FROM PRODUCT p
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001'
AND m.MANF_ID = (SELECT MANF_ID FROM MANUFACTURER man WHERE man.PRODUCT_ID = p.PRODUCT_ID ...
0
My problem was that after each update on relation_b, the trigger asks to update relation_b, so it calls itself and enter in a loop.
I solved it configuring the trigger to begin when one field is updated, except the field it updates :
CREATE OR REPLACE FUNCTION function1() RETURNS trigger AS
$$
BEGIN
UPDATE relation_b as b
SET field_1 = a.field_1
...
0
Try running the cases seperately. If that's faster you can work on combining them into one query. eg something like the not strictly-equivilent:
SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md
ON md.BranchID = rt.BranchID
AND (rt.EditEffectiveDate >= md....
0
Amazing. You have that many indexes on sql_reciept_sms (many of which are certainly superfluous), but you still managed to forget the crucial index for this query:
CREATE INDEX ON sql_reciept_sms (userid);
0
Just SET the value for field_1 BEFORE INSERT OR UPDATE. Just, make sure you use the alias for the new row of data.
needs to be edited for postgresql syntax
CREATE TRIGGER trigger1
BEFORE INSERT OR UPDATE
ON relation_b
FOR EACH ROW
begin
-- "new" represent "current row being inserted/updated"
-- just modify the values BEFORE it is stored in ...
1
As documented in the manual you should not repeat the target table in the FROM clause.
So your UPDATE should be like this:
UPDATE public."section" s
SET column = evt.oldcolumn
FROM public."event" evt
WHERE s.event = evt.id
AND s.id = 1;
1
Disclaimer, I don't use PostgreSQL but I'm pretty familiar with SQL Server.
From what I understand in the PostgreSQL documentation here, I think the error you are getting is caused by the inclusion of public."section" INNER JOIN in your FROM statement. When using UPDATE you specify the table you are updating so adding public."section" in the FROM is not ...
0
As indicated in comments these queries are not equivalent. To understand why the are different, let's examine them:
create table A (x int);
insert into A(x) values (1);
create table B (x int, y int);
insert into B(x,y) values (2,2);
select A.x, B.x, B.y
from A
left join B
on A.x = B.x
and B.y = 2;
Since no rows match the join predicate, B.x and B....
-2
This is a complete example, but check the update sentence at the end also check this example
DECLARE @section TABLE
(
id INT PRIMARY KEY,
event int,
[column] int
);
INSERT INTO @section(id, event,[column])
VALUES
(1,3,77),
(2,2,55),
(3,4,88),
(4,6,44),
(5,1,33);
DECLARE @event TABLE
(
id INT PRIMARY KEY,
...
1
I had just asked that on a different forum. The best solution for a 3rd Normal Form schema design was to select all that is needed in the linking table and compare the Cardinality (count) of the matching results.
The other forum was for Oracle. The answer you seek is the PostgreSQL version of this post: https://community.oracle.com/message/15613817#...
1
You never ANALYZEd the foreign tables, so the estimates are way off, and PostgreSQL chooses a bad execution plan.
ANALYZE tds, dq_infos;
should improve the situation.
Different from regular tables, foreign tables are not handled by autoanalyze.
1
Did you try a lateral join?
select parent.id, parent.name, q.cnt
from parent
left join lateral (
select count(*) cnt
from child ch
where ch.pid = parent.id
) q on true
where name like '%xyz%'
0
Why not simple
SELECT parent.id,
parent.name,
COUNT(child.pid) cnt
FROM parent
LEFT JOIN child ON parent.id = child.pid
WHERE parent.name LIKE '%xyz%'
GROUP BY parent.id,
parent.name
?
Anycase fullscan by parent (due to LIKE '%...%') needed...
1
For example you can do it this way:
select p.id, p.name,
COALESCE((select count(*) from child where p.id = pid), 0) AS cnt
from parent AS p
where name like '%xyz%'
or this:
select p.id, p.name,
count(c.pid) AS cnt
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id, p.name
if you need more columns from parent:
...
1
Assuming all the columns are compatible:
SELECT * from table1
UNION ALL
SELECT table2.* from table2 left join table1 using (customer_id, date)
where table1.customer_id is null
If not all the columns are compatible, you will have to do a bit of tedious manipulation of the select lists, rather than using *.
1
All elements of an array must have the same type; when constructing an array with a subquery, the simplest way to enforce this is to demand that the query returns exactly one column.
But you can make the subquery return a single column whose type is a composite type by using a row constructor:
ARRAY(SELECT ROW(name, type, description) FROM ...)
1
LEFT join says "take all rows from the left i.e. preceeding table i.e. SwitchportMac_import". You don't want all rows from that table, only the ones that do not match.
Adding the IS NULL will work. I think a NOT EXISTS would better suit your needs here.
3
from SwitchportMac_import i LEFT OUTER JOIN SwitchportMac sm
This returns rows in i which match in sm.
Correct. That's exactly what a LEFT [OUTER] JOIN does - it gets you everything in the "left" table (i) whether or not it can find a matching row in the "right" table (sm).
To get "new" items (those that appear in the "left" table but not the "...
10
LEFT JOIN and LEFT OUTER JOIN are synonymous so apologies but it sounds like option 2 :-)
The "Outer" join (which preserves unmatched rows) is as opposed to "Inner" (which doesn't).
There are three flavours of outer join. Left, Right, Full (dependent on which unmatched rows are to be preserved).
As SQL doesn't have direct join syntax for any other types ...
4
This would be my preferred way to get what you want, using a correlated sub-query with EXISTS. Assuming you have an index on both tables on switch, port and mac, this should be good performance as well.
SELECT *
FROM SwitchportMac_import i
WHERE NOT EXISTS (
SELECT TOP (1) 1
FROM SwitchportMac sm
WHERE sm.switch = i.switch
and sm....
1
where sm.id is null is needed to match the rows that don't exist in the join.
Left joins are always OUTER so its syntactic sugar invented by committee. They where thinking of confusing you and others ahead of time. You're not a idiot, confusion was implemented as planned.
1
I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to
produce the desired result
FULL JOIN works fine. Maybe you were joining on the wrong predicate or didn't coalesce the results from the ID and Date columns (done with ISNULL below)?
db <> fiddle
SELECT ISNULL(T1.[ID], T2.[ID]) AS [ID],
ISNULL(T1.Date, T2.Date) AS Date,
...
1
Try this one
with cte as (
select metric_id, value, datetime, ROW_NUMBER () OVER (PARTITION BY metric_id ORDER BY datetime desc) as rn
from his_metric
)
select n.ip, m.id, m.key, m_h.value, m_h.datetime
from metric as m
join node as n on m.node_id = n.id
join cte as m_h on m.id = m_h.metric_id and m_h.rn=1
Top 50 recent answers are included
