mysqlsqljoinnon-relational-database

Mysql - How to select record from table which has no relation


Is it possible to get record from non-relation table?
In this case, I have two records on table rating.

I want to get data from idBengkel on table rating then add them into column idBengkel_2 on table bengkel_similarity.

Note : Column idUser resulting how many records. So, number of record must match on all table. And idUser is not same with idBengkel_1.


Here is my tables :

bengkel_similarity

SELECT idBengkel_1, idBengkel_2, nilaiSimilarity
FROM bengkel_similarity
WHERE idBengkel_1 = 1 AND idBengkel_2 = 7
OR idBengkel_1 = 1 AND idBengkel_2 = 10

rating

SELECT idUser, idBengkel, nilai
FROM rating
WHERE idUser = 1

Here the result from all table :

Result of bengkel_similarity

+-------------+-------------+-----------------+
| idBengkel_1 | idBengkel_2 | nilaiSimilarity |
+-------------+-------------+-----------------+
|           1 |           7 | -0.1692         |
|           1 |          10 | 0               |
+-------------+-------------+-----------------+

Result of rating

+--------+-----------+-------+
| idUser | idBengkel | nilai |
+--------+-----------+-------+
|      1 |         7 |     4 |
|      1 |        10 |     5 |
+--------+-----------+-------+

This is what I want :

+--------+-------------+-------------+-------+-----------------+
| idUser | idBengkel_1 | idBengkel_2 | nilai | nilaiSimilarity |
+--------+-------------+-------------+-------+-----------------+
|      1 |           1 |            7|      4|         -0.1692 |
|      1 |           1 |           10|      5|               0 |
+--------+-------------+-------------+-------+-----------------+

Solution

  • I don't know why you would qualify this as "non-relational". From your data and desired results, I think you just want a join:

    SELECT 
        bs.idBengkel_1, 
        bs.idBengkel_2, 
        r.nilai
        bs.nilaiSimilarity,
    FROM bengkel_similarity bs
    INNER JOIN rating r 
        ON  r.idUser    = bs.idBengkel_1
        AND r.idBengkel = bs.idBengkel_2
    WHERE bs.idBengkel_1 = 1 AND bs.idBengkel_2 IN (7, 10)