CODEWITHSUNDEEP
mysqlsql
executable
executable

Reputation: 3600

How to get the equivalent using join

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

Upvotes: 0

Views: 41

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28854

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

Upvotes: 2

dfundako
dfundako

Reputation: 8324

SELECT value_field 
FROM value_field v

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

Upvotes: 2

Related Questions