Joining tables together in Java

Gummy Panda

Hi

I have created a Java application which using a simple socket gets vectors from a database source.

The problem is that when the "Get Movies" button is click it should send the vectors from both Tables in the database source.

I thought using "SELECT * FROM Cassette_Table, DVD_Table" would work but it only gets the results from the Cassette Table.

Here is the code for both the client and server

MovieClient MovieCollectionServer

Talliesin

SELECT * FROM Cassette_Table, DVD_Table

should give you a cartesian product join of the tables. E.g. if Cassette_Table had the following records:

ID Name
1 CA
2 CB

And DVD_Table had the following records:

ID Name
11 DA
12 DB

Then the results would be:
ID Name ID Name
1 CA 11 DA
1 CA 12 DB
2 CB 11 DA
2 CB 12 DB

If I understand correctly what you want to do is to have the results of the two tables added together, which is not what is called a "Join" in SQL, rather it's a Union:

SELECT * FROM Cassette_Table
UNION
SELECT * DVD_Table

It's generally a good idea to be have a specific column list with this (actually it's advisable anyway). By default "UNION" is short for "UNION ALL", "UNION DISTINCT" won't add entries from the second table if they are duplicates of entries from the second table. This is less efficient but sometimes important.

None of this has anything to do with Java.

Gummy Panda

Thanks for the help but that just seems to crash the application.

I think i need to add the vectors together before it sends it back client

Zab

Are the columns in Cassette_Table the same as the columns in DVD_Table? If not, this is probably why an exception is being thrown. You'll need to put in a column list so that the two result sets have the same column types. I suggest that you try to get the query working before you do anything else. You'll need to use a UNION, as mentioned, or run two queries and combine the results.

Also, you know you can use RMI instead of passing messages through sockets? This would handle the serialization for you. And if you are going to use messages, you should have them as constants ( such as 'public static final String MOVIES = "MOVIES"; ' this is just good practice ). It also looks like if the SQL statement excepts, the client is left waiting for data that will never arrive, so you should send it a error condition.

Zab