Hi all so I have a homework assignment that I am having difficulty understanding. the problem I have been given is this:
ix. Referring to the customers and shipping_details tables on the ER, display all data from the customers table but only where the province on the shipping_details table is equal to NS. Do not use a join for this requirement.
So I can complete the question easily enough using a join but I am having difficulty understanding how one can reference another table without using a join. I am trying to use a vector aggregate function but I am very unclear on how to reference a table and a value within a column of that table without using a join
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Since you don’t need to return any data from the shipping_details table, you can use a semi-join, which can be written with either
IN and a subquery or
EXISTS and a correlated subquery.
-- Using IN -- select c.* from customers as c where c.shipping_detail_id IN ( select sd.shipping_detail_id from shipping_details as sd where sd.province = 'NS' ) ; -- Using EXISTS -- select c.* from customers as c where EXISTS ( select 1 from shipping_details as sd where sd.province = 'NS' and sd.shipping_detail_id = c.shipping_detail_id -- correlation ) ;
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0