How to add dummy values to a table in SQL

Probably not the most descriptive title but sometimes it's hard to put stupid SQL problems(the kind that make you want to put booze in your coffee to get through the day) into english. I basically have the following two tables:

TABLE1

studentid   firstname   lastname    counter cmid    score   lessond
328000            Jacob           GG             5  189 90  103950
328111            Jacob           GG            6   189 80  103951
3283333           Tyler           GR            5   189 70  103950
3244444            William    FL               5    189 70  103950
3280000            Sean           DA           5    189 80  103950
3283333            Sean           DA             6  189 90  103951

TABLE 2

Lesson2ClassroomAssignmentId    Counter
103956                      1
103947                           2
103948                          3
103949                          4
103950                          5
103951                          6

The first table is basically a list of students and their scores for certain lessons(numbered 1-6 and denoted by counter). The second table is a list of all possible lessons(numbered 1-6 and denoted by counter).

What I need to do is get a table that has a row for each student and each lesson, regardless of whether or not the student has a row in table 1 for that lesson or not.

Ive tried doing something like selecting * from table 1 and UNIONING it with the results of the 2nd table LEFT OUTER JOINED with the first table where counter <> counter but havent been able to get the results I desired.

If anyone has any suggestions(dont need the full answer, just pointed in the right direction) I'd owe ya one!! Alternatively, feel free to make fun of me if this post is unintelligable as it barely makes sense to me and I'm the one who has been staring at it for the past 6 hours ;)

Ciao Ciao,

Tim

DESIRED OUTPUT:

The Desired output would be:

TABLE FINAL

 studentid     firstname   lastname    counter cmid    score   lessond
    3280000         Jacob       GG      1 NULL, NULL, NULL
    3280000         Jacob       GG 2 NULL, NULL, NULL
    3280000         Jacob       GG 3 NULL, NULL, NULL
    3280000         Jacob       GG 4 NULL, NULL, NULL
    3280000         Jacob       GG 5 189, 90, 103950
    3280000         Jacob       GG 6 189, 80, 103951
    3283333         Tyler       GR 1, NULL, NULL, NULL
    3283333         Tyler       GR 2, NULL, NULL, NULL
    3283333         Tyler       GR 3, NULL, NULL, NULL
    3283333         Tyler       GR 4, NULL, NULL, NULL
    3283333         Tyler       GR 5, 189, 70, 103950
    3283333         Tyler       GR 6, NULL, NULL, NULL

So basically taking any r开发者_StackOverflow中文版ow with actual lesson data for a student from table one and supplementing it with dummy rows with no score data for each lesson(from table 2) that the student didnt have a score for.

---

Try using Cross Join

e.g: I have added a pseudo column IsMatch to identify if the current row is a matched row or not just in case....

SELECT  a.*, 
        b.*,
        a.counter-b.counter AS IsMatch
  FROM TABLE1 a  CROSS JOIN TABLE2 b
ORDER BY studentid

---

looks like you would benefit from a Student table and a Lesson table. then you could do a unjoined select from them both to get the cross product of all rows.

---

If you find your time to be valuable just purchase one of the commercial products designed to provide this type of functionality.

I'm very happy with EMS Data Generator for SQL Server which is priced under $100...

EMS Data Generator for SQL Server is an impressive tool for generating test data to SQL Server database tables with the possibility to save and edit scripts. The utility can help you to simulate the database production environment and allows you to populate several SQL Server database tables with test data simultaneously, define tables and fields for generating data, set value ranges, generate SQL Server char fields by mask, define lists of values manually or select them from SQL queries, set generation parameters for each field type and has many other features to generate SQL Server test data in a simple and direct way. Data Generator for SQL Server also provides a console application, which allows you to generate SQL Server test data in one-touch by using generation templates.