-1

I have a query that joins to the same table more than once and it requires over 2 dozen table aliases. However, it is not returning all the results. Here is the query. 

Select 
bracketID_1, bracketWins_1, a.logo as bracketID1_logo, bracketID_2, bracketWins_2, b.logo as bracketID2_logo, bracketID_3, bracketWins_3, c.logo as bracketID3_logo, 
bracketID_4, bracketWins_4, d.logo as bracketID4_logo, bracketID_5, bracketWins_5, e.logo as bracketID5_logo, bracketID_6, bracketWins_6, f.logo as bracketID6_logo, 
bracketID_7, bracketWins_7, g.logo as bracketID7_logo, bracketID_8, bracketWins_8, h.logo as bracketID8_logo, bracketID_9, bracketWins_9, i.logo as bracketID9_logo, 
bracketID_10, bracketWins_10, j.logo as bracketID10_logo, bracketID_11, bracketWins_11, k.logo as bracketID11_logo, bracketID_12, bracketWins_12, l.logo as bracketID12_logo, 
bracketID_13, bracketWins_13, m.logo as bracketID13_logo, bracketID_14, bracketWins_14, n.logo as bracketID14_logo, bracketID_15, bracketWins_15, o.logo as bracketID15_logo, 
bracketID_16, bracketWins_16, p.logo as bracketID16_logo, bracketID_17, bracketWins_17, q.logo as bracketID17_logo, bracketID_18, bracketWins_18, r.logo as bracketID18_logo,
bracketID_19, bracketWins_19, s.logo as bracketID19_logo, bracketID_20, bracketWins_20, t.logo as bracketID20_logo, bracketID_21, bracketWins_21, u.logo as bracketID21_logo,
bracketID_22, bracketWins_22, v.logo as bracketID22_logo, bracketID_23, bracketWins_23, w.logo as bracketID23_logo, bracketID_24, bracketWins_24, x.logo as bracketID24_logo,
bracketID_25, bracketWins_25, y.logo as bracketID25_logo, bracketID_26, bracketWins_26, z.logo as bracketID26_logo, bracketID_27, bracketWins_27, aa.logo as bracketID27_logo,
bracketID_28, bracketWins_28, ab.logo as bracketID28_logo, bracketID_29, bracketWins_29, ac.logo as bracketID29_logo, bracketID_30, bracketWins_30, ad.logo as bracketID30_logo

from YearlyPlayoffResults

join teams a on YearlyPlayoffResults.bracketID_1 = a.Abbreviation
join teams b on YearlyPlayoffResults.bracketID_2 = b.Abbreviation
join teams c on YearlyPlayoffResults.bracketID_3 = c.Abbreviation
join teams d on YearlyPlayoffResults.bracketID_4 = d.Abbreviation
join teams e on YearlyPlayoffResults.bracketID_5 = e.Abbreviation
join teams f on YearlyPlayoffResults.bracketID_6 = f.Abbreviation
join teams g on YearlyPlayoffResults.bracketID_7 = g.Abbreviation
join teams h on YearlyPlayoffResults.bracketID_8 = h.Abbreviation
join teams i on YearlyPlayoffResults.bracketID_9 = i.Abbreviation
join teams j on YearlyPlayoffResults.bracketID_10 = j.Abbreviation
join teams k on YearlyPlayoffResults.bracketID_11 = k.Abbreviation
join teams l on YearlyPlayoffResults.bracketID_12 = l.Abbreviation
join teams m on YearlyPlayoffResults.bracketID_13 = m.Abbreviation
join teams n on YearlyPlayoffResults.bracketID_14 = n.Abbreviation
join teams o on YearlyPlayoffResults.bracketID_15 = o.Abbreviation
join teams p on YearlyPlayoffResults.bracketID_16 = p.Abbreviation
join teams q on YearlyPlayoffResults.bracketID_17 = q.Abbreviation
join teams r on YearlyPlayoffResults.bracketID_18 = r.Abbreviation
join teams s on YearlyPlayoffResults.bracketID_19 = s.Abbreviation
join teams t on YearlyPlayoffResults.bracketID_20 = t.Abbreviation
join teams u on YearlyPlayoffResults.bracketID_21 = u.Abbreviation
join teams v on YearlyPlayoffResults.bracketID_22 = v.Abbreviation
join teams w on YearlyPlayoffResults.bracketID_23 = w.Abbreviation
join teams x on YearlyPlayoffResults.bracketID_24 = x.Abbreviation
join teams y on YearlyPlayoffResults.bracketID_25 = y.Abbreviation
join teams z on YearlyPlayoffResults.bracketID_26 = z.Abbreviation
join teams aa on YearlyPlayoffResults.bracketID_27 = aa.Abbreviation
join teams ab on YearlyPlayoffResults.bracketID_28 = ab.Abbreviation
join teams ac on YearlyPlayoffResults.bracketID_29 = ac.Abbreviation
join teams ad on YearlyPlayoffResults.bracketID_30 = ad.Abbreviation

However, it only returns random years and not in a particular order either. Also, when I add a where clause to return a specific year, some years work and others don't and the years that go missing do exist in the table.

Jason
  • 119
  • 8
  • 1
    Seems like the problem is your table design; are you open to fixing it? – Thom A Sep 22 '19 at 18:49
  • Absolutely. Please advise. However, I don't think there are logic errors in the table design though. The results that do appear are in fact accurate. It's just not returning all of the results. – Jason Sep 22 '19 at 18:58
  • Comment out half the joins, run the query. Continue until you find the join that causes no results. – David Browne - Microsoft Sep 22 '19 at 19:00
  • 4
    The problem is you have non-normalised data. Why do you have 30 bracket columns? You should have 2; the bracket number and the value you currently store in the 30 different ones; resulting in many rows. – Thom A Sep 22 '19 at 19:01
  • I do believe my data is normalized though. I'm not using an INT in the joins because every team abbreviation is unique, and when it comes to designing a query or looking at results...it's far easier going by team abbreviation as opposed to a number. I rather see 'LAL' for the Lakers instead of 14.Please advise. Honest question, what makes it not normalised? – Jason Sep 22 '19 at 21:12
  • "Normalized" has 2 senses, "... to 1NF" & "... to higher NFs". Neither involves replacing (subrow) values by (somehow more id-ish) surrogate values. [Notions of "1NF"](https://stackoverflow.com/a/40640962/3404097) basically all say, replace tables or columns with names that differ by parameters by one with a column per parameter. @Larnu means that. PS Time to follow a published academic textbook on information modelling, the relational model & DB design & querying. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) – philipxy Sep 23 '19 at 01:26
  • 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. 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/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. PS Clearly there is non-minimal code/data here. (But much missing.) – philipxy Sep 23 '19 at 01:32

1 Answers1

1

While @Larnu is correct that you need to normalize your data, in the meantime you can find the data problem by replacing your JOINs with LEFT JOIN. It will show which column(s) have a NULL record, which is causing your data to be "missing".

It's possible that this is "expected" results in that, if you have an odd number of teams, it's possible that some brackets might be skipped.

daShier
  • 2,056
  • 2
  • 8
  • 14
  • 1
    Great! I would still recommend you look into creating a table to link the `team` table with the results in a few columns: one for the `bracketID` and perhaps two others for the two `team_Abbreviation` IDs that played in that bracket. and finally one or two more for the winner, or perhaps the two scores (if you need that level of detail). You can then have as many brackets as you need. But in the meantime, if this solved your immediate problem, you might want to mark the answer "accepted". Cheers! – daShier Sep 22 '19 at 19:23
  • Honest question, why would I create another table when this query(fixed btw, ID mismatch) can give me those same results? – Jason Sep 22 '19 at 21:02
  • What this query result does is give me the entire NBA playoff results for the past 35 years. Each row represents a year. I'm using this data to makeup an object on the code side of things in ASP.net. That object, will then complete the bracket you see on the front end. – Jason Sep 22 '19 at 21:07
  • 1
    Actually, I now fully understand what you guys mean! That was a pretty inefficient and confusion way of doing this. I am redesigning the table. I did solve the issue with the previous, but continuing to run different data on on that table as is would be a complex and unnecessary nightmare. Thanks again guys! – Jason Sep 24 '19 at 21:32