Selecting multiple columns from several tables without using JOIN
-
I'm currently building a web-based store for my company that allows students register for different classes. In the administration section of the website there is page where a user can create a new class for students to register for. On this page there is multiple items that the person must fill out; date and time of class, the instructor, the course name, etc. Some of these items are stored in a database. For example, there is a table that has all the instructor's names and ID numbers, and there is another table with all the course names, price, etc. My problem is that I want to populate several drop-down menus with information from different tables that have no relationship. For example I want to pull all the instructor's names (to be placed in one drop-down) and also select all the different course names (to be placed in a different drop-down). Now I could use multiple SQL statements to do this, but I was hoping there was a way to do it with a single SQL statement, however it cannot use JOINS. This is because, in the above example, the instructors table and the course table do not have any columns that are the same that the JOIN can be done on. I wanted to use a single SQL statement since this is web-based and the less calls to the database the quicker the website will load. Any ideas or suggestions would be greatly appreciated, and thanks in advance for any help.
Using
UNION ALLas suggested by some folks would not be an optimal solution, because the time taken to access the tables would remain the same, you would just end up saving the time to open and close additional connections (which ASP.NET saves you anyway through connection pooling). Since the Instructors and Courses do not change often, you can use theSystem.Web.Caching.Cacheclass to load the data from the database in the event and bind the controls from theCache. There are mechanisms in SQL Server to hook on to Table change notifications and invalidate the Cache. So anytime the data in the base tables change, your Cache gets invalidated and the fresh data is loaded. Here's an example on how to use theCacheandSqlCacheDependencyclasses: http://davidhayden.com/blog/dave/archive/2006/04/29/2929.aspx[^] -
I'm currently building a web-based store for my company that allows students register for different classes. In the administration section of the website there is page where a user can create a new class for students to register for. On this page there is multiple items that the person must fill out; date and time of class, the instructor, the course name, etc. Some of these items are stored in a database. For example, there is a table that has all the instructor's names and ID numbers, and there is another table with all the course names, price, etc. My problem is that I want to populate several drop-down menus with information from different tables that have no relationship. For example I want to pull all the instructor's names (to be placed in one drop-down) and also select all the different course names (to be placed in a different drop-down). Now I could use multiple SQL statements to do this, but I was hoping there was a way to do it with a single SQL statement, however it cannot use JOINS. This is because, in the above example, the instructors table and the course table do not have any columns that are the same that the JOIN can be done on. I wanted to use a single SQL statement since this is web-based and the less calls to the database the quicker the website will load. Any ideas or suggestions would be greatly appreciated, and thanks in advance for any help.
I'm not sure I understand what you want to do. You have several different drop-downs which hold unrelated data from different tables in the database. You want to populate all these different drop-downs from a single result set produced from a single database query. How is that going to work?
-
I'm currently building a web-based store for my company that allows students register for different classes. In the administration section of the website there is page where a user can create a new class for students to register for. On this page there is multiple items that the person must fill out; date and time of class, the instructor, the course name, etc. Some of these items are stored in a database. For example, there is a table that has all the instructor's names and ID numbers, and there is another table with all the course names, price, etc. My problem is that I want to populate several drop-down menus with information from different tables that have no relationship. For example I want to pull all the instructor's names (to be placed in one drop-down) and also select all the different course names (to be placed in a different drop-down). Now I could use multiple SQL statements to do this, but I was hoping there was a way to do it with a single SQL statement, however it cannot use JOINS. This is because, in the above example, the instructors table and the course table do not have any columns that are the same that the JOIN can be done on. I wanted to use a single SQL statement since this is web-based and the less calls to the database the quicker the website will load. Any ideas or suggestions would be greatly appreciated, and thanks in advance for any help.
I think you need to go back and look at your datadase design. You say there is no connection between Instructors and Courses but I think, in reality, there is a many-to-many relationship. You can achieve the connection with a linked (or intermediate) table which only contains the two primary keys - one from each table. Then create foreign keys on this table to the Instructors and Classes tables. The Primary Key of the linking table needs to be the composite of the two elements InstructorID and CourseID - see below.
Instructor Table: InstructorID, FirstName, LastName,... Course Table: CourseID, Subject, DateStart, DateEnd,.... **InstructorCourse Table: InstructorID, CourseID //This is a new table with just two fields**I presume you don't want Lecturer A being able to teach Geography when really his/her subject is History, but Lecturer B can teach Mathematics and Statistics - no problem.It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
-
I think you need to go back and look at your datadase design. You say there is no connection between Instructors and Courses but I think, in reality, there is a many-to-many relationship. You can achieve the connection with a linked (or intermediate) table which only contains the two primary keys - one from each table. Then create foreign keys on this table to the Instructors and Classes tables. The Primary Key of the linking table needs to be the composite of the two elements InstructorID and CourseID - see below.
Instructor Table: InstructorID, FirstName, LastName,... Course Table: CourseID, Subject, DateStart, DateEnd,.... **InstructorCourse Table: InstructorID, CourseID //This is a new table with just two fields**I presume you don't want Lecturer A being able to teach Geography when really his/her subject is History, but Lecturer B can teach Mathematics and Statistics - no problem.It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
-
AnnieMacD wrote:
which only contains the two primary keys
Two primary keys? I presume you meant to say "a two column primary key".
No, she's correct. This isn't the primary key for the middle table but the elements from the two other tables that are primary keys in those tables. Like so: Table A PKA Instructor Table B PKB Class Table Middle PKMiddle PKA PKB The primary key for the middle table is neither PKA nor PKB in that case.
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...
-
I'm currently building a web-based store for my company that allows students register for different classes. In the administration section of the website there is page where a user can create a new class for students to register for. On this page there is multiple items that the person must fill out; date and time of class, the instructor, the course name, etc. Some of these items are stored in a database. For example, there is a table that has all the instructor's names and ID numbers, and there is another table with all the course names, price, etc. My problem is that I want to populate several drop-down menus with information from different tables that have no relationship. For example I want to pull all the instructor's names (to be placed in one drop-down) and also select all the different course names (to be placed in a different drop-down). Now I could use multiple SQL statements to do this, but I was hoping there was a way to do it with a single SQL statement, however it cannot use JOINS. This is because, in the above example, the instructors table and the course table do not have any columns that are the same that the JOIN can be done on. I wanted to use a single SQL statement since this is web-based and the less calls to the database the quicker the website will load. Any ideas or suggestions would be greatly appreciated, and thanks in advance for any help.
I think several good points have been made in this thread. You could, if you are bent on doing this, use the row number function and join based on that. select * from ( select name ,row_number() over (order by name) rowa from instructor ) tableA full outer join ( select name Classname ,row_number() over (order by name ) rowb from class ) tableb on rowa=rowb This should give you a listing of columns from table a, and table b as if they'd been put together into a spreadsheet. Then you just have to be sure you don't have a duplicate column name (as I showed in the second subquery).
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...
-
No, she's correct. This isn't the primary key for the middle table but the elements from the two other tables that are primary keys in those tables. Like so: Table A PKA Instructor Table B PKB Class Table Middle PKMiddle PKA PKB The primary key for the middle table is neither PKA nor PKB in that case.
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...
-
AnnieMacD wrote:
which only contains the two primary keys
Two primary keys? I presume you meant to say "a two column primary key".
The Primary Key for the linking table is a composite of InstructorID + CourseID (in my example). This then makes it unique which a Primary Key has to be. There is NO other data in the linking table. This then effectively creates a many-to-many relationship. Here are the commands for creating the linking table assuming you have a Course table with Primary Key CourseID and an Instructor table with Primary Key InstructorID.
CREATE TABLE [dbo].[CourseInstructor](
[CourseID] [int] NOT NULL,
[InstructorID] [int] NOT NULL,
CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[InstructorID] 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
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GOALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course]
GOALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Instructor] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Instructor] ([InstructorID])
GOALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Instructor]
GOIt’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
-
Then, it's no longer called Primary Key, we call it a Foreign key. A table can have one and only one Primary key.
-
Then, it's no longer called Primary Key, we call it a Foreign key. A table can have one and only one Primary key.
Yes, and we all know that. She was referring to them in explanatory fashion. There was nothing wrong with her explanation.
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...
-
Yes, and we all know that. She was referring to them in explanatory fashion. There was nothing wrong with her explanation.
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...
-
But the Primary Key can be a composite.
It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
-
AnnieMacD wrote:
But the Primary Key can be a composite.
Yes, of course. I know what you were talking about. But a beginner who happens to see this thread might think that a table can have more than one Primary key.
-
smcnulty2000 wrote:
Yes, and we all know that.
I agree, but a beginner who happens to view this thread might get confused and misled.
Fair enough. I took your original use of the term as condescending. Perhaps it wasn't meant that way, but, oh well.
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...