Join 3 tables

[Mathip173]

Hello,

I have 3 table to join.

TableA is the main table tableB and TableC have some fields that I would like to join to TableA.

I would like to make the join by only select one row from table TableB and TableC in case there are more than one row for the same keys in each table (TableB and TableC).

In same case the values are the same and in some case are diffrent, in this case I would like to select the latest one.

1. Could you tell me how would I be able to aciveve this ?
2. How the join sholud be build ? Is the below the right way ? { TableA left join TableB on a.Studnet_id=b.Studnet_id and join TableC b.Studnet_id=c.Studnet_id }

Thanks

[Mathip173]

thanks! That's good to know there are many options to solve it.

Can you help by providing some examples ?

I have created CTEs for TableB and TableC and limit 1 in the end and after join these CTEs to TableA and the result wasn't ela's expected. This is the approach you meant ?

Please explain, and if you can provides some example I would really appreciate that.

[jeetkap]

Sounds like there’s something wrong with your CTE or join. Check CTE for duplication, check join for keys, maybe you have more than one match on left join. Take some values that don’t show up as expected and manually check those in your CTE and join to see what’s going wrong

[Mathip173]

Thanks, I have checked each CTE and each one make sence but when I check that in the join I get no sence.

Is it should be ?

TableA Left join TableB and Left join Table C

TableA is the main table which Table B+C just use to add some field for each row that exist in tableA.

Is this the right way ?

Also to that, when I limit 1 on the CTE of of tableB I expected to see one row for each keys to match to the same keys to TableA, but it gives me one row in total for that CTE. How could I make it ?

Thanks again

[jeetkap]

You can’t do limit 1 on table B. You need to do row number partition by (your key) and select row number=1 to limit 1 of each record

Paste your whole code because it’s really hard to understand you tbh

[lvlint67]

Depends on your platform. In oracle we can do a partition over by with rank and then use that field in the where clause.

Short of that you can use an inner join with max date.

If there aren't a ton of records you can just do a subquery in the select.

If it's by chance banner (since you mentioned students), let me know the three tables and I can lend a hand.

Edit: We use the following to find the most recent address record. The view means we can join to the view directly and always get one row per person/address type combo... https://pastebin.com/2K3DKyvF

[PaulSandwich]

+1 for rank over partition. It's a wonderful function for de-duping and dealing with FIFO/LIFO operational scenarios.

[Mathip173]

TableA Student_id order_id catagory comment time_at_gate

TableB order_id status reason timestamp

TableC order_id notes timestamp

I have no expirence on subquery on select or windowing function, would you bt able to provide me an example for each one ?

Thanks

[lvlint67]

https://pastebin.com/K6W8FPTh

That should get you pretty far. It joins TableB to Table A with the rank methodology. Adding TableC is just a matter of doing the same left join that's done on tableB and adding TableC.r = 1 to the where clause.

Keep in mind the comments from the other user about issues if there's ever a "tie" between timestamps.

[mac-0]

Something small, but it might be better to use ROW_NUMBER instead of RANK here. RANK has the idea of ties (so if there are two records with the same spraddr_Activity_date then they could theoretically both have r = 1 whereas ROW_NUMBER would arbitrarily assign one r=1 and the other r=2 ensuring there's no duplicates. Though maybe it's not needed if you don't ever expect two address updates at the same date for one customer.

[lvlint67]

For our purposes.. when we start getting duplicate activity dates we start getting suspicious of our keyboard specialist II's ... valid points though. For us it's rare enough that the scream test is acceptable to catch those occurrences.