Querying multiple tables

  • Thread starter Thread starter Denny Lee
  • Start date Start date
D

Denny Lee

Hello,
I have been programming in C# for a while but have not done much with
ADO.Net. I am running into a problem that I am not sure how to solve.
In my database I have 3 tables with 2 junction tables relating them.
Here is the basic structure

Book Title
-------------
Title_PK
Book_Title


Author
----------
Author_PK
Name

Publishing Company
------------
Company_PK
Company_Name


Author_Comapany
--------------
Title_PK
Company_PK

Junction 2
---------------
Author_PK
Title_PK

What I need is to be able to find all book titles written by Author A
and Author B and published by Company A.

So far I figured out how to give a SQL command to get the data out of
one junction table and 2 data tables, such as find all books from
Author A and Author B, but I can't figure out how to link in the other
tables. This will be access via ASP.net but I am not sure if this
should be done via SQL or some controls in .NET.

I have been messing with this for couple days, so any suggestion would
be great. Thanks.
 
U¿ytkownik "Denny Lee said:
Hello,
I have been programming in C# for a while but have not done much with
ADO.Net. I am running into a problem that I am not sure how to solve.
In my database I have 3 tables with 2 junction tables relating them.
Here is the basic structure

Book Title
-------------
Title_PK
Book_Title


Author
----------
Author_PK
Name

Publishing Company
------------
Company_PK
Company_Name


Author_Comapany
--------------
Title_PK
Company_PK

Junction 2
---------------
Author_PK
Title_PK

What I need is to be able to find all book titles written by Author A
and Author B and published by Company A.

So far I figured out how to give a SQL command to get the data out of
one junction table and 2 data tables, such as find all books from
Author A and Author B, but I can't figure out how to link in the other
tables. This will be access via ASP.net but I am not sure if this
should be done via SQL or some controls in .NET.

I have been messing with this for couple days, so any suggestion would
be great. Thanks.
Click to expand...

I think, that best way is to use sql query, for example if you seek all
books written by one or other author and published by specific company:

Select Distinct
T.Title_Pk,
T.Book_Title
From
Book_Title As T
Inner Join
Junction_2 As J
On
T.Title_Pk = J.Title_Pk
Inner Join
Author As A
On
J.Author_Pk = A.Author_Pk
Inner Join
Author_Company As Ac
On
T.Title_Pk = Ac.Title_Pk
Inner Join
Publishing_Company As Pc
On
Ac.Company_Pk = Pc.Company_Pk
Where
(A.Name = 'John Smith"
Or
A.Name = "John Stuart Mill")
And
Pc.Company_Name = "Amazon"

But if you seek books written by two authors simultaneously you must use
some other sql, for example:

Select
T.Title_Pk,
T.Book_Title
From
Book_Title As T
Where
Exists(
Select *
From
Junction_2 As A1
Inner Join
Author As A
On
A1.Author_Pk = A.Author_Pk
Where
A1.Title_Pk = T.Title_Pk
And
A.Name = 'John Smith"
)
And
Exists(
Select *
From
Junction_2 As A2
Inner Join
Author As A
On
A2.Author_Pk = A.Author_Pk
Where
A2.Title_Pk = T.Title_Pk
And
A.Name = "John Stuart Mill"
)
And
Exists(
Select *
From
Author_Company As Ac
Inner Join
Publishing_Company As Pc
On
Ac.Company_Pk = Pc.Company_Pk
Where
T.Title_Pk = Ac.Title_Pk
And
Pc.Company_Name = "Amazon"
)

The sql looks some strange but if user choose authors and publishing company
using comboboxes there is possibility to use primary keys of authors and
publishing company instead of athors and company name, for example:
Select
T.Title_Pk,
T.Book_Title
From
Book_Title As T
Where
Exists(
Select *
From
Junction_2 As A1
Where
A1.Title_Pk = T.Title_Pk
And
A1.Author_Pk = 123
)
And
Exists(
Select *
From
Junction_2 As A1
Where
A1.Title_Pk = T.Title_Pk
And
A1.Author_Pk = 463
)
And
Exists(
Select *
From
Author_Company As Ac
Where
T.Title_Pk = Ac.Title_Pk
And
Ac.Company_Pk = 34634
)

If number of coauthors is variable then sql must be yet another.

Regards,
Grzegorz
 
Thanks. I will give it a try.

Grzegorz Danowski said:
I think, that best way is to use sql query, for example if you seek all
books written by one or other author and published by specific company:

Select Distinct
T.Title_Pk,
T.Book_Title
From
Book_Title As T
Inner Join
Junction_2 As J
On
T.Title_Pk = J.Title_Pk
Inner Join
Author As A
On
J.Author_Pk = A.Author_Pk
Inner Join
Author_Company As Ac
On
T.Title_Pk = Ac.Title_Pk
Inner Join
Publishing_Company As Pc
On
Ac.Company_Pk = Pc.Company_Pk
Where
(A.Name = 'John Smith"
Or
A.Name = "John Stuart Mill")
And
Pc.Company_Name = "Amazon"

But if you seek books written by two authors simultaneously you must use
some other sql, for example:

Select
T.Title_Pk,
T.Book_Title
From
Book_Title As T
Where
Exists(
Select *
From
Junction_2 As A1
Inner Join
Author As A
On
A1.Author_Pk = A.Author_Pk
Where
A1.Title_Pk = T.Title_Pk
And
A.Name = 'John Smith"
)
And
Exists(
Select *
From
Junction_2 As A2
Inner Join
Author As A
On
A2.Author_Pk = A.Author_Pk
Where
A2.Title_Pk = T.Title_Pk
And
A.Name = "John Stuart Mill"
)
And
Exists(
Select *
From
Author_Company As Ac
Inner Join
Publishing_Company As Pc
On
Ac.Company_Pk = Pc.Company_Pk
Where
T.Title_Pk = Ac.Title_Pk
And
Pc.Company_Name = "Amazon"
)

The sql looks some strange but if user choose authors and publishing company
using comboboxes there is possibility to use primary keys of authors and
publishing company instead of athors and company name, for example:
Select
T.Title_Pk,
T.Book_Title
From
Book_Title As T
Where
Exists(
Select *
From
Junction_2 As A1
Where
A1.Title_Pk = T.Title_Pk
And
A1.Author_Pk = 123
)
And
Exists(
Select *
From
Junction_2 As A1
Where
A1.Title_Pk = T.Title_Pk
And
A1.Author_Pk = 463
)
And
Exists(
Select *
From
Author_Company As Ac
Where
T.Title_Pk = Ac.Title_Pk
And
Ac.Company_Pk = 34634
)

If number of coauthors is variable then sql must be yet another.

Regards,
Grzegorz
Click to expand...
 
Back
Top