943,955 Members | Top Members by Rank

Web Development > Databases > MySQL > Compare 2 tables for a equivlent set of values
Ad:
  • MySQL Discussion Thread
  • Unsolved
  • Views: 736
  • MySQL RSS
Permalink
May 26th, 2009
0

Compare 2 tables for a equivlent set of values

Expand Post »
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
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
fusi0n423 is offline Offline
18 posts
since Apr 2008
Permalink
May 26th, 2009
0

Re: Compare 2 tables for a equivlent set of values

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.
Reputation Points: 485
Solved Threads: 89
Posting Shark
verruckt24 is offline Offline
944 posts
since Nov 2008
Permalink
May 26th, 2009
0

Re: Compare 2 tables for a equivlent set of values

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?
Reputation Points: 10
Solved Threads: 0
Newbie Poster
PoulH is offline Offline
3 posts
since May 2009
Permalink
May 26th, 2009
0

Re: Compare 2 tables for a equivlent set of values

Click to Expand / Collapse  Quote originally posted by PoulH ...
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 ?
Reputation Points: 485
Solved Threads: 89
Posting Shark
verruckt24 is offline Offline
944 posts
since Nov 2008
Permalink
May 26th, 2009
0

Re: Compare 2 tables for a equivlent set of values

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.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
PoulH is offline Offline
3 posts
since May 2009
Permalink
May 26th, 2009
0

Re: Compare 2 tables for a equivlent set of values

.
Last edited by fusi0n423; May 26th, 2009 at 9:12 am. Reason: double post
Reputation Points: 10
Solved Threads: 0
Newbie Poster
fusi0n423 is offline Offline
18 posts
since Apr 2008
Permalink
May 26th, 2009
0

Re: Compare 2 tables for a equivlent set of values

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.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
fusi0n423 is offline Offline
18 posts
since Apr 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MySQL Forum Timeline: Storing Binary Data in MySQL
Next Thread in MySQL Forum Timeline: hi im new i have problems with mysql databases





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC