0

here is my situation:

I have 3 tables:

A: (A_id, Name)

B: (B_id, A_id, Name)

C: (C_id, B_id, State)

What i want is to have the following resultset:

A.A_id,A.Name, C.State

the complicator is that i need State to have a default value when there is no B data to link. In that case, i want 

A.A_id, A.Name, 'Default_Value'

I dont know much of advanced Sql, so any pointers are greatly appreciated.

sergio
  • 1,026
  • 2
  • 19
  • 43

3 Answers3

3
select
coalesce(c.State, 'default value')
from
a
left join b on a.id = b.A_id
left join c on b.B_id = c.B_id
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Retracted my +1 because of the link to that horrible explanation. – ypercubeᵀᴹ Feb 13 '14 at 15:51
  • What's so horrible about it? Cause it's from codinghorror.com? Or did you rather want to write that to the w3schools link in the other answer? (that's why I posted it). See w3fools.com – fancyPants Feb 13 '14 at 15:52
  • It's a good (very good) visual explanation of `UNION` and `EXCEPT`. Not of joins. – ypercubeᵀᴹ Feb 13 '14 at 15:53
  • May you please explain? I see no mention of UNION or EXCEPT in there. Are you sure we're talking about the same link? – fancyPants Feb 13 '14 at 15:54
  • @fancyPants Yes, I originally added that so that my answer had at least some information on joins while I wrote out an explanation of the query that I had written. Not a big deal in this case, I thought, but I'll keep that in mind next time. Fair enough. – Manny Feb 13 '14 at 16:00
  • See the comments (by Matt on October 13, 2007 11:48 AM): *"Joins are indeed analogous to set operations, but it is only an analogy. Understand what's really going on, or you will get burned when you actually have to use the things."* – ypercubeᵀᴹ Feb 13 '14 at 16:01
  • And the answer by the author: *" The commenters pointing out that the diagrams break down in case of multiple and or duplicate results, are absolutely right. I was actually thinking of joins along the primary key, which tends to be unique by definition, although the examples are not expressed that way. Like the cartesian or cross product, anything that results in more rows than you originally started with does absolutely breaks the whole venn diagram concept. So keep that in mind. Jeff Atwood on October 12, 2007 8:11 AM"* – ypercubeᵀᴹ Feb 13 '14 at 16:01
  • Okay, maybe we can agree on that it's a very good explanation for starters. The "breaking the venn diagramm" thing, not a big deal in my eyes. – fancyPants Feb 13 '14 at 16:10
2
SELECT A.A_id, A.Name, COASLESCE(C.State, 'Default_Value')
FROM 
    A LEFT JOIN 
        (B INNER JOIN C ON C.B_id = B.B_id)
    ON B.A_id = A.A_id

Some information on joins: What is the difference between "INNER JOIN" and "OUTER JOIN"?

What's happening here is that we are joining table B and C with an INNER JOIN where the respective B_id column is equal. The INNER specifies that results will be returned only when records exist in both tables that match the C.B_id = B.B_id condition.

The LEFT JOIN will join those combined values to table A if the matching condition exists, while still returning the records from table A if no match exists. That is, if nothing exists for the condition B.A_id = A.A_id, NULL values are returned for the columns from the right side of the join (the B and C join). We perform the COASLESCE, so that if the queried column returns with NULL, it can default to some specified value.

COALESCE has some added benefits when performing this function: http://msdn.microsoft.com/en-us/library/ms190349.aspx

One last thing, table B in your example is commonly known as a junction table (or join table, or bridge table)... http://en.wikipedia.org/wiki/Junction_table

Community
  • 1
  • 1
Manny
  • 967
  • 1
  • 6
  • 17
2

You could use ISNULL in the select

SELECT A.A_id,A.Name, ISNULL (C.State, 'Default_Value')
from A
left join b...
left join c...
Yumei De Armas
  • 386
  • 2
  • 6