Support Forums - View Thread

View Thread

The following is the text of the current message along with any replies.

Messages 1 to 3 of 3 total

Joining a table on a constant, not a field

Wed, Feb 26 2014 11:38 PM	Permanent Link
Adam H.	Hi, I was wondering - is it possible (or what is the best practice) when it comes to joining a second table in a query, where the first table doesn't have any records to match on. ie: If I have a list of names in one table, and a list of courses in another table - and I want to create a result where there is a record for each name and course - how can I go about this. I've tried: Select N.Firstname, C.Course From Names N left outer join Course C on (C.CourseDate = '2014') ....but this fails The tables/data I might have are: Names ~~~~~~~~~~~~~ BOB BILL BARRY Courses ~~~~~~~~~~~~~ Maths Science and I would like a result of BOB Maths BOB Science BILL Maths BILL Science BARRY Maths BARRY Science Is there a way to achieve this? Best Regards Adam.
Thu, Feb 27 2014 1:49 PM	Permanent Link
John Easley	It's called a Cartesian join. Try SELECT * FROM Names, Courses If you want to filter by year, just do that in the WHERE clause WHERE CourseDate = '2014' etc.. John "Adam H." wrote: Hi, I was wondering - is it possible (or what is the best practice) when it comes to joining a second table in a query, where the first table doesn't have any records to match on. ie: If I have a list of names in one table, and a list of courses in another table - and I want to create a result where there is a record for each name and course - how can I go about this. I've tried: Select N.Firstname, C.Course From Names N left outer join Course C on (C.CourseDate = '2014') ....but this fails The tables/data I might have are: Names ~~~~~~~~~~~~~ BOB BILL BARRY Courses ~~~~~~~~~~~~~ Maths Science and I would like a result of BOB Maths BOB Science BILL Maths BILL Science BARRY Maths BARRY Science Is there a way to achieve this? Best Regards Adam.
Thu, Feb 27 2014 4:02 PM	Permanent Link
Adam H.	Hi John, > It's called a Cartesian join. Try > > SELECT * FROM Names, Courses > > If you want to filter by year, just do that in the WHERE clause > > WHERE CourseDate = '2014' etc.. Aaah - I was looking at it the wrong way. I have used them before, but must be nigh on 10 years ago - my memories coming back now thanks to you. Cheers Adam.