| Author |
Topic |
|
kalaivani
Starting Member
11 Posts |
 Posted - 2007-12-19 : 02:11:48
|
| use default pubs database in sqlserver2000.use authors table and publishers table.Write a query to list first name, last of all authors and name of the publisher (if any) present in the same city as the author. If no publisher is present in the city where the author is located then the column should contain aNULL value. If there is more than one publisher in the citywhere the author is located, then the details of the author are to be repeated for each publisher.but there is no field match between authors table and publishers table. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2007-12-19 : 02:17:55
|
Don't homework questions like these just suck?Duane.  |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-12-19 : 02:32:08
|
| Post what you have tried and some one over here may guide uChiraghttp://www.chirikworld.com |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
kalaivani
Starting Member
11 Posts |
Posted - 2007-12-19 : 05:43:15
|
| i get the result partially by using the following query.select s.au_lname,s.au_fname,s.city,p.city,p.pub_name from authors s,publishers p where s.city=p.citybut, i need the result for else part of the query;If no publisher is present in the city where the author is located then the column should contain aNULL value. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-19 : 05:51:50
|
| This will give you the desired result:select s.au_lname,s.au_fname,s.city,p.city,p.pub_name from authors sleft outer join publishers p where s.city=p.citywhat your query does was to get the author details only when you have at least a publisher from the same city. INNER JOIN or simply join looks for matching records based on key in another table and returns the records from both tables only if a match is found.Left Outer Join retrieves all the results from left table regardless of whether it has a match in right table and for those which has a match it returns matching record values ( for the rest it puts a NULL value in column). |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2007-12-19 : 06:17:46
|
| I always like it when people don't answer the question and just give flippent comments instead, try this one if the question is below you answering just don't post anything |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2007-12-19 : 06:18:57
|
| And also look where it was posted in the "New to SQL Server" section so next time just don't ever enter that forum and you may not find the need to be sarcastic |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2007-12-19 : 07:11:31
|
| mmmmm......OK Boss!But maybe there should be a forum called "help me do my homework", this is clearly an assignment.Pubs database is used for learning purposes. Homework questions are frowned upon here.what will people learn if all their questions were answered by sql professionals.I would definately not want to hire any new developer who had all his questions answered by forum members whilst he was studying - my "sarcasm" was intended to let the op think for himself rather than have others help him out.How did you get through your studies - did you actually think problems through or did you have others do your assignments for you?Duane. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-19 : 07:14:43
|
I second that!Create a new forum, "Homework".
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
Joining more than one table without foreign key .