Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jul 2013
    Posts
    9

    Unanswered: show users whether btheyve done traing or not!

    I'm working on a training program and I'm at a point where I can’t figure out the SQL query. I have 4 tables: userProfilesTbl, trainingTbl, userAssessmentTbl and setsTbl userProfilesTbl and userAssessmentsTbl are related through the UserId fkey which is a unique identity field type.

    trainingTbl and userAssessmentTbl are related through the tt_id fkey INT field type. setsTbl and trainingTbl are related through the s_id fkey INT field type.

    The trainingTbl is populated with training carried out on specified dates by specified user groups.

    The userAssessmentTbl is populated by the individual users training (outcomes, notes etc).

    The userProfilesTbl holds all additional user information not included in the membership and roles framework (connects to aspnet_users table).

    The setsTbl is populated by a list of the different types of training.

    I want to show all training sets and users regardless if they’ve done the training or not.

    The select statement shown displays all of the training sets (setName from the setsTbl ) along the Y axis. However the Gridview (which is a PIVOT table) should also be returning surName, from the userProfilesTbl, and outcome (value) from the userAssessmentTbl This will show the users if they've carried out training if they have not carried out any training the user the user disappears from the top x axis!

    SELECT * FROM userProfilesTbl RIGHT OUTER JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId FULL OUTER JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id RIGHT OUTER JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE (userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id) AND (DATEPART(mm,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ) OR t_date IS NULL ORDER BY setName ASC, t_date ASC, surname ASC

    any help would be appreciated.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try changing your right outer join to left outer join. Looks like you just have your tables on wrong sides.

  3. #3
    Join Date
    Jul 2013
    Posts
    9
    I've continually tried this method to no avail!

    I think this part of the statement 'userProfilesTbl RIGHT OUTER JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId' only shows me the users that are selected for the training i need a way to show all the users in the shop and on the shift that do the training! need a way to get around this?

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Not enough information here..

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Putting “tbl” in a table name is called “tibbling” and we do not do it (Google Phil Factor's editorial on it). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Now we have to start typing and guessing for you.

    >> I have 4 tables: User_ProfilesTbl, trainingTbl, User_AssessmentTbl and setsTbl User_ProfilesTbl and User_AssessmentsTbl are related through the user_id fkey which is a unique identity field [sic] type. <<

    That is also wrong. Columns are not fields-- totally different concepts! IDENTITY is not an identifier; it is a count of the physical insertion attempts in one copy of SQL Server to one table, on one machine. Why do you think that is part of a logical data model? How did you plan to validate or verify it?

    You might want to Google why we do not use camelCase. It does not work; your eye is trained to jump to an uppercase letter if you are a Latin Alphabet user.

    CREATE TABLE User_Profiles
    (user_id CHAR(10) NOT NULL PRIMARY KEY,
    ..);

    CREATE TABLE Training_Something,
    (user_id CHAR(10) NOT NULL
    REFERENCES User_Profiles,
    training_date DATE NOT NULL,
    training_group_id CHAR(10) NOT NULL,
    ..);

    User_Assessments
    (user_id CHAR(10) NOT NULL
    REFERENCES User_Profiles,
    ..);

    CREATE TABLE Training_Sets
    (training_set_name VARCHAR(20) NOT NULL PRIMARY KEY,
    /* user_id CHAR(10) NOT NULL --- this makes no sense.
    REFERENCES User_Profiles, */
    .. );


    >> training and User_Assessment are related through the tt_id foreign key INTEGER field [sic] type. Training_Sets and Something_Training are related through the s_id foreign key INTEGER field [sic] type. <<

    Which is the referenced and referencing table in those pairs? This is not like the double-linked lists you can have in a network database.

    >> The Training_Sets is populated by a list of the different types of training. <<

    Then why does it have a user_id?? Read your narrative.

    I want to show all training sets and users regardless if they’ve done the training or not.

    >> The SELECT statement shown displays all of the training sets (training_set_name from Training_Sets) along the Y axis. <<

    What is a “Y-axis”? What is a “Gridview”? Those things do not exist in SQL. In the database tier of a correct architecture, we do no display formatting at all. This is the fundamental principle of C/S.

    You use more RIGHT OUTER JOINs than I usually see. Westerners read left to right, so the LEFT OUTER JOINs is more natural to read and use. We also seldom use the FULL OUTER JOIN in a correctly designed schema. These are not wrong, but they have a bad code smell.

    There is a trick for recording a month. Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

    CREATE TABLE Monthly_Report_Periods
    (monthly_report_name CHAR(10) NOT NULL PRIMARY KEY
    CHECK (monthly_report_name
    LIKE '[12][0-9][0-9][0-9]-[01][0-9]-00'),
    monthly_report_start_date DATE NOT NULL,
    monthly_report_end_date DATE NOT NULL,
    CONSTRAINT date_ordering
    CHECK (monthly_report_start_date <= monthly_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

    If you want to give us DDL, we can correct this design. We do not have anything we can use to program.

  5. #5
    Join Date
    Jul 2013
    Posts
    9
    I was told that this was a good forum for helping people with their problems in relation to database issues.
    I'm going to ignore the condescending and very unhelpful post from Celko and request if anybody who has not got to much time on their hands can assist.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I was wrong in agreeing with n_i on the DB2 board. This is now the most hilarious thread on the site.

    Do you realize who Joe is? If you, actually read what he wrote above or some of his books, you'll go a long way in this business, otherwise, some of us will be able to get hefty contracts to help out with your company's performance/error issues. Also, his post was if taken as it was written does address your problem(s)

    My point yesterday
    Code:
    SELECT * FROM userProfilesTbl RIGHT OUTER JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId
    should be written as
    Code:
    SELECT * FROM userProfilesTbl LEFT OUTER JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId
    That will give you all in the users in the profile table whether they have an assessment or not.

    Couple of other points, should always use a correlation name when joining tables and explicitly name each column with it and explicitly name what columns you want rather than *. Helps with others understanding of the query and future maintenance.
    Dave

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Feed my ego!

    I was wrong in agreeing with n_i on the DB2 board. This is now the most hilarious thread on the site.
    Do you realize who Joe is?
    Some mornings I forget myself. Beer for breakfast at my age is not like beer for breakfast in college any more.

    If you, actually read what he wrote above or some of his books, you'll go a long way in this business, otherwise, some of us will be able to get hefty contracts to help out with your company's performance/error issues. Also, his post was if taken as it was written does address your problem(s)
    I gave him about $1K+ worth of consulting, if he had taken it, done some leg work and asked for a follow-up, so I want that “hefty contract” when his company's performance/error issues crop up. I have a mortgage!

    Seriously, after ten books, 1200+ article and 125+ gigs and 30+ years I have a set of SQL symptoms (code smells, to steal a term from other languages). The big one is the data element names! It show the mindset of the programmer and often his first programming language immediately. For example, that “tbl-” is a strong indicator of design flaws (Phil Factor had a great column on this); it shows a non-RDBMS mindset, locked in a FORTRAN II physical model of data.

    Are you a FUTURAMA fan? This is like Dr. Zoidberg saying he knows human medicine, then asking Frye to lift his gills so he can check for parasites.

    The LEFT OUTER JOIN vs RIGHT OUTER JOIN is sometimes a sign of an Arabic programmer. They also used to write loops with a “FOR n DOWN TO 1 DO..” syntax option in some languages. Buy me a beer and I will tell you the full story on how we put that into the ANSI Standards.

    Couple of other points, should always use a correlation name when joining tables and explicitly name each column with it and explicitly name what columns you want rather than *. Helps with others understanding of the query and future maintenance.
    AMEN! You forgot the deadly re-compilation problems of SELECT * queries after the base tables are altered. The only place I use this is with “[NOT] EXISTS SELECT * FROM .. WHERE..” because it expresses the concept of a row in a table-level construct. I wish we had used “ROW_NUMBER(*)”, etc. in the newer constructs.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm Ok with using the dreaded "SELECT *" when doing "database sniffing" on a database that you've just inherited and have a dozen people screaming that "our code broke" without any more details... Sometimes you gotta grab what you can get to put the wheels back on the bus while it is rolling down the road, then prettify things after triage.

    While you (Joe) can be an arrogant, obnoxioius, and sometimes even pendantic pain in the posterior. If we could refine your social skills so that they were even one percent as good as your SQL / Relational Algebra skills you'd be a happier camper, the folks with questions would be happier, and I wouldn't take nearly as much heat for defending you in spite of your presentation... All of which I'd count as "wins" by any measure!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jul 2013
    Posts
    9
    Thanks Pat Phelan for explaining to Joe how he can be construed as supercilious!
    Not after £1ks worth of consultation and never requested your CV just asked for a very basic answer to my question!

    dav1mo your answer still only offers me the same output as I had previously!

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you could post the CREATE TABLE statements for your tables, and INSERT statements to provide about five rows of sample data, then we could be a lot more helpful. The problem is that while you understand your problem, your tables, and your data clearly enough to work on this problem, so far we don't... We need a better understanding to be able to help you.

    Something like:
    Code:
    CREATE TABLE #employee (
       id       INT
    ,  descr    VARCHAR(23)
       PRIMARY KEY (id)
    )
    
    CREATE TABLE #class (
       id       INT
    ,  employee INT
    ,  descr    VARCHAR(32)
       )
    
    INSERT INTO #employee (id, descr)
       VALUES (0, 'zero'), (1, 'one'), (2, 'two')
    
    INSERT INTO #class (id, employee, descr)
       VALUES (1, 1, 'ein')
    ,    (2, 2, 'zwei'), (3, 2, 'deux')
    ,    (4, 3, 'wtf')
    
    SELECT e.descr AS employee_name, c.descr AS class_description
       FROM #employee AS e
       LEFT JOIN #class AS c
          ON (c.employee = e.id)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    "family curse code" and posters

    I'm Ok with using the dreaded "SELECT *" when doing "database sniffing" on a database that you've just inherited and have a dozen people screaming that "our code broke" without any more details... Sometimes you gotta grab what you can get to put the wheels back on the bus while it is rolling down the road, then prettify things after triage.
    LOL! I prefer to say "family curse code" as opposed to "legacy code", but "triage" instead of "maintenance" programming is better

    While you (Joe) can be an arrogant, obnoxious, and sometimes even pedantic pain in the posterior. If we could refine your social skills so that they were even one percent as good as your SQL / Relational Algebra skills you'd be a happier camper, the folks with questions would be happier, and I wouldn't take nearly as much heat for defending you in spite of your presentation... All of which I'd count as "wins" by any measure!
    I am pedantic but that is expected when someone asks an ANSI/ISO Standards question. Does a Rabbi say "It's in the Torah somewhere" when you ask him a religious question? Nah! You get an exact answer.

    Seriously, I tired being overly diplomatic decades ago. It did not work. The posters would take that as approval of whatever disaster they had coded. They would grab a kludge or expect me to post a full answer for them. This was a pain when I would get homework assignments from India or Russia in emails that began with "I have a stolen copy of your book, so you must do this classroom/my job for me..." I am not kidding, it was that bad.

    They would do no research and re-post the same basic problems over and over. They learned nothing.

    But when I would go into the details of what they were doing wrong and why they are doing it, I got a lot of crybabies. They want the kludges, not education. Their sense of entitlement is greater than the desire to be a professional. This is a problem with k-12 students today, too.

    I have a tee shirt that reads "Oh no! I misplaced my social skills again!" I got as a gift
    Last edited by Pat Phelan; 08-02-13 at 16:25. Reason: Fixed vbcode typo

  12. #12
    Join Date
    Jul 2013
    Posts
    9
    Hi Pat,

    Sorry for the delay! Here's the CREATE TABLE statements for my tables, and INSERT statements to provide sample data as requested. Hope this is okay!

    CREATE TABLE [dbo].[setsTbl](
    [s_id] [int] IDENTITY(1,1) NOT NULL,
    [setName] [varchar](250) NULL,
    [instruction] [varchar](max) NULL,
    [link] [varchar](max) NULL,
    [startDate] [datetime] NULL,
    [endDate] [datetime] NULL,
    [setNo] [varchar](50) NULL,
    [sub_id] [int] NULL,
    CONSTRAINT [PK_setsTbl] PRIMARY KEY CLUSTERED
    (
    [s_id] ASC
    )
    )
    SET IDENTITY_INSERT [dbo].[setsTbl] ON
    INSERT [dbo].[setsTbl] ([s_id], [setName], [instruction], [link], [startDate], [endDate], [setNo], [sub_id]) VALUES (1, N'SPA - Ladder 1', NULL, N'MFRS_SPA_Ladder_1.pdf', NULL, NULL, N'1', NULL)
    INSERT [dbo].[setsTbl] ([s_id], [setName], [instruction], [link], [startDate], [endDate], [setNo], [sub_id]) VALUES (2, N'SPA - Service 1', NULL, N'MFRS_SPA_SERVICE_1.pdf', NULL, NULL, N'2', NULL)
    INSERT [dbo].[setsTbl] ([s_id], [setName], [instruction], [link], [startDate], [endDate], [setNo], [sub_id]) VALUES (3, N'SPA - Service 2', NULL, N'MFRS_SPA_SERVICE_2.pdf', NULL, NULL, N'3', NULL)
    INSERT [dbo].[setsTbl] ([s_id], [setName], [instruction], [link], [startDate], [endDate], [setNo], [sub_id]) VALUES (4, N'SPA - Service 3', NULL, N'MFRS_SPA_SERVICE_3.pdf', NULL, NULL, N'4', NULL)
    INSERT [dbo].[setsTbl] ([s_id], [setName], [instruction], [link], [startDate], [endDate], [setNo], [sub_id]) VALUES (5, N'SPA - Service 4', NULL, N'MFRS_SPA_SERVICE_4.pdf', NULL, NULL, N'5', NULL)
    INSERT [dbo].[setsTbl] ([s_id], [setName], [instruction], [link], [startDate], [endDate], [setNo], [sub_id]) VALUES (6, N'SPA - Service 5', NULL,
    SET IDENTITY_INSERT [dbo].[setsTbl] OFF

    CREATE TABLE [dbo].[trainingTbl](
    [tt_id] [int] IDENTITY(1,1) NOT NULL,
    [t_date] [datetime] NULL,
    [s_id] [int] NULL,
    [EditorId] [varchar](50) NULL,
    [st_id] [int] NULL,
    [wa_id] [int] NULL,
    CONSTRAINT [PK_trainingTbl] PRIMARY KEY CLUSTERED
    (
    [tt_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[trainingTbl] ON
    INSERT [dbo].[trainingTbl] ([tt_id], [t_date], [s_id], [EditorId], [st_id], [wa_id]) VALUES (237, CAST(0x0000A18100000000 AS DateTime), 22, N'Monroe', 2, 1)
    INSERT [dbo].[trainingTbl] ([tt_id], [t_date], [s_id], [EditorId], [st_id], [wa_id]) VALUES (238, CAST(0x0000A1A300000000 AS DateTime), 1, N'Monroe', 2, 1)
    INSERT [dbo].[trainingTbl] ([tt_id], [t_date], [s_id], [EditorId], [st_id], [wa_id]) VALUES (241, CAST(0x0000A1A800000000 AS DateTime), 1, N'Monroe', 2, 1)
    INSERT [dbo].[trainingTbl] ([tt_id], [t_date], [s_id], [EditorId], [st_id], [wa_id]) VALUES (242, CAST(0x0000A1A400000000 AS DateTime), 1, N'Monroe', 2, 1)
    INSERT [dbo].[trainingTbl] ([tt_id], [t_date], [s_id], [EditorId], [st_id], [wa_id]) VALUES (243, CAST(0x0000A1AA00000000 AS DateTime), 7, N'Monroe', 2, 1)

    SET IDENTITY_INSERT [dbo].[trainingTbl] OFF

    CREATE TABLE [dbo].[userProfilesTbl](
    [UserId] [uniqueidentifier] NOT NULL,
    [serviceNo] [varchar](50) NULL,
    [forename] [varchar](100) NULL,
    [surname] [varchar](100) NULL,
    [ss_id] [int] NULL,
    [st_id] [int] NULL,
    [wa_id] [int] NULL,
    CONSTRAINT [PK_userProfilesTbl] PRIMARY KEY CLUSTERED
    (
    [UserId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[userProfilesTbl] ([UserId], [serviceNo], [forename], [surname], [ss_id], [st_id], [wa_id]) VALUES (N'42129d2e-202a-4a71-91c9-00657bdd8290', N'5083', N'Sean ', N'Ohara', 1, 22, 3)
    INSERT [dbo].[userProfilesTbl] ([UserId], [serviceNo], [forename], [surname], [ss_id], [st_id], [wa_id]) VALUES (N'a018a4af-f6a6-405f-8085-09cacd1d204e', N'1997', N'Tony', N'Blair', 1, 1, 2)
    INSERT [dbo].[userProfilesTbl] ([UserId], [serviceNo], [forename], [surname], [ss_id], [st_id], [wa_id]) VALUES (N'c79f242a-9f7f-469d-8c3c-0d5a2142e84e', N'2969', N'Glenn', N'Smith', 1, 22, 2)
    INSERT [dbo].[userProfilesTbl] ([UserId], [serviceNo], [forename], [surname], [ss_id], [st_id], [wa_id]) VALUES (N'fcab0f66-6fc0-44bf-92a7-10204bc4e4c8', N'5310', N'Debbie', N'Walker', 1, 22, 2)
    INSERT [dbo].[userProfilesTbl] ([UserId], [serviceNo], [forename], [surname], [ss_id], [st_id], [wa_id]) VALUES (N'b3939a02-2c93-4c47-828e-118b73a90a7a', N'1997', N'Jimmy', N'Carter', 1, 2, 1)

    CREATE TABLE [dbo].[userAssessmentTbl](
    [tu_id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [uniqueidentifier] NULL,
    [tt_id] [int] NULL,
    [o_id] [int] NULL,
    [n_id] [int] NULL,
    [notes] [varchar](250) NULL,
    CONSTRAINT [PK_userAssessment] PRIMARY KEY CLUSTERED
    (
    [tu_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[userAssessmentTbl] ON
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1016, N'b3939a02-2c93-4c47-828e-118b73a90a7a', 237, 2, NULL, NULL)
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1017, N'1ebfe8b5-8702-4d92-9aae-bfeb9797c1c5', 237, 2, NULL, NULL)
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1018, N'4dfe14c0-c0e3-4fbe-954f-bdd5c53834a8', 237, 2, NULL, NULL)
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1019, N'083939d9-db44-48a3-9e64-62533b5a8243', 237, 2, NULL, NULL)
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1020, N'839d451a-ccde-4ef1-b97d-987a64bf1a25', 237, 2, NULL, N' This is a test Hello World! ')

    GO
    print 'Processed 100 total records'
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1128, N'1ebfe8b5-8702-4d92-9aae-bfeb9797c1c5', 258, 1, NULL, N' ')
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1129, N'4dfe14c0-c0e3-4fbe-954f-bdd5c53834a8', 258, 3, NULL, N' ')
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1130, N'083939d9-db44-48a3-9e64-62533b5a8243', 258, 2, NULL, N' ')
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1131, N'61df35ff-2b76-44cc-9a05-c884c80a5215', 258, 3, NULL, N' ')
    INSERT [dbo].[userAssessmentTbl] ([tu_id], [UserId], [tt_id], [o_id], [n_id], [notes]) VALUES (1132, N'839d451a-ccde-4ef1-b97d-987a64bf1a25', 258, 1, NULL, N'
    SET IDENTITY_INSERT [dbo].[userAssessmentTbl] OFF

  13. #13
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Celko View Post
    For example, that “tbl-” is a strong indicator of design flaws (Phil Factor had a great column on this); it shows a non-RDBMS mindset, locked in a FORTRAN II physical model of data.
    I think that infamous "tbl" prefix comes from some coding guidelines that seem to stem from the Microsoft SQL Server world. I have never seen the "tbl" prefix in Oracle or Postgres models.

    Quote Originally Posted by TheBo
    I'm going to ignore the condescending and very unhelpful post from Celko and request if anybody who has not got to much time on their hands can assist.
    You could at least have the courtesy to format your SQL code so that it is actually readable. Compare your last post with Pat's nicely formatted answer.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  14. #14
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    It is older than that ..

    I think that infamous "tbl" prefix comes from some coding guidelines that seem to stem from the Microsoft SQL Server world. I have never seen the "tbl" prefix in Oracle or Postgres models.
    It is actually older than that. The first compilers did not have good symbol tables, so they had to give hints in the names. In particular, FORTRAN I and II only had Integer and Floating Point data types. A variable was declared by using it in the code. The names had to be 6 or fewer letters and if they began with 'I' thru 'N', they were integers! Later BASIC used $ to signal a string variable.

    Many early tape and disk operating systems required that physical device names begin or end with certain affixes (MT<digit><digit>: for a magnetic tape drive was common). You had to tell the OS how the data was being stored in hardware.

    It took awhile to break away from the 1950-60 mindset of physical EDP to the modern abstract IT mindset. In the case of SQL, since all we have are tables (base, derived, virtual, temp, etc), this is really redundant. It is like putting "_noun" at the end of every noun in a novel!

    But I agree, I have not see this done much outside of SQL Server. I guess people who started with BASIC have a hard time with abstract concepts.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I couldn't figure out what was wrong at first... The code "broke" my pretty-fier. Then I figured out that the DDL/DML to build the tables and sample data was broken!

    I kind of hacked the output of my pretty-fier to make the code syntax complete, then ran it successfully. After that, I went back to your original specifications and realized that the answer is actually trivial!
    Quote Originally Posted by TheBo View Post
    I want to show all training sets and users regardless if they’ve done the training or not.
    The solution for this problem as stated is the last SELECT statement in:
    Code:
    CREATE TABLE [dbo].[setsTbl] (
       [s_id]         [int] IDENTITY(1,1)  NOT NULL
    ,  [setName]      [varchar](250)           NULL
    ,  [instruction]  [varchar](max)           NULL
    ,  [link]         [varchar](max)           NULL
    ,  [startDate]    [datetime]               NULL
    ,  [endDate]      [datetime]               NULL
    ,  [setNo]        [varchar](50)            NULL
    ,  [sub_id]       [int]                    NULL
       CONSTRAINT [PK_setsTbl]
          PRIMARY KEY CLUSTERED 
             ( [s_id] ASC )
    ) 
    
    SET IDENTITY_INSERT [dbo].[setsTbl] ON
    INSERT [dbo].[setsTbl] 
    (  [s_id], [setName], [instruction]
    ,  [link], [startDate], [endDate]
    ,  [setNo], [sub_id]
    )  VALUES
          (1, N'SPA - Ladder 1',  NULL, N'MFRS_SPA_Ladder_1.pdf',  NULL, NULL, N'1', NULL)
     ,    (2, N'SPA - Service 1', NULL, N'MFRS_SPA_SERVICE_1.pdf', NULL, NULL, N'2', NULL)
     ,    (3, N'SPA - Service 2', NULL, N'MFRS_SPA_SERVICE_2.pdf', NULL, NULL, N'3', NULL)
     ,    (4, N'SPA - Service 3', NULL, N'MFRS_SPA_SERVICE_3.pdf', NULL, NULL, N'4', NULL)
     ,    (5, N'SPA - Service 4', NULL, N'MFRS_SPA_SERVICE_4.pdf', NULL, NULL, N'5', NULL)
     ,    (6, N'SPA - Service 5', NULL, N'MFRS_SPA_SERVICE_5.pdf', NULL, NULL, N'6', NULL)
     SET IDENTITY_INSERT [dbo].[setsTbl] OFF
    
    CREATE TABLE [dbo].[trainingTbl] (
       [tt_id]        [int] IDENTITY(1,1)  NOT NULL
    ,  [t_date]       [datetime]               NULL
    ,  [s_id]         [int]                    NULL
    ,  [EditorId]     [varchar](50)            NULL
    ,  [st_id]        [int]                    NULL
    ,  [wa_id]        [int]                    NULL
       CONSTRAINT [PK_trainingTbl]
          PRIMARY KEY CLUSTERED 
             ( [tt_id] ASC )
    		 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    ,		    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    ,           ALLOW_PAGE_LOCKS = ON)
             ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[trainingTbl] ON
    INSERT [dbo].[trainingTbl] 
    (  [tt_id], [t_date], [s_id]
    ,  [EditorId], [st_id], [wa_id]
    )  VALUES
          (237, CAST(0x0000A18100000000 AS DateTime), 22, N'Monroe', 2, 1)
    ,     (238, CAST(0x0000A1A300000000 AS DateTime), 1, N'Monroe', 2, 1)
    ,     (241, CAST(0x0000A1A800000000 AS DateTime), 1, N'Monroe', 2, 1)
    ,     (242, CAST(0x0000A1A400000000 AS DateTime), 1, N'Monroe', 2, 1)
    ,     (243, CAST(0x0000A1AA00000000 AS DateTime), 7, N'Monroe', 2, 1)
    
    SET IDENTITY_INSERT [dbo].[trainingTbl] OFF
    
    CREATE TABLE [dbo].[userProfilesTbl] (
       [UserId]       [uniqueidentifier]   NOT NULL
    ,  [serviceNo]    [varchar](50)            NULL
    ,  [forename]     [varchar](100)           NULL
    ,  [surname]      [varchar](100)           NULL
    ,  [ss_id]        [int]                    NULL
    ,  [st_id]        [int]                    NULL
    ,  [wa_id]        [int]                    NULL
       CONSTRAINT [PK_userProfilesTbl]
          PRIMARY KEY CLUSTERED
             ( [UserId] ASC )
    	     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    ,           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    ,           ALLOW_PAGE_LOCKS = ON)
             ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[userProfilesTbl] 
    (  [UserId], [serviceNo], [forename]
    ,  [surname], [ss_id], [st_id], [wa_id]
    )  VALUES
          (N'42129d2e-202a-4a71-91c9-00657bdd8290', N'5083', N'Sean ', N'Ohara', 1, 22, 3)
    ,     (N'a018a4af-f6a6-405f-8085-09cacd1d204e', N'1997', N'Tony', N'Blair', 1, 1, 2)
    ,     (N'c79f242a-9f7f-469d-8c3c-0d5a2142e84e', N'2969', N'Glenn', N'Smith', 1, 22, 2)
    ,     (N'fcab0f66-6fc0-44bf-92a7-10204bc4e4c8', N'5310', N'Debbie', N'Walker', 1, 22, 2)
    ,     (N'b3939a02-2c93-4c47-828e-118b73a90a7a', N'1997', N'Jimmy', N'Carter', 1, 2, 1)
    
    CREATE TABLE [dbo].[userAssessmentTbl](
       [tu_id]        [int] IDENTITY(1,1)  NOT NULL
    ,  [UserId]       [uniqueidentifier]       NULL
    ,  [tt_id]        [int]                    NULL
    ,  [o_id]         [int]                    NULL
    ,  [n_id]         [int]                    NULL
    ,  [notes]        [varchar](250)           NULL
       CONSTRAINT [PK_userAssessment]
          PRIMARY KEY CLUSTERED  ( [tu_id] ASC )
    	  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    ,	     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    ,        ALLOW_PAGE_LOCKS = ON)
          ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[userAssessmentTbl] ON
    INSERT [dbo].[userAssessmentTbl]
    (  [tu_id], [UserId], [tt_id]
    ,  [o_id], [n_id], [notes]
    )  VALUES
          (1016, N'b3939a02-2c93-4c47-828e-118b73a90a7a', 237, 2, NULL, NULL)
    ,     (1017, N'1ebfe8b5-8702-4d92-9aae-bfeb9797c1c5', 237, 2, NULL, NULL)
    ,     (1018, N'4dfe14c0-c0e3-4fbe-954f-bdd5c53834a8', 237, 2, NULL, NULL)
    ,     (1019, N'083939d9-db44-48a3-9e64-62533b5a8243', 237, 2, NULL, NULL)
    ,     (1020, N'839d451a-ccde-4ef1-b97d-987a64bf1a25', 237, 2, NULL, N' This is a test Hello World! ')
    ,     (1128, N'1ebfe8b5-8702-4d92-9aae-bfeb9797c1c5', 258, 1, NULL, N' ')
    ,     (1129, N'4dfe14c0-c0e3-4fbe-954f-bdd5c53834a8', 258, 3, NULL, N' ')
    ,     (1130, N'083939d9-db44-48a3-9e64-62533b5a8243', 258, 2, NULL, N' ')
    ,     (1131, N'61df35ff-2b76-44cc-9a05-c884c80a5215', 258, 3, NULL, N' ')
    ,     (1132, N'839d451a-ccde-4ef1-b97d-987a64bf1a25', 258, 1, NULL, N' ')
    SET IDENTITY_INSERT [dbo].[userAssessmentTbl] OFF
    GO
    --  The actual solution to the problem is:
    
    SELECT s.setName, u.forename, u.surname
       FROM [dbo].[setsTbl] AS s
       CROSS JOIN [dbo].[userProfilesTbl] AS u
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •