10 Replies - 1007 Views - Last Post: 22 April 2015 - 04:02 PM Rate Topic: -----

#1 skatingrocker17   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 138
  • Joined: 01-September 11

Querying two databases (basic)

Posted 21 April 2015 - 07:15 PM

I have three tables that I've created. Table(field, field...)
Author(AuthorName, Address, Age)
Book(BookTitle, BookAuthor, BookPublisher, PublishDate)
Publisher(PublisherName, PublisherAddress)

I'm having an issue trying to figure out how to query 2-3 of the databases at once. I have no issues pulling the data from each database individually. I know that Author.AuthorName = Book.BookAuthor and Book.Publisher = Publisher.PublisherName. I think I should be using some sort of innter join because some of the fields are the same but I'm not sure how to implement it.

For example, one of the things I need to query is printing the Authors name, authors address, publisher date, and publisher name for titles between a certain range. I know the last part would be similar to"WHERE Pulisher.PublisherName BETWEEN value1 AND value2;".

I'm not sure where to go from after selecting the first few things from the authors databases.

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Querying two databases (basic)

#2 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 449
  • View blog
  • Posts: 2,186
  • Joined: 07-April 08

Re: Querying two databases (basic)

Posted 21 April 2015 - 07:23 PM

So it depends on what your requirements are as to how you should proceed. What I always do is Start from the top and work my way down. Find your primary object, which in this case looks like Titles, and then start adding in supporting data.

EX:

--Start with a basic query just grabbing titles
SELECT
    Book.BookTitle
FROM Book



--next add in your first set of supporting data.  In this case it is Author name/address
SELECT
    Book.BookTitle
,   Author.AuthorName
,   Author.Address
FROM Book
INNER JOIN Author
    ON Book.<Column> = Author.<Column>



Then add the next one untill you've got all of the data you want to output. After that work on the limiting factors.

You can have multiple inner joins to a single query, and because you are using Book as your main table, both Author and Publisher link to that table somehow. Just make sure you use that link in your ON clause and you'll be good to go.

Also as a quick note, you are querying multiple tables not databases. If you were querying multiple databases you would have tables within them. I.E. an Author database might have a AuthorType table, Authors table, and maybe PaymentInformation table, while an Author table is just a collection of Author records.

This post has been edited by rgfirefly24: 21 April 2015 - 07:32 PM

Was This Post Helpful? 1
  • +
  • -

#3 skatingrocker17   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 138
  • Joined: 01-September 11

Re: Querying two databases (basic)

Posted 21 April 2015 - 08:59 PM

Awesome. I was able to figure that out. I have a few questions using WHERE. I know it goes after the inner joins.

For my first query,
select author.AuthorName, author.Address, book.BookTitle, book.publDate, book.BookPublisher 
FROM book 
INNER JOIN auth ON book.BookAuthor=author.AuthorName;


I tried to just add WHERE book.BookTitle BETWEEN ‘R1’ AND ‘T2’;

All of the data is fake, so there are just random char values. For some reason, I couldn't get it to return any data after using the WHERE clause. Without it, it works just fine. I looked at a few examples and I'm not seeing where the error is.

For the second query there needs to be a WHERE clause to print all authors who have books in 2014, if the author has no books in 2014, the title and publisher columns are NULL.

select author.AuthorName, author.Age, author.Address, book.BookTitle, book.publDate, publisher.Address 
FROM book 
INNER JOIN author ON book.BookAuthor=author.AuthorName 
INNER JOIN publisher ON book.BookPublisher=publisher.PublisherName;


What confuses me about the date is that it's not just a year. It's an entire date AND time.
When I put the data in the database it was in this format:
insert into book values ('T6', 'A5', 'P3',
  STR_TO_DATE('2014/01/01', '%Y/%m/%d '));


So I'm not sure how to extract just the year and put in into a WHERE clause.

This post has been edited by andrewsw: 22 April 2015 - 02:34 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#4 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4202
  • View blog
  • Posts: 13,275
  • Joined: 08-June 10

Re: Querying two databases (basic)

Posted 21 April 2015 - 11:03 PM

Quote

So I'm not sure how to extract just the year and put in into a WHERE clause.

MySQL has the YEAR() function for that. other databases have similar functions.
Was This Post Helpful? 1
  • +
  • -

#5 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 449
  • View blog
  • Posts: 2,186
  • Joined: 07-April 08

Re: Querying two databases (basic)

Posted 22 April 2015 - 03:09 AM

Is there a reason you used between on a text field? What is your exact requirement?
Was This Post Helpful? 0
  • +
  • -

#6 skatingrocker17   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 138
  • Joined: 01-September 11

Re: Querying two databases (basic)

Posted 22 April 2015 - 08:35 AM

View Postrgfirefly24, on 22 April 2015 - 03:09 AM, said:

Is there a reason you used between on a text field? What is your exact requirement?


For the first query, it says to print for titles between R1 and T2 (both inclusive) OR published within the last 60 days. So that's what I took from it, to find titles between those fields.
Was This Post Helpful? 0
  • +
  • -

#7 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 449
  • View blog
  • Posts: 2,186
  • Joined: 07-April 08

Re: Querying two databases (basic)

Posted 22 April 2015 - 08:47 AM

you are correct, that's what they want it's just odd to see that. I've never in the 8 years as a developer used BETWEEN on text fields.

I did run a test (Below)

CREATE TABLE #Temp
(
	Col1 INT
,	Col2 VARCHAR(25)
)

INSERT INTO [#Temp]([Col1], [Col2])
VALUES	(0, 'A1')
,		(1, 'A2')
,		(2, 'B2')
,		(3, 'A3')
,		(4, 'R2')
,		(5, 'D2')
,		(6, 'E8')
,		(7, 'T3')
,		(8, 'T1')
,		(9, 'S4')
,		(10, 'S5')
,		(11, 'S6')
,		(12, 'R1')


SELECT
	*
FROM [#Temp] [T]
WHERE Col2 BETWEEN 'R2' AND 'T2'


DROP TABLE [#Temp]



And the returned result is what is expected:

Col1	Col2
4	R2
8	T1
9	S4
10	S5
11	S6



Post your exact query. It could be a matter of the Books with Title between R1 and T2 don't have a matching Author record to join to, and because you are using an INNER JOIN It filters out any data that doesn't have a match in the second table. Try changing it to a LEFT JOIN and see if you get results.
Was This Post Helpful? 1
  • +
  • -

#8 skatingrocker17   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 138
  • Joined: 01-September 11

Re: Querying two databases (basic)

Posted 22 April 2015 - 11:48 AM

I agree. It's a bit confusing. It works, but I have emailed the teacher and asked for clarity on what exactly he's looking for.

But any idea on how to isolate the year part of the date? A few posts up, Dormilich said there was a function for it but I'm not sure how to use that in the query.
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14092
  • View blog
  • Posts: 56,461
  • Joined: 12-June 08

Re: Querying two databases (basic)

Posted 22 April 2015 - 11:52 AM

He gave you the function name for MYSQL. If your DB is different perhaps explain what you are using or hit their docs.
Was This Post Helpful? 1
  • +
  • -

#10 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 449
  • View blog
  • Posts: 2,186
  • Joined: 07-April 08

Re: Querying two databases (basic)

Posted 22 April 2015 - 03:41 PM

So the function is the same between the two systems: YEAR(). A quick tip for the future. If you are working on sql and need to look something up, type the fuction + msdn into google and it will give you the msdn whitepaper for it.

Now, a practical example would be if I was a library and wanted to track how many times the books were checked out. For this let's assume I have a Books table: Books(BookID, AuthorID,...). Now let's assume I have another table called CheckoutHistory(BookID, CheckoutDate,CardID).

I could do something like this:
SELECT
    Book.BookID
,   SUM(COH.BookID)
FROM Books Book
INNER JOIN CheckoutHistory COH
    ON Book.BookID = COH.BookID
WHERE YEAR(COH.CheckoutDate) = 2015
GROUP BY Book.BookID



that would give me all books and their respective checkout totals. There are also MONTH(), and DAY() function as well.

This post has been edited by rgfirefly24: 22 April 2015 - 03:41 PM

Was This Post Helpful? 1
  • +
  • -

#11 skatingrocker17   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 138
  • Joined: 01-September 11

Re: Querying two databases (basic)

Posted 22 April 2015 - 04:02 PM

Yep I was able to find it. Most of what I'm looking for I can find on MSDN and W3Schools but sometimes it looks different from what I'm doing so I'm not sure. Thanks for your help.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1