Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JohnGault777
Starting Member
31 Posts |
 Posted - 2011-02-25 : 14:19:45
|
| Sorry if the title isn't very descriptive but I didn't know how to write it. I want to pull only the first entry from a table where the PK / FK match. I have a table showing customers that links to a table showing locations. The locations are listed 1, 2, 3, 4,...etc.. The locations have a territory ID. I want to pull the Territory ID for the 1st location only (it is typically the primary location). So when the PK in Table 1 equal the FK in Table 2 and the location ID is 1 pull the Territory. What is the syntax to write this kind of statement?JG777 |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-25 : 14:41:14
|
yes its not descriptive, but never mind  Can you come up with sample data of your table(s), PK, FKs of those tables and The Desired Output your are looking for. this will help us to guide you accordingly.CheersMIK |
 |
|
|
JohnGault777
Starting Member
31 Posts |
Posted - 2011-02-25 : 15:32:21
|
| Ok here are the necessary tables I'm working with.dbo.Auto (example: 2,000 rows)-------------autoID (PK)dbo.Vehicle (example: 5,000 rows)-------------vehicleID (PK)autoID (FK)vehicleNumber (1, 2, 3, 4,...etc. for each AutoID)TerritoryI know it could do an inner join to get all the Territories, but it would take the AutoID and duplicate it for every different vehicle. Therefore, the output would be 5,000 rows; one for each vehicle. I want the output to only be 2,000 rows and only return the Territory for the first vehicleNumber. I know the description is different from the previous post but I was trying to simplify it. How do I write the statement to say "When the AutoID from the Auto table equals the AutoID in the Vehicle table and the vehiclenumber is 1 pull the territory."?Is this enough information?JG777 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2011-02-25 : 15:44:42
|
Based on this:"When the AutoID from the Auto table equals the AutoID in the Vehicle table and the vehiclenumber is 1 pull the territory."SELECT v.TerritoryFROM Vehicle vJOIN Auto a ON v.AutoID = a.AutoIDAND vehicleNumber = 1 Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
JohnGault777
Starting Member
31 Posts |
Posted - 2011-02-25 : 16:13:17
|
| Brett - I know physical order has no meaning. I guess my explanation wasn't clear. vehicleNumber is literally counting up: 1, 2, 3, 4, 5. So the first vehicle is 1, the second vehicle is 2....etc. I apologize for not posting the question correctly. I have literally been shoved into working with SQL. I've only been working with it for about 5 weeks now. I can barely understand your hint link even though it is probably very basic (I'm struggling with basic right now). Thank you for the other links. I'm very interested in the SQL Server Analysis Services. I've been reading some of that in the last little bit. Skorch - Using that syntax worked perfectly. Thank you. Thank you to all for baring with me and helping me learn this stuff. The old saying "you learning something new everyday." doesn't apply to me right now. I'm learning 1,000 new things each day simply by necessity. JG777 |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2011-02-25 : 16:28:41
|
| You're very welcome. Glad it worked out :)Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
|
|
|
|
|
Pulling the first from a multiples during Join