Hello,I would like to run an SQL query in order to get only the green rows and exclude red ones.
I want to exclude the rows that have SAG and SEG when they both have the same fields B and same fields b. Same as with C and c, etc.
I want to keep only the fields that have SAG and there is no matching field D and D1 for a SEG row. I would like to keep as well the first 2 SAG rows that have the same A field but different A1 and A2 fields.
My query as of now is something like this:The idea is to find the pairs of fields B-b, C-c ... and discard them.
Any help would be appreciated.
Many thanks in advance,
Vasilis
Ok, so if I understand correctly, if there's both a SAG and a SEG with the same ContainerNo, you want both rows removed from the output?
In that case, left join a second instance of the same entity (let's call it "Booking", as you haven't supplied it, so alias the second one as Booking_Seg), with
Booking_Seg.IKBookingNo = Booking.IKBookingNo and Booking_Seg.ContainerNo = Booking.ContainerNo and Booking_Seg.EventType = "SEG"
as join condition, and have EventType = "SAG" as Filter for the first instance of the entity. Then also add a Filter that says "Booking_Seg.Id = NullIdentifier()". Thus you'll get all SAG records that don't have a matching SEG record.
EDIT: Oh, you needed an SQL. Then convert the above to SQL syntax.
Hi Vasilis,
First, the SELECT in a sub query of an EXISTS should always be "SELECT TOP 1 1", as you're not interested in the result at all (any existing record is ok).
I find it a bit difficult to read your image with all the capital and lowercase letters. Also, since you didn't name the attributes in the image, I can't relate the image to the SQL query. Could you explain a bit more what data should be excluded, preferably using the right attributes/column names?
Hello Kilian,
So, the 1st column is the IKBookingNo and the last one is the ContainerNo.I would like to get rid of the rows that are like:
and keep the rows:
I hope I explained better my case but feel free to ask any further questions.Thank you once again.
Thank you very much for your solution! It seems to work until I cross check it from other sources. May I ask for a short explanation if it doesn't consume much of your time?Thank you once again :)edit: I thought that I could not make it through standard aggregates, that is why I was heading towards SQL.
This is a common scenerio in queries, to have only records that do not have some other record that depends on it. It's the opposite of selecting only records that do have some other record depending on it. The latter is easy for most people: join the entity again, using an inner join, so you know for certain that record A has a record B (because if they don't, the inner join makes sure such record As will not appear in the output).
The former case is very similar. But because you only want As without Bs, you use a left join instead of an inner join. And next you need to filter out all As that do not have a B, which means the left join didn't find anything, so B.Id is NULL (or NullIdentifier() in OutSystems).
If it's a 1:n relationship, so you have multiple Bs for every A, it's about the same, but you use a sub query: you join B on B.Id equals a SELECT of TOP 1 B.Id in the sub query, and have the other joins in the sub query, and the rest is the same. Downside is you need a SQL query, you can't do that in an aggregate.
Example:
SELECT {Invoice} LEFT JOIN {Invoice} Inv2 ON Inv2.[Id] = ( SELECT TOP 1 Inv2.[Id] FROM {Invoice} Inv2 WHERE Inv2.[ParentId] = {Invoice}.[Id] AND Inv2.[Status] = @StatusReturned ) WHERE Inv2.[Id] IS NULL
EDIT: come to think of it, even with 1:n you can use the former trick in an aggregate, as it will select multiple rows on the join, but you're filtering those out anyway :).
I cannot thank you enough! Have a great rest of the week!
You're most welcome! Happy I could teach someone a trick 😄. Have a great week, and happy coding!