I am trying to join all the elements in one table to a portion of another table. The join happens, then the restrictive where clause; I am trying to get the restrictive where clause to run first, then the join… I want to filter out the stuff I don’t want from tables AL2 and AL3, and THEN join them to AL1 with an outer join. If I put (+) next to the AL2 and AL3 and clauses, the query works, but it will not let me save the format (it eliminates the (+)'s. What is the current version of SQL syntax for this?
SELECT AL1.PORTFOLIO_CODE,
AL2.PORTFOLIO_GROUP_ID,
AL1.PORTFOLIO_NAME,
AL1.PORTFOLIO_TYPE_CODE,
AL3.CODE_DESCRIPTION
FROM A.ENTITY_VIEW AL1, A.ENTITY_LIST_VIEW AL2, A.CODES_VIEW AL3
WHERE (AL1.PORTFOLIO_CODE = AL2.PORTFOLIO_CODE(+))
AND (AL1.ENTITY_ID_UNTRIMMED = AL3.CODE_VALUE(+))
AND ( ( AL2.PORTFOLIO_GROUP_ID = ‘DLYPRICE’ Note: can add (+) after ID and it works.
AND AL3.CODE_INTERFACE = 'PROD ') Note: same here after interface
AND AL3.CODE_TYPE_SHORT_DESC = 'PORTPRICSRCE ') Note: same here after desc
hi Barbgentile,
What SQL provider do you use?
Have you tried to explicitly use left joins on each table with its own ON clause?
select …
from table1 as t1
left join table2 as t2 on t2.id = t1.id and t2.myColumn = ‘MyValue’
left join table3 as t3 on t3.id = t2.OtherId and t3.SomeColumn = ‘SomeValue’
where t1.CreateDate between ‘2017-05-13’ and ‘2017-05-17’
?
That worked great and the query ran as I wanted, but when I hit the Visualize button, the additional and clauses right after the joins disappeared. I can’t save the query unless I hit Visualize.
Could you please describe your scenario in more detail? Which modules are you using? Where are you editing the SQL code? Because now you mentioned that you use the Visualize button/option and I am not sure what do you need to save / achieve here.
Also you might want to try sub queries… Such as:
select t1.Name, t2.*
from table1 as t1
join (select addressId, City, Country, Address1 + ’ ’ + Address2 as FullAddress from Addresses where myColumn = ‘myValue’) as t2 on t2.addressId = t1.AddressId
where t2.City = ‘London’
Let me know how this goes,
Martin
I am using Toad Data Point V 4.1.0.226, and I am writing my query in the Query Builder “Query” section (as opposed to the Diagram section). The sub queries work for the most part. I am trying to left join one table to a few other tables, so I added three sub queries/left joins. When I run the query it is fine, but when I hit “Visualize”, Toad switches the order of the tables so that table 2 is first, then table 1. Are all the joins to the first table? Or are they to the combined data set? Regardless, the sub query idea was a great help, and I just have to find the order that Toad likes. Thank you for your help!
The use of (+) for outer joins is an older syntax. Ansi join syntax uses the words of " outer join", etc. The Query Builder has a toggle button that switches the syntax from ansi to non-ansi joins. Try deselecting ansi joins and see if this behavior goes away.
When I remove the ANSI joins, Toad puts the (+)'s back in, and I get an error message that a table can be outer joined to only one other table. The ANSI joins work better.
Yes, ANSI joins are the preferred syntax. Are you good now?
When your join condition uses And’s in addition to the On it is a complex join. Toad can have trouble visualizing complex joins sometimes, specially multiple complex joins. Sometimes if you add one complex join visualize, add another, visualize, etc., you can get it to work. It works better the other way. Drag and drop your tables in query builder, create your complex joins there and your done. Then go look at the code if you want. I use complex joins all the time and so mostly use editor (and don’t bother visualizing hardly anything anymore). If you are still learning SQL, QueryBuilder is great, just keep it simple if you are going backwards (visualizing code).