-1

I have the following tables populated with these records:

enter image description here

I have created a view that looks like this and have selected all records from it:

enter image description here

However, the results are not as expected. Each store location is matched with each craft item, even if they are not supplied to that store.

enter image description here

Even regions that don't have recorded stores display records:

enter image description here

I imagine this has something to do with the natural join being mixed with the left outer join, but I don't understand why.

Angel Davis
  • 7
  • 1
  • 2
    Welcome to Stack Overflow. Please don't post code or data as images - it makes it very difficult to try to reproduce your problem. Instead, post them as code-formatted text - you can get code formatting by indenting each line by four spaces. And yes, mixing NATURAL or CROSS JOINs with OUTER JOINS can give unexpected results. I suggest you change the NATURAL JOIN to a LEFT OUTER JOIN. – Bob Jarvis - Слава Україні Nov 29 '19 at 22:02
  • 2
    @AngelDavis . . . Simply **Say No** to "natural join"s. They are not natural at all, because they do not use properly declared foreign key relationships. They make queries really hard to debug, because the `join` keys are not explicit. Use `using` or `on`. – Gordon Linoff Nov 29 '19 at 22:27
  • You don't clearly say what what function of the input your query is supposed to return, so how can we help you correct it? Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. – philipxy Nov 29 '19 at 22:39
  • 1
    Don't worry re "mixing", learn definitions of operators & find out what your subexpressions return. Your code is the same as `cr left join cs using (cri) inner join csi using (columns common to both 'cr left join cs using (cri)' & 'csi')`. Is that what you want or not? Etc. PS Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. PS [Inner Join vs Natural Join vs USING clause: are there any advantages?](https://stackoverflow.com/a/35034568/3404097) – philipxy Nov 29 '19 at 22:50
  • 1
    Hi AngelDavis, I must apologise (to you as a newbie) that you've stepped into the middle of a raging debate in Relationland, about the merits of Natural Join. @Gordon is correct to point out that in SQL NatJoin is problematic; that's because almost everything in SQL is problematic: it's a pile of crud that should have been strangled shortly after birth. All that's needed for NatJoin to work effectively is careful naming of columns (it has very little to do with Keys or FKs). Your schema _is_ carefully named; `Natural Join` is ideal for the intention in this query. – AntC Nov 30 '19 at 03:10
  • 1
    @AntC Indeed they would ideally be both NATURAL or both not. – philipxy Nov 30 '19 at 03:14
  • 1
    `Left Join` is not suitable if you don't want "regions that don't have recorded stores". It's not the "being mixed" that's the problem. @BobJarvis' comment is just plain wrong: for this query do not use `LEFT (OUTER) JOIN` at all. Indeed changing `left join` to `natural join` would precisely avoid showing "regions that don't have recorded stores". – AntC Nov 30 '19 at 03:15

1 Answers1

1

And based on all the comments others have provided, and you may not be comfortable explicitly with the syntax, think of LEFT side as the first table of a query and right is the second. So a left join implies everything from the left-side table regardless of a match on the other, but if one exists, it only exists based on the matching criteria/condition. For what you have, you are probably looking for something like...

create or replace view detailedCraftRegaion as 
select
      cr.CraftRegionDescription,
      cs.StoreAddress,
      cs.StoreCity,
      cs.StoreState,
      cs.StoreZipCode,
      csi.CraftItemName

   from
      CraftStore cs
         JOIN CraftRegion cr
            on cs.CraftRegionID = cr.CraftRegionID
         JOIN CraftShipItems csi
            on cs.CraftStoreID = csi.CraftStoreID
Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
DRapp
  • 47,638
  • 12
  • 72
  • 142