-1

I have a database with 3 tables records, categories, relational. 

records (id, lat, lng)
categories (c_id, c_value)
relational (r_id, c_id)

records
id  | lat      | lng
----------------------
1    23.57258   -35.28412
2    23.54855   -35.18881
3    23.74128   -35.17469

categories
c_id | c_value
---------------
100    groceries
101    bags
102    drinks

relational
id  |  c_id
------------
1     100
1     102
2     101
3     100

The relational.r_id = records.id and the relational.c_id = categories.c_id I want to take pairs from records with different c_value, so I want to make a self join in records and inner join in categories and relational. I've made this without the self join in records

SELECT id, lat, lng, c_value 
FROM records 
JOIN relational 
  ON records.id = relational.id 
JOIN categories 
  ON relational.c_id = categories.c_id 
WHERE c_value = "V1"

I tried something like this but it didn't work. I have problem with the R1.c_value.

SELECT R1._id, R1.lat, R1.lng, R1.c_value, R2._id, R2.lat, R2.lng, 
       R2.c_value 
FROM records R1, records R2 
JOIN relational 
  ON records.id = relational.id 
JOIN categories 
  ON relational.c_id = categories.c_id 
WHERE R1.c_value = "groceries" AND R2.c_value = "bags"

Do you know how can I combine those 3 joins in order to take 2 rows from records with the criteria of the other tables? I want to have an output like this: For "groceries" and "bags" as c.value

1 | 23.57258  |  -35.28412  |  groceries | 2  | 23.54855  |  -35.18881  |  bags
arek
  • 1
  • 6
  • 1
    It's generally a bad idea to mix "comma join" and "join" syntax in the same query....it's generally a bad idea to USE "comma join" notation at all. – Uueerdo May 30 '17 at 22:00
  • I find this question quite difficult to understand. Can you please include some sample data and the result you're trying to get for it? – Mureinik May 30 '17 at 22:01

2 Answers2

0

You can self join records table along with categories and relational like this:

SELECT r1.id, r1.lat, r1.lng, r2.id, r2.lat, r2.lng, c.c_value
FROM records r1 JOIN relational rl ON r1.id = rl.r_id
JOIN records r2 ON r2.id = rl.r_id
JOIN categories c ON rl.c_id = c.c_id
WHERE r1.id <> r2.id;
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • This looks good for 1 c_value. Can you help me with the query if I want the c_value to be different in r1 and different in r2? – arek May 30 '17 at 22:33
  • In that case, you will have to join it twice on `categories` table and provide different values. – Darshan Mehta May 30 '17 at 23:06
  • Do you mean to put c1.c_value, c2.c_value in select and then join categories c1 on rl.c_id= c1.c_id join categories c2 on rl.c_id= c2.c_id where c1.c_value = "V1" AND c2.c_value = "V2". I tried this but it returns empty result. :/ – arek May 30 '17 at 23:17
  • I'm sorry but can you explain me more how to do it? Because I can't figure it out. – arek May 31 '17 at 10:14
  • We have `c.c_value = 'V1';` in the query so it can't be different. Could you add expected output in the question? – Darshan Mehta May 31 '17 at 10:27
  • @arek updated the answer and removed the condition, try now – Darshan Mehta May 31 '17 at 11:03
  • But I want the condition with 2 c_value because I want to search every time something different. I want to find from the table records all the rows that have c_value = "groceries" and all of them that have c_value = "bags". And I need them to the same query with joins – arek May 31 '17 at 11:18
0

I finally found exactly what I wanted. My query should be like this. Thanks for the help and the idea anyway.

SELECT r1.id, r1.lat, r1.lng, c1.c_value, r2.id, r2.lat, r2.lng, c2.c_value
FROM records r1 JOIN relational rl1 ON rl1.id = r1.id
JOIN categories c1 ON rl1.c_id = c1.c_id
JOIN records r2 JOIN relational rl2 ON rl2.id = r2.id
JOIN categories c2 ON rl2.c_id = c2.c_id
WHERE  c1.c_value = "groceries" AND c2.c_value = "bags"
arek
  • 1
  • 6