MySQL statement, JOIN

Question

I have these tables :

enter image description here

I don't know how I can write a statement, that takes emails from Table "Firm", that have Location_id = '1' and Category_id = '130'; I know that I should use JOINs, but I'm not sure how to go from there.

you need to try something before posting questions, that's one of the easiest joins you might face in your life — mamdouh alramadan, Feb 17 '14 at 15:28
I tryed something SELECT Firm.email FROM Firm INNER JOIN FirmID ON FirmID.location_id = '1' AND FirmID.category_id = '130'; — Vladson, Feb 17 '14 at 15:29

score 1 · Accepted Answer · edited Feb 17 '14 at 15:33

1

SELECT Firm.email 
FROM Firm 
INNER JOIN FirmID ON Firm.firma_id = FirmID.firma_id 
WHERE FirmID.location_id = '1' 
AND FirmID.Category_id = '130'

edited Feb 17 '14 at 15:33

Linger

14,942
23
52
79

answered Feb 17 '14 at 15:29

CodeBird

3,883
2
20
35

Jonast92 · Answer 2 · 2014-02-17T15:34:43.580

0

You could do:

SELECT f.email 
FROM Firm f 
WHERE f.firma_id = 
(
    SELECT ff.firma_id 
    FROM FirmID ff 
    WHERE ff.location_id = 1 
    AND ff.category_id = 130
)

Using an inner select.

But using JOINS is in the long term the way to go, what have you tried and what's not working?

edited Feb 17 '14 at 15:34

answered Feb 17 '14 at 15:29

Jonast92

4,964
1
18
32

score 0 · Answer 3 · answered Feb 17 '14 at 15:30

0

Should be as simple as doing the following:

SELECT email
FROM Firm, FirmID
WHERE Firm.firma_id = FirmID.firma_id
AND FirmID.location_id = 1
AND FirmID.category_id = 130;

It does a join behind the scenes, but can be a bit clearer to understand than using the JOIN keyword.

answered Feb 17 '14 at 15:30

Serenthia

1,222
4
22
40

I really wouldn't advise to use that syntax instead of the join one. – Raphaël Althaus Feb 17 '14 at 15:37
Could you explain why? My understanding was that it was no less efficient than explicitly using the JOIN keyword, as SQL still does a join. Thanks – Serenthia Feb 17 '14 at 16:02
for example : http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause – Raphaël Althaus Feb 17 '14 at 16:31
Thanks for clarifying your point. However, I think the outcome of that particular question is that the two are synonyms - the advantages of either option (in a case as simple as this) are stylistic. I was just trying to help the OP understand what a join meant :-) – Serenthia Feb 17 '14 at 17:52

MySQL statement, JOIN

3 Answers3