SELECT - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-24 : 11:42:53

Hello,

I have 2 related tables:
[A] > Aid (PK), Title, Name, ...
[B] > Bid (PK), Aid (FK), B

I use the input parameterd@Aid
> Select the record in A where Aid = @Aid
> Select all the records in B where Aid = @AId

In my C# code I have a class named A with the properties:
Title (String)
Name (String)
Bs (Generic.List(Of B)

I think I should get 2 tables in a dataset and then populate my class.

How can I do this?

Thanks,
Miguel

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-24 : 11:45:11

So are you asking for help with C# code or SQL code?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-24 : 11:45:28

You could do that or have a single resultset with Title and Name repeated in each row.

select a.Title, a.Name
from a
left join b
on a.Aid = b.Aid
where a.Aid = @Aid

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-24 : 13:04:05

Hi Dinakar,

I was asking both.
I really don't know if in this cases I need to use two selects or an inner join ... I think I can use both depending of my C# code.

Does anyone knows any URL with such an example?

Thanks,
Miguel

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-24 : 13:13:03

quote:
Originally posted by shapper
I really don't know if in this cases I need to use two selects or an inner join ... I think I can use both depending of my C# code.

That depends on what data you need at the front end. I am guessing Nigel's code should be sufficient.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-24 : 14:09:35

Hi,

I have been trying to figure this out.

Basically my class has a similar structure then my tables.

For example, class A has 3 properties:
Title, Name and Generic.List(Of B)

For what I have been reading there are two options:
1. Use one select, with joins, getting only one table in my dataset;
2. Use one select for each table getting various tables in my dataset.

Which is the best option?

In relation to C# code I will post on .NET Inside SQL Server (2005).
Maybe it is better?

Thanks,
Miguel

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-24 : 20:27:36

Hi,

I created my SQL code that returns the records from various tables associated to a @PostId. It returns a table using Inner Join.

Isn't the table getting bigger when I use this approach rather then using the 5 SELECTS return 5 tables?

Here is the code I created:
(It runs ok. I didn't notice anything wrong)


SELECT * 
FROM dbo.by27_Blogger_Posts p
LEFT JOIN (
  SELECT * 
  FROM dbo.by27_Blogger_Comments c
  LEFT JOIN dbo.by27_Membership_Users u 
  ON c.AuthorId = u.UserId) AS cu 
ON p.PostId = cu.PostId
LEFT JOIN (
  SELECT * 
  FROM dbo.by27_Blogger_Ratings r
  LEFT JOIN dbo.by27_Membership_Users u 
  ON r.AuthorId = u.UserId) AS ru
ON p.PostId = ru.PostId
LEFT JOIN (
  SELECT cip.PostId, cip.CategoryId, c.CategoryText
  FROM dbo.by27_Blogger_CategoriesInPosts cip
  INNER JOIN dbo.by27_Blogger_Categories c
  ON c.CategoryId = cip.CategoryId) AS ccip
ON p.PostId = ccip.PostId
LEFT JOIN (
  SELECT tip.PostId, tip.TagId, t.TagText
  FROM dbo.by27_Blogger_TagsInPosts tip
  INNER JOIN dbo.by27_Blogger_Tags t
  ON t.TagId = tip.TagId) AS ttip
ON p.PostId = ttip.PostId
WHERE	p.PostId = @PostId
ORDER BY p.PostUpdatedDate

Thanks,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources