make join return empty result set

[guillermobytes]

hi,

i was wondering which type of join should i use to get an empty result set when there is no record in one of the tables

i.e : i have two tables : Element and Attribute
the table Element has elements in it...

[B]Element[/B]
id | name
---|---------------
0  | element0
1  | element1
2  | element2
3  | element3
4  | element4

then there is the attributes table, each attribute is related to an element _but_ not every element has attributes

[B]Element[/B]
elementId | attribute
----------|-----
2         | attributeForElement2
4         | attributeForElement4

so now i want to know if element with name : "element3" has an attribute.

for that i do a join:

SELECT *
FROM Element AS e 
    [B]INNER[/B] JOIN Attribute AS a
WHERE e.name = 'element3'

is that INNER JOIN going to return an empty resultset?

ah I think found the answer i just have to specify the column i want to get

SELECT a.attribute
FROM Element AS e 
    [B]INNER[/B] JOIN Attribute AS a
WHERE e.name = 'element3'

as there is no attribute it should return empty set, right?

edit post:

sorry, now i rethink my problem and what i really want is to be able to differentiate 3 cases in one single query:

1. there is no such element
2. there is such element but it has no attribute
3. there is such element and has an attribute

so my query should return for each case:
1. empty set
2. row with element but without attribute
3. row with element and attribute

SELECT a.attribute, e.element
FROM Element AS e 
    [B]INNER[/B] JOIN Attribute AS a
          ON (e.id = a.elementId)
WHERE e.name = 'element3'

would this query make the trick?

[Atli]

Hey.

Yes, you would use an INNER JOIN for that. - However, for this to work as expected, you need to use the ON clause of the join. Otherwise the tables are joined on every possible combination of every row of both tables. (All attributes will be linked to all elements.)

Typically, you would do something like:
[code=mysql]SELECT *
FROM Element AS e
INNER JOIN Attribute AS a
ON e.id = a.elment_id_fk
WHERE e.name = 'element3' [/code]
The "a.element_id_f k" field being a FOREIGN KEY that links the attribute to it's element.

[guillermobytes]

sorry my bad i forgot to put the ON clause,
hey i edited my post and rethought my problem, can have a quick look at it again?
thanks

[Atli]

Ok, not problem.

In that case, you could use a LEFT JOIN. As opposed to a INNER JOIN, it always returns all matching rows from the "parent" (or "right") table, even if there are no matching rows for it in the "child" ("left") table.

So using a LEFT JOIN, if there are no elements matching your query you get an empty set. - If there is an element without any attributes, you get a row for the element, but with a NULL for the attribute fields. - If it has attributes, they are returned joined just as if you had done a INNER JOIN.

[guillermobytes]

many thanks! i'll give it a try