left outer join query

[alena1347]

I have a query by

 

$ques=mysql_query("SELECT field.field FROM field LEFT OUTER JOIN skill_freelancer ON skill_freelancer.sname=field.field WHERE skill_freelancer.id=$sr");

 

here $sr is a id 1

 

my table are

 

field skill_freelancer

------------------- --------------------------

id | field id | sname

1 | html 1 | html

2 | php 1 | php

3 | c

 

 

The output is html,php but i need only "c" could anyone help in the query

Edited February 13, 2013 by alena1347

[Barand]

skill_freelancer is "LEFT JOINED" so you can't put conditions in the WHERE clause, they need to be part of the JOIN condition

 

SELECT field.field FROM field
LEFT OUTER JOIN skill_freelancer
ON skill_freelancer.sname=field.field AND skill_freelancer.id=$s

Edited February 13, 2013 by Barand

[alena1347]

I have a query by

 

$ques=mysql_query("SELECT field.field FROM field LEFT OUTER JOIN skill_freelancer ON skill_freelancer.sname=field.field WHERE skill_freelancer.id=$sr");

 

here $sr is a id 1

 

my table are

 

field

-------------------

id | field

1 | html

2 | php

3 | c

 

 

skill_freelancer

--------------------------

id | sname

1 | html

1 | php

 

 

The output is html,php but i need only "c" could anyone help in the query

 

OK barand but could you show me a way to get only the skill c

[Barand]

SELECT field.field FROM field
LEFT OUTER JOIN skill_freelancer
ON skill_freelancer.sname=field.field AND skill_freelancer.id=$s
WHERE skill_freelancer.sname IS NULL

[alena1347]

SELECT field.field FROM field
LEFT OUTER JOIN skill_freelancer
ON skill_freelancer.sname=field.field AND skill_freelancer.id=$sr
WHERE skill_freelancer.sname IS NULL

 

Thank you for this query it solved the problem just replace the $s with $sr , could you please explain this query in words

Edited February 13, 2013 by alena1347

[Barand]

First a bit of basic join theory.

 

SELECT *
FROM A
INNER JOIN B ON A.key = B.key

 

The INNER JOIN means get only data from records matching on key in both files

 

SELECT *
FROM A
LEFT JOIN B ON A.key = B.key

 

LEFT JOIN above means get all records from A with the matching data from B if it exists. Where there is no matching record in B, the fields from B contain NULL.

 

So with your query, limiting the selection to freelancer 1

SELECT field.field, skill_freelancer.id, skill_freelancer.sname
FROM field
LEFT OUTER JOIN skill_freelancer
ON skill_freelancer.sname=field.field AND skill_freelancer.id=1

 

we get

+-------+------+-------+
| field | id   | sname |
+-------+------+-------+
| html  |    1 | html  |
| php   |    1 | php   |
| c	 | NULL | NULL  |
+-------+------+-------+

 

You want listed the fields that are not in skill_freelancer for id 1 - ie where sname is NULL, so we add "WHERE sname IS NULL" to show only those results.