CODEWITHSUNDEEP
mysqlsqljoinnon-relational-database
Khamad Ali
Khamad Ali

Reputation: 31

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 |
+--------+-------------+-------------+-------+-----------------+

Upvotes: -1

Views: 801

Answers (2)

Thomas
Thomas

Reputation: 1100

When I understand you correct:

select * from bengkel_similarity , rating where idBengkel = idBengkel_2

Upvotes: -1

GMB
GMB

Reputation: 222642

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)

Upvotes: 3

Related Questions