selecting on two tables in Cassandra

Question

I use Cassandra for a project, and it's my first project. , and I'm trying to do a simple request on two tables, but that doesn't work...

I want to do something like:

Select * from table1, table2 where table1.test = test and table2.test2 = 123;

Is it possible to request on two tables in Cassandra? And how can I do that?

Thanks

please explain more about your question! do you want to select from tables in one request? do you need something like `SELECT * from table1 and table2` ? or `SELECT * from table1` , `SELECT * from table2`? can you write your purposed request? — Zahra Aminolroaya, Nov 29 '15 at 11:31
well I think you want to join tables! you should read this: http://stackoverflow.com/questions/17248232/how-to-do-a-join-queries-with-2-or-more-tables-in-cassandra-cql — Zahra Aminolroaya, Nov 29 '15 at 12:41

score 3 · Accepted Answer · answered Nov 29 '15 at 14:36

I'm trying to do a simple request on two tables

What you're trying to do is known as a "distributed join" and Cassandra is specifically designed to prevent you from doing this.

The way to solve these types of problems, is with a process called denormalization. Let's say you have simple two tables carMake and carModel:

 makeid | make
--------+--------
      1 |  Chevy
      2 |  Dodge
      3 |   Ford

 modelid | makeid | model
---------+--------+---------
      15 |      3 |   Focus
      11 |      3 | Mustang
      32 |      2 | Charger
      82 |      3 |  Fusion

Now, in a traditional RDBMS if I wanted to SELECT all car models with a make of "Ford" I would execute a JOIN query. But with Cassandra, the idea is to solve this problem at the modeling stage, by building a table which supports the ability to query make and model of a car at the same time:

CREATE TABLE carMakeModel (
    carid int,
    make text,
    model text,
    PRIMARY KEY (make,carid));

aploetz@cqlsh:stackoverflow> SELECT * FROM carMakeModel WHERE make='Ford';

 make | carid | model
------+-------+---------
 Ford |     1 | Mustang
 Ford |     2 |   Focus
 Ford |     3 |  Fusion

(3 rows)

Some key points to note here:

make is duplicated as much as is necessary. You'll notice that "Ford" is specified 3 times in the result set. If you had data for 13 models of Fords, you would store the value of "Ford" 13 times.
PRIMARY KEYs in Cassandra are unique. I have carid added as a part of the PRIMARY KEY to ensure uniqueness for each model, otherwise an INSERT for each make would overwrite itself.

So for my project i can have a collection and this collections can have products, and this product have attributs but in one collection for example, the product will have all the attribut visible, and in other collection the product will have only two attributs visible. But what table i've to do? For now ive a table collection, collection_contains, collection_attributs, products and products_contains, but i do this like a traditionnal RDBMS... — Kreepz, Nov 30 '15 at 08:10

selecting on two tables in Cassandra

1 Answers1

Linked