How to get the equivalent using join

Question

I have two tables :

fields_col

---------------------
| id |   type_field |
---------------------
| 1  |    login     |
| 2  |    password  |
| 3  |    login     |
| 4  |    password  |

value_field

----------------------------------
| id | typefieldid | value_field |
----------------------------------
| 1  |      1      |     joe     |
| 2  |      1      |     doe     |
| 3  |      4      |     car     |
| 4  |      3      |     dom     |
| 5  |      2      |     he6     |
| 6  |      2      |     abc     |
| 7  |      3      |     iph     |
| 8  |      1      |     nan     |
| 9  |      4      |     no9     |

I expect to get all values where the type is login. Here is the expected output :

joe
doe
dom
iph
nan

I made up the following query :

SELECT value_field 
FROM value_field
WHERE typefieldid IN (SELECT id FROM fields_col WHERE type_field = "login")

And it output me the correct value.

I wonder how to use SQL join feature in this case.

http://sqlfiddle.com/#!9/801bc/2/0

is your `fields_col` correct? It looks like you have duplication for the fields `login` and `password` — oktapodia, Sep 30 '19 at 13:47

score 2 · Answer 1 · answered Sep 30 '19 at 13:46

2

SELECT value_field 
FROM value_field v

INNER JOIN fields_col f
  ON f.id = v.typefieldid
  AND f.type_field='login'

answered Sep 30 '19 at 13:46

dfundako

8,022
3
18
34

Madhur Bhaiya · Accepted Answer · 2019-09-30T13:53:30.460

2

This is how it is done with JOIN; Use ON clause to connect the tables with their appropriate relationships and add another condition such that type_field = 'login'.

SELECT vf.value_field 
FROM value_field AS vf 
JOIN fields_col AS fc ON fc.id = vf.typefieldid 
                         AND fc.type_field = 'login'

SQL Fiddle

Additional Tip: In case of multi-table queries, it is a good practice to use Aliasing, for code disambiguation and readability.

Also, your current schema (table structure) is basically EAV (Entity Attribute Value) Pattern. It is generally a SQL Anti-pattern, and should be avoided. Check this: Entity Attribute Value Database vs. strict Relational Model Ecommerce

edited Sep 30 '19 at 13:53

answered Sep 30 '19 at 13:46

Madhur Bhaiya

28,155
10
49
57

Why some answer gives some INNER JOIN or LEFT JOIN ? – executable Sep 30 '19 at 13:49
1

@executable `LEFT JOIN` is needed when you want all the rows from the left-side table of the JOIN clause, irrespective of a matching row is found or not. – Madhur Bhaiya Sep 30 '19 at 13:50
1

A quick research on what an inner join and left join would tell you; it's up to you to decide which one you need. To add to @MadhurBhaiya's comment, an inner join would require the result row to exist in both tables. – Tiny Haitian Sep 30 '19 at 13:50
Thanks for the answer and I also see `INNER JOIN` and `JOIN` alone – executable Sep 30 '19 at 13:51
2

@executable `INNER JOIN` is same as `JOIN`. Similarly `LEFT JOIN` is same as `LEFT OUTER JOIN`. – Madhur Bhaiya Sep 30 '19 at 13:51

How to get the equivalent using join

2 Answers2