Compare 2 tables for a equivlent set of values

[fusi0n423]

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

[verruckt24]

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;

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

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;

Here what is result ? Or should it be rentals.

[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?

[verruckt24]

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 ?

[PoulH]

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.

[fusi0n423]

.

[fusi0n423]

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.