MySQL RSS MySQL RSS

Compare 2 tables for a equivlent set of values

Reply

•
•
Join Date: Apr 2008
Posts: 17
Reputation: fusi0n423 is an unknown quantity at this point 
Solved Threads: 0
fusi0n423 fusi0n423 is offline Offline
Newbie Poster

Compare 2 tables for a equivlent set of values

 
0
  #1
May 26th, 2009
I'm new to mysql and I need some more practice with this stuff since were going to have a test soon (this is not a homework assignment). One thing I still don't get is how to check if two tables contain an equal set of values.

Heres the practice problem I came up with based on a problem given where we had to find all sailors who rented all boats after a certain date.

My Problem:
I need to be able to see what sailors have rented the same boats as john doe rented after May 5, 2009. Heres my setup:
Sailor ( name: varchar(20), sid: integer)
Boat ( name: varchar(20), bid: integer)
Rental ( sid: integer, bid: integer, day: date)
and what I've tried so far:
create view john_doe_rentals as
select b.bid
from sailor s, boat b, rental r
where s.sid = r.sid and ( = 'John Doe') and r.date > "5/5/09" and rbid = b.bid;

create view sailor_rentals as
select s.name, r.bid
from sailor s, result r, john_doe_rentals j
where s.sid = r.sid and (s.name != 'John Doe') and r.date > "5/5/09" and j.bid = r.bid;

select name
from sailor_rentals s
where exist (select * from john_doe_rentals);

This gives me a syntax error. If anybody could help me out it'd be greatly appreciated.

Thanks
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2008
Posts: 823
Reputation: verruckt24 is a jewel in the rough verruckt24 is a jewel in the rough verruckt24 is a jewel in the rough verruckt24 is a jewel in the rough 
Solved Threads: 73
verruckt24's Avatar
verruckt24 verruckt24 is offline Offline
Practically a Posting Shark

Re: Compare 2 tables for a equivlent set of values

 
0
  #2
May 26th, 2009
sql Syntax (Toggle Plain Text) 
  1. CREATE view john_doe_rentals as
  2. SELECT b.bid
  3. FROM sailor s, boat b, rental r
  4. WHERE s.sid = r.sid AND ( = 'John Doe') AND r.DATE > "5/5/09" AND rbid = b.bid;

Here I guess you have forgotten to mention what equals to 'John Doe'.

sql Syntax (Toggle Plain Text) 
  1. CREATE view john_doe_rentals as
  2. SELECT b.bid
  3. FROM sailor s, boat b, rental r
  4. WHERE s.sid = r.sid AND ( = 'John Doe') AND r.DATE > "5/5/09" AND rbid = b.bid;

Here what is result ? Or should it be rentals.
Get up every morning and take a look at the Forbes' list of richest people. If your name doesn't appear.... GET TO WORK !!!
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2009
Posts: 3
Reputation: PoulH is an unknown quantity at this point 
Solved Threads: 0
PoulH PoulH is offline Offline
Newbie Poster

Re: Compare 2 tables for a equivlent set of values

 
0
  #3
May 26th, 2009
Do you need to do this automaticaly all the time?
If it is just something you need to do once in a while and would like to see the differences graphically I would suggest you use our tool SQLMerger for this, this is an easy and a preferable solution.

What is the syntax error you get?

The first view should be:

create view john_doe_rentals as
select b.bid
from sailor s, boat b, rental r
where s.sid = r.sid and ( s.name = 'John Doe') and r.date > "5/5/09" and rbid = b.bid;

Added "s.name"

maybe that is where the syntax error comes from?
Poul Halgaard, Software Developer Auisoft ApS
www.Auisoft.com/SQLMerger - Tool for comparing data in two databases. Compare, merge, edit & convert tables easily!
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2008
Posts: 823
Reputation: verruckt24 is a jewel in the rough verruckt24 is a jewel in the rough verruckt24 is a jewel in the rough verruckt24 is a jewel in the rough 
Solved Threads: 73
verruckt24's Avatar
verruckt24 verruckt24 is offline Offline
Practically a Posting Shark

Re: Compare 2 tables for a equivlent set of values

 
0
  #4
May 26th, 2009
•
•
•
•
Originally Posted by PoulH View Post
Do you need to do this automaticaly all the time?
If it is just something you need to do once in a while and would like to see the differences graphically I would suggest you use our tool SQLMerger for this, this is an easy and a preferable solution.

What is the syntax error you get?

The first view should be:

create view john_doe_rentals as
select b.bid
from sailor s, boat b, rental r
where s.sid = r.sid and ( s.name = 'John Doe') and r.date > "5/5/09" and rbid = b.bid;

Added "s.name"

maybe that is where the syntax error comes from?
The only useful thing you have suggested in here was already suggested in my post (see above) but you did not read it or, more appropriately, did not want to read it, otherwise how would have your product been advertised then, isn't it ?
Get up every morning and take a look at the Forbes' list of richest people. If your name doesn't appear.... GET TO WORK !!!
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2009
Posts: 3
Reputation: PoulH is an unknown quantity at this point 
Solved Threads: 0
PoulH PoulH is offline Offline
Newbie Poster

Re: Compare 2 tables for a equivlent set of values

 
0
  #5
May 26th, 2009
Yes, but I also wanted to assist you and others. Of cause I have a natural interest in this subject related to my product.
l like to add my 50 cents to any where I feel I can contribute and suggesting there is a product that can do something easier and faster is not hurting anyone, it is even free in its basic version which is sufficient for the task. But I understand there is a thin line and you think I crossed it, maybe I agree, I'm a bit in doubt. However, I apologize.
And I did read the thread through but it was a bit unclear as to where the error was exactly, so I just wanted to make it very clear, sorry.
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 17
Reputation: fusi0n423 is an unknown quantity at this point 
Solved Threads: 0
fusi0n423 fusi0n423 is offline Offline
Newbie Poster

Re: Compare 2 tables for a equivlent set of values

 
0
  #6
May 26th, 2009
.
Last edited by fusi0n423; May 26th, 2009 at 9:12 am. Reason: double post
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 17
Reputation: fusi0n423 is an unknown quantity at this point 
Solved Threads: 0
fusi0n423 fusi0n423 is offline Offline
Newbie Poster

Re: Compare 2 tables for a equivlent set of values

 
0
  #7
May 26th, 2009
I don't know how I missed that...but I'm still getting an error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from john_doe_rentals)' The john_doe_rentals should look like:
+-----+
| bid |
+-----+
| 2   |
| 5   |
+-----+
and the sailors_rentals should be:
+----------------------+-----+
| name                 | bid |
+----------------------+-----+
| Captian KirK         | 2   |
| Jane Doe             | 2   |
| Johnny Rocket        | 2   |
| Jane Doe             | 5   |
+----------------------+-----+

My goal is to use whats in john_doe_rentals to get only those names that have rented every boat he has from sailors_rentals. So with this small dataset it would be:
+----------------------+
| name                 |
+----------------------+
| Jane Doe             | 
+----------------------+
because she is the only one who has rented every boat John Doe has.

I'm really avoiding any other tools, I'd like to understand what exactly is going on and be able to write the SQL statements myself. I have to think this is probably a good time to use a join query, but I can't figure it out. The other thing I thought of would be to concat every bid in john_doe_rentals with a space, and do the same thing in sailor_rentals. to get something like:
+-----+
| bids |
+------+
| 2  5 |
+------+
+----------------------+------+
| name                 | bids  |
+----------------------+------+
| Captian KirK         | 2    |
| Jane Doe             | 2  5 |
| Johnny Rocket        | 2    |
+----------------------+------+
but then it comes down to making sure things are sorted correctly so I don't get '5 2' and I see it getting ugly fast. There's gotta be simple query to do what I'm try to achieve.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MySQL Forum
Thread Tools Search this Thread



agplv3 alfresco api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm database design developer development distinct drupal dui ec2 email enterprise eudora facebook form foss gnu government gpl greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier join journalism keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource oracle penelope php query referencedesign reorderingcolumns resultset saas select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency virtualization
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC