Warning

 

Close

Confirm Action

Are you sure you wish to do this?

Confirm Cancel
BCM
Durkin Tactical Franklin Armory
User Panel

Site Notices
Arrow Left Previous Page
Page / 2
Posted: 11/29/2016 11:57:55 PM EDT
I am struggling with some homework and could use some help please.
Link Posted: 11/30/2016 12:00:11 AM EDT
[#1]
Yes, it's what I do for a living.






But if I do your homework you'll never learn.  So I'll give you hints.  So ask your questions.
Link Posted: 11/30/2016 12:00:45 AM EDT
[#2]
Gotta post your question and maybe which database you are using. Theres a tech forum that this kinda stuff usually goes in but its cool here too.
Link Posted: 11/30/2016 12:01:49 AM EDT
[#3]
Link Posted: 11/30/2016 12:03:42 AM EDT
[#4]
Sure.  Which dbms?
Link Posted: 11/30/2016 12:04:49 AM EDT
[#5]
SELECT * from TABLE

all you need to know.

Link Posted: 11/30/2016 12:14:41 AM EDT
[#6]
This isn't my major just a required IS database class so we are learning using Microsoft Access.





I have to do a parameter query where I Report the least expensive copy of a book whose title is entered.







I have a table for book and one for book_copy







I believe I need to do a join for them and then do the parameter query.

























This is what I have so far







Select*


from Book


where Title = [Enter book title];











 
Link Posted: 11/30/2016 12:16:53 AM EDT
[#7]

Discussion ForumsJump to Quoted PostQuote History
Quoted:


This isn't my major just a required IS database class so we are learning using Microsoft Access.



I have to do a parameter query where I Report the least expensive copy of a book whose title is entered.





I have a table for book and one for book_copy





I believe I need to do a join for them and then do the parameter query.
http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png
This is what I have so far





Select*

from Book

where Title = [Enter book title];
 
View Quote




 
Well, for starters, you don't have to select *. You only need the title and the asking price, right?
Link Posted: 11/30/2016 12:18:03 AM EDT
[#8]

Discussion ForumsJump to Quoted PostQuote History
Quoted:





  Well, for starters, you don't have to select *. You only need the title and the asking price, right?

View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:



Quoted:

This isn't my major just a required IS database class so we are learning using Microsoft Access.



I have to do a parameter query where I Report the least expensive copy of a book whose title is entered.





I have a table for book and one for book_copy





I believe I need to do a join for them and then do the parameter query.
http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png
This is what I have so far





Select*

from Book

where Title = [Enter book title];
 


  Well, for starters, you don't have to select *. You only need the title and the asking price, right?

Yes. I need to display the minimum price as well. So would that be a min() type of statement?

 
Link Posted: 11/30/2016 12:19:41 AM EDT
[#9]
Book has the title.

Book_copy has the price.

So you know you need to join those.

TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
Link Posted: 11/30/2016 12:20:04 AM EDT
[#10]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
This isn't my major just a required IS database class so we are learning using Microsoft Access.

I have to do a parameter query where I Report the least expensive copy of a book whose title is entered.


I have a table for book and one for book_copy


I believe I need to do a join for them and then do the parameter query.




<a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png</a>




This is what I have so far


Select*
from Book
where Title = [Enter book title];



 
View Quote


You have a unique identifier. . . can't you just do a v-lookup and phone it in
Link Posted: 11/30/2016 12:21:04 AM EDT
[#11]



Discussion ForumsJump to Quoted PostQuote History
Quoted:
Yes. I need to display the minimum price as well. So would that be a min() type of statement?  
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:






Quoted:



This isn't my major just a required IS database class so we are learning using Microsoft Access.
I have to do a parameter query where I Report the least expensive copy of a book whose title is entered.
I have a table for book and one for book_copy
I believe I need to do a join for them and then do the parameter query.
http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png
This is what I have so far
Select*



from Book



where Title = [Enter book title];
 




  Well, for starters, you don't have to select *. You only need the title and the asking price, right?



Yes. I need to display the minimum price as well. So would that be a min() type of statement?  
He is saying you are returning all fields.  You should specify specific fields to return, only the ones you need, and you are correct, you need to do a join on the field that contains the same data in each table.
 
Link Posted: 11/30/2016 12:21:42 AM EDT
[#12]
Link Posted: 11/30/2016 12:23:57 AM EDT
[#13]
Router# configure terminal

or config t for short...

Wait, Wrong system. LOL
Link Posted: 11/30/2016 12:24:35 AM EDT
[#14]
Link Posted: 11/30/2016 12:24:43 AM EDT
[#15]

Discussion ForumsJump to Quoted PostQuote History
Quoted:


Book has the title.



Book_copy has the price.



So you know you need to join those.



TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
View Quote
Yeah I got that. I keep getting an error about book.mdb not being able to be found

 
Link Posted: 11/30/2016 12:25:12 AM EDT
[#16]
select price,Title from Book order by price asc limit 1;
Link Posted: 11/30/2016 12:25:17 AM EDT
[#17]

Discussion ForumsJump to Quoted PostQuote History
Quoted:



Yeah I got that. I keep getting an error about book.mdb not being able to be found  
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:



Quoted:

Book has the title.



Book_copy has the price.



So you know you need to join those.



TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
Yeah I got that. I keep getting an error about book.mdb not being able to be found  
Paste your join statement.



 
Link Posted: 11/30/2016 12:26:06 AM EDT
[#18]


Discussion ForumsJump to Quoted PostQuote History
Quoted:



select price,title from table order by price asc limit 1;
View Quote
1.  It's better to answer someone's questions than just give them the answer.....





2.  I would fire you if I saw that.



3. You are wrong.





 
Link Posted: 11/30/2016 12:26:51 AM EDT
[#19]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
1.  It's better to answer someone's questions than just give them the answer.....

2.  I would fire you if I saw that.

3. You are wrong.
 
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
select price,title from table order by price asc limit 1;
1.  It's better to answer someone's questions than just give them the answer.....

2.  I would fire you if I saw that.

3. You are wrong.
 

I wouldnt work for you with an attitude like that so we're ok.
Link Posted: 11/30/2016 12:28:24 AM EDT
[#20]

Discussion ForumsJump to Quoted PostQuote History
Quoted:



Paste your join statement.

 
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:



Quoted:


Quoted:

Book has the title.



Book_copy has the price.



So you know you need to join those.



TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
Yeah I got that. I keep getting an error about book.mdb not being able to be found  
Paste your join statement.

 


 
Link Posted: 11/30/2016 12:28:38 AM EDT
[#21]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
select price,Title from Book order by price asc limit 1;
View Quote


^this
Link Posted: 11/30/2016 12:30:11 AM EDT
[#22]

Discussion ForumsJump to Quoted PostQuote History
Quoted:



Quoted:


Quoted:


Quoted:

Book has the title.



Book_copy has the price.



So you know you need to join those.



TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
Yeah I got that. I keep getting an error about book.mdb not being able to be found  
Paste your join statement.

 
http://i1201.photobucket.com/albums/bb358/sabroso1/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png  


SELECT `Book`.`Title`, `Book_Copy`.`AskingPrice` FROM `Book`, `Book_Copy` WHERE `Book`.`ISBN` = `Book_Copy`.`ISBN` AND `Book`.`Title` = 'The Title' ORDER BY `Book_Copy`.`AskingPrice` ASC LIMIT 1;



 
Link Posted: 11/30/2016 12:30:30 AM EDT
[#23]
Quoted:
I am struggling with some homework and could use some help please.
View Quote


SELECT *
FROM rtfm
WHERE Help = 'RTFM'


That should get you started
Link Posted: 11/30/2016 12:32:11 AM EDT
[#24]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Paste your join statement.
 
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
Book has the title.

Book_copy has the price.

So you know you need to join those.

TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
Yeah I got that. I keep getting an error about book.mdb not being able to be found  
Paste your join statement.
 


This. Also Access has a particularly fucked variant of SQL, and I have a hard time remembering what does and doesn't work in it. IIRC, joins may require some careful parenthesis to work right. There's a query builder in it that can help you get it right.
Link Posted: 11/30/2016 12:32:20 AM EDT
[#25]
Discussion ForumsJump to Quoted PostQuote History
Quoted:


^this
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
select price,Title from Book order by price asc limit 1;


^this



Link Posted: 11/30/2016 12:32:28 AM EDT
[#26]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
select price,Title from Book order by price asc limit 1;
View Quote


Didn't read the schema.
Didn't read the question.
Uses MySQL.

Lemme guess, developer?
Link Posted: 11/30/2016 12:32:35 AM EDT
[#27]
SELECT
   b.Title,
   b.ISBN,
   MIN(c.AskingPrice) AskingPrice
FROM Book b
JOIN Book_Copy c
   ON b.ISBN = c.ISBN
WHERE b.Title = [Enter book title]
GROUP BY b.Title, b.ISBN;

(eta. forgot the GROUP BY...)
Link Posted: 11/30/2016 12:34:12 AM EDT
[#28]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
All I know about SQL is what I learned from xkcd.

http://imgs.xkcd.com/comics/exploits_of_a_mom.png
View Quote

that's funny as fuck
Link Posted: 11/30/2016 12:35:39 AM EDT
[#29]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
SELECT
   b.Title,
   b.ISBN,
   MIN(c.AskingPrice) AskingPrice
FROM Book b
JOIN Book_Copy c
   ON b.ISBN = c.ISBN
WHERE b.Title = [Enter book title]
GROUP BY b.Title, b.ISBN;

(eta. forgot the GROUP BY...)
View Quote


No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly.
Link Posted: 11/30/2016 12:36:24 AM EDT
[#30]

Discussion ForumsJump to Quoted PostQuote History
Quoted:



Quoted:


Quoted:


Quoted:

Book has the title.



Book_copy has the price.



So you know you need to join those.



TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
Yeah I got that. I keep getting an error about book.mdb not being able to be found  
Paste your join statement.

 
http://i1201.photobucket.com/albums/bb358/sabroso1/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png  
I think that is mySQL, I am not familar with it and work with the enterprise edition, but I think it thinks book is a database instead of a table name.  For example a call outside of a database query analyzer is database.dbo.table.  I think you need to call as "datebase name".book.isbn.  I will let someone more familiar with mySQL chime in.



also you are missing your join statement.  For example:



Select * from table1 as t1 (nolock)

inner join table2 as t2

on t1.field1 = t2.field1

where etc etc



You can also left join, right join and outer join.



 
Link Posted: 11/30/2016 12:37:10 AM EDT
[#31]
Discussion ForumsJump to Quoted PostQuote History
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
select price,Title from Book order by price asc limit 1;


^this



http://i.imgur.com/bLutSUl.jpg


ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need.
Link Posted: 11/30/2016 12:37:52 AM EDT
[#32]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
I think that is mySQL, I am not familar with it and work with the enterprise edition, but I think it thinks book is a database instead of a table name.  For example a call outside of a database query analyzer is database.dbo.table.  I think you need to call as "datebase name".book.isbn.  I will let someone more familiar with mySQL chime in.

also you are missing your join statement.  For example:

Select * from table1 as t1 (nolock)
inner join table2 as t2
on t1.field1 = t2.field1
where etc etc

You can also left join, right join and outer join.
 
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Book has the title.

Book_copy has the price.

So you know you need to join those.

TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
Yeah I got that. I keep getting an error about book.mdb not being able to be found  
Paste your join statement.
 
http://i1201.photobucket.com/albums/bb358/sabroso1/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png  
I think that is mySQL, I am not familar with it and work with the enterprise edition, but I think it thinks book is a database instead of a table name.  For example a call outside of a database query analyzer is database.dbo.table.  I think you need to call as "datebase name".book.isbn.  I will let someone more familiar with mySQL chime in.

also you are missing your join statement.  For example:

Select * from table1 as t1 (nolock)
inner join table2 as t2
on t1.field1 = t2.field1
where etc etc

You can also left join, right join and outer join.
 
n
That will send OP into a coma, you know that, right?  
Link Posted: 11/30/2016 12:38:10 AM EDT
[#33]


Discussion ForumsJump to Quoted PostQuote History
Quoted:
No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly.


View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:





Quoted:


SELECT


   b.Title,


   b.ISBN,


   MIN(c.AskingPrice) AskingPrice


FROM Book b


JOIN Book_Copy c


   ON b.ISBN = c.ISBN


WHERE b.Title = [Enter book title]


GROUP BY b.Title, b.ISBN;





(eta. forgot the GROUP BY...)






No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly.


My teacher had us learning joins like this







 
Link Posted: 11/30/2016 12:39:53 AM EDT
[#34]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
My teacher had us learning joins like this

<a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a>
 
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
SELECT
   b.Title,
   b.ISBN,
   MIN(c.AskingPrice) AskingPrice
FROM Book b
JOIN Book_Copy c
   ON b.ISBN = c.ISBN
WHERE b.Title = [Enter book title]
GROUP BY b.Title, b.ISBN;

(eta. forgot the GROUP BY...)


No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly.
My teacher had us learning joins like this

<a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a>
 


That's the gay way.
Link Posted: 11/30/2016 12:40:47 AM EDT
[#35]

Discussion ForumsJump to Quoted PostQuote History
Quoted:
No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly.

View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:



Quoted:

SELECT

   b.Title,

   b.ISBN,

   MIN(c.AskingPrice) AskingPrice

FROM Book b

JOIN Book_Copy c

   ON b.ISBN = c.ISBN

WHERE b.Title = [Enter book title]

GROUP BY b.Title, b.ISBN;



(eta. forgot the GROUP BY...)




No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly.

This the answer pretty much written out for you.  The guy above me is right about the variable, but I don't think your teacher is looking for that much thought.  Would probably cause him/her to make a special version of the test for you .



 
Link Posted: 11/30/2016 12:41:21 AM EDT
[#36]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
<a href="http://s1201.photobucket.com/user/sabroso1/media/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png</a>  
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
Quoted:
Book has the title.

Book_copy has the price.

So you know you need to join those.

TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
Yeah I got that. I keep getting an error about book.mdb not being able to be found  
Paste your join statement.
 
<a href="http://s1201.photobucket.com/user/sabroso1/media/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png</a>  


try

from book, book_copy

Link Posted: 11/30/2016 12:41:52 AM EDT
[#37]


Select b.isbn, c.price

From book b, book_copy c

Where

 B.title =  'UserEntered Name'

 And   B.isbn = c.Isbn

 And c.Price = ( select min(price) from book_copy d where d.isbn = b.isbn)




Get the isbn from book. Join it up with all of the cook_copy rows

Identify the lowest cost book-copy row with a sun select use that value for the seco d joi .




Done
Link Posted: 11/30/2016 12:45:34 AM EDT
[#38]

Discussion ForumsJump to Quoted PostQuote History
Quoted:


Select b.isbn, c.price

From book b, book_copy c

Where

 B.title =  'UserEntered Name'

 And   B.isbn = c.Isbn

 And c.Price = ( select min(price) from book_copy d where d.isbn = b.isbn)





Get the isbn from book. Join it up with all of the cook_copy rows

Identify the lowest cost book-copy row with a sun select use that value for the seco d joi .





Done

View Quote
I'm kinda trying to keep the syntax the same as the way she's been teaching it. We haven't learned the b.xxx c.xxx type stuff
Link Posted: 11/30/2016 12:46:48 AM EDT
[#39]
SELECT TOP 1
 b.ISBN,b.Title,c.AskingPrice
FROM
 Book b
 INNER JOIN Book_Copy c ON c.ISBN = b.ISBN
WHERE
 b.Title = @EnteredBookTitle
ORDER BY
 c.AskingPrice ASC
Link Posted: 11/30/2016 12:47:42 AM EDT
[#40]
SELECT 'Hello World';
Link Posted: 11/30/2016 12:50:07 AM EDT
[#41]



Discussion ForumsJump to Quoted PostQuote History
Quoted:
I'm kinda trying to keep the syntax the same as the way she's been teaching it. We haven't learned the b.xxx c.xxx type stuff
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:



Select b.isbn, c.price



From book b, book_copy c



Where



 B.title =  'UserEntered Name'



 And   B.isbn = c.Isbn



 And c.Price = ( select min(price) from book_copy d where d.isbn = b.isbn)
Get the isbn from book. Join it up with all of the cook_copy rows



Identify the lowest cost book-copy row with a sun select use that value for the seco d joi .
Done



I'm kinda trying to keep the syntax the same as the way she's been teaching it. We haven't learned the b.xxx c.xxx type stuff
That's just an alias.  For example if you had a table called klfajkjoiafnianjofijeaifjaiw.  You could say from klfajkjoiafnianjofijeaifjaiw k, or from klfajkjoiafnianjofijeaifjaiw as k.  From that point on in the rest of the query you could just type k and it would reference the table.  You don't have to do it, but you will learn to love it.



 
Link Posted: 11/30/2016 12:50:26 AM EDT
[#42]
Anyone have any good cliffsnotes guides for beginners in SQL? I have about 50 gigs of data that would be much easier to work with if I learned how to use database software.
Link Posted: 11/30/2016 12:51:03 AM EDT
[#43]
Discussion ForumsJump to Quoted PostQuote History
Quoted:
My teacher had us learning joins like this

<a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a>
 
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
SELECT
   b.Title,
   b.ISBN,
   MIN(c.AskingPrice) AskingPrice
FROM Book b
JOIN Book_Copy c
   ON b.ISBN = c.ISBN
WHERE b.Title = [Enter book title]
GROUP BY b.Title, b.ISBN;

(eta. forgot the GROUP BY...)


No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly.
My teacher had us learning joins like this

<a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a>
 


Yeah, that's awful if you're doing anything non-trivial. Or even if you aren't. But classes being what they are, suppose you'll have to do it that way for now.
Link Posted: 11/30/2016 12:53:20 AM EDT
[#44]
Discussion ForumsJump to Quoted PostQuote History
Quoted:


try

from book, book_copy

View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Book has the title.

Book_copy has the price.

So you know you need to join those.

TO join - you need to have a unique ID shared by each table - which in this case is ISBN.
Yeah I got that. I keep getting an error about book.mdb not being able to be found  
Paste your join statement.
 
<a href="http://s1201.photobucket.com/user/sabroso1/media/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png</a>  


try

from book, book_copy



But yeah, this. The FROM clause should only have tables, not fields.
Link Posted: 11/30/2016 12:54:20 AM EDT
[#45]

Discussion ForumsJump to Quoted PostQuote History
Quoted:
Yeah, that's awful if you're doing anything non-trivial. Or even if you aren't. But classes being what they are, suppose you'll have to do it that way for now.
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:



Quoted:


Quoted:


Quoted:

SELECT

   b.Title,

   b.ISBN,

   MIN(c.AskingPrice) AskingPrice

FROM Book b

JOIN Book_Copy c

   ON b.ISBN = c.ISBN

WHERE b.Title = [Enter book title]

GROUP BY b.Title, b.ISBN;



(eta. forgot the GROUP BY...)




No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly.

My teacher had us learning joins like this



<a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a>

 




Yeah, that's awful if you're doing anything non-trivial. Or even if you aren't. But classes being what they are, suppose you'll have to do it that way for now.
Yeah.... I'm not particularly fond of the powerpoint teaching style

 
Link Posted: 11/30/2016 12:56:46 AM EDT
[#46]


Discussion ForumsJump to Quoted PostQuote History
Quoted:
But yeah, this. The FROM clause should only have tables, not fields.


View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:





Quoted:













try





from book, book_copy











But yeah, this. The FROM clause should only have tables, not fields.


It looked weird for sure but I figured it was some mySQL weirdness, never messed with it.





 
Link Posted: 11/30/2016 1:02:39 AM EDT
[#47]
Discussion ForumsJump to Quoted PostQuote History
Quoted:


ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need.
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
Quoted:
select price,Title from Book order by price asc limit 1;


^this



http://i.imgur.com/bLutSUl.jpg


ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need.


You're a Solution Architect too?

Clearly, your data is replicated and duplicated in too many places.  Just insert the price into the table with the ISBN and book name and drop the other table.  Then delete all the duplicate records which don't have the lowest price.
Link Posted: 11/30/2016 1:04:33 AM EDT
[#48]




Discussion ForumsJump to Quoted PostQuote History
Quoted:
You're a Solution Architect too?
Clearly, your data is replicated and duplicated in too many places.  Just insert the price into the table with the ISBN and book name and drop the other table.  Then delete all the duplicate records which don't have the lowest price.
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:




select price,Title from Book order by price asc limit 1;

^this

http://i.imgur.com/bLutSUl.jpg

ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need.

You're a Solution Architect too?
Clearly, your data is replicated and duplicated in too many places.  Just insert the price into the table with the ISBN and book name and drop the other table.  Then delete all the duplicate records which don't have the lowest price.
Views need to die in a fire, I would love 10 minutes alone in a room with the DBA that was here before I was. I am not a DBA, but I have develop using the views of views of views of views he left behind...  I call it going down the rabbit hole.  By the time I get the bottom I forgot what the fuck I was looking for.
 
Link Posted: 11/30/2016 1:10:09 AM EDT
[#49]
Discussion ForumsJump to Quoted PostQuote History
Quoted:


You're a Solution Architect too?

Clearly, your data is replicated and duplicated in too many places.  Just insert the price into the table with the ISBN and book name and drop the other table.  Then delete all the duplicate records which don't have the lowest price.
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
select price,Title from Book order by price asc limit 1;


^this



http://i.imgur.com/bLutSUl.jpg


ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need.


You're a Solution Architect too?

Clearly, your data is replicated and duplicated in too many places.  Just insert the price into the table with the ISBN and book name and drop the other table.  Then delete all the duplicate records which don't have the lowest price.




Schema changes are unsupported.

You must be new here?
Link Posted: 11/30/2016 1:16:35 AM EDT
[#50]
Discussion ForumsJump to Quoted PostQuote History
Quoted:




Schema changes are unsupported.

You must be new here?
View Quote View All Quotes
View All Quotes
Discussion ForumsJump to Quoted PostQuote History
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
...

^this



http://i.imgur.com/bLutSUl.jpg


ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need.


You're a Solution Architect too?

Clearly, your data is replicated and duplicated in too many places.  Just insert the price into the table with the ISBN and book name and drop the other table.  Then delete all the duplicate records which don't have the lowest price.




Schema changes are unsupported.

You must be new here?


Yeah, you have 30 years of jankey code and "just get it working" fixes to support!
Arrow Left Previous Page
Page / 2
Close Join Our Mail List to Stay Up To Date! Win a FREE Membership!

Sign up for the ARFCOM weekly newsletter and be entered to win a free ARFCOM membership. One new winner* is announced every week!

You will receive an email every Friday morning featuring the latest chatter from the hottest topics, breaking news surrounding legislation, as well as exclusive deals only available to ARFCOM email subscribers.


By signing up you agree to our User Agreement. *Must have a registered ARFCOM account to win.
Top Top