CODEWITHSUNDEEP
sqlsql-server
WizardDBA
WizardDBA

Reputation: 46

SQL Server - Get a parent row, and all his children, in a relationship 1: n

I am currently learning SQL, using SQL Server. I have two tables, which are tbAuthors and tbBooks as in the following sql code:

CREATE TABLE [dbo].[tbAuthors](
    [id_Author] [int] NOT NULL,
    [nameAuthor] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tbAuthors] PRIMARY KEY CLUSTERED ([id_Author] ASC)
)

CREATE TABLE [dbo].[tbBooks](
    [idBooks] [int] NOT NULL,
    [bookName] [varchar](50) NOT NULL,
    [FKAuthor] [int] NOT NULL,
 CONSTRAINT [PK_tbBooks] PRIMARY KEY CLUSTERED ([idBooks] ASC)ON [PRIMARY]
)

What I want is to get just the name of the author, and all his books, without the author's name appears repeatedly, through the following query, I get this data:

select distinct ta.id_Author, ta.nameAuthor, tb.bookName 
from tbAuthors as ta 
inner join tbBooks as tb on tb.FKAuthor=ta.id_Author where tb.FKAuthor = 1

and the sql result is this:

id_Author  nameAuthor   bookName
   1       Author1      Book1
   1       Author1      Book2
   1       Author1      Book3
   1       Author1      Book4

What I wanted to know was how do I get the query to that found in the following image?

id_Author   nameAuthor   bookName
   1        Author1      Book1
 empty      empty        Book2
 empty      empty        Book3
 empty      empty        Book4

Is it possible to get through a query, if so how?

Thank you for your attention to this problem.

Upvotes: 0

Views: 270

Answers (1)

Dylan Smith
Dylan Smith

Reputation: 22245

You really shouldn't ever want to do this via sql; it's not what sql is intended to accomplish. That kind of result should be handled in client code, at the presentation level. I'm guessing you just don't understand how to consume the resultset properly yet. What do you plan to do with the result set that you wouldn't want the authorname repeated for each row?

Upvotes: 3

Related Questions