MySQL Join Problem

I am having a problem with MySQL joins.

Table_A:

 A_id  Cost1  A1_id  Cost2
 1     500     0     200
 1     100     1     100
 1     50      2      60
 1     10      3      50
 2      5      0      10

Table_B (Refers B_id: from Table_A A1_id):

 B_id  FName  LName
 1     X      A
 2     Y      B
 3     Z      C

Table_C (Refers C_id: from Table_A A_id):

C_id  Towns

 1    Atlanta
 2    NewYork

I need to combine all three tables like the following output:

• I extract the Towns that match (Table_A.A_id=Table_C.C_id).
• I extract the Fname,Lname that match (table_A.A1_id=Table_b.b_id).
• I need to skip the Towns if A1_id !=0.
• I need to skip the Fname,Lname if A1_id ==0.

Remaining data may either be a value or null, which I specify as '#'. What would be an efficient MySQL query for the given scenario?

Output:

A_id  Cost1  A1_id cost2 Fname Lname Towns
1     500     0    200    #     #    Atlanta
1     100     1    100    X     A     #
1      50     2     60    Y     B     #
1      10     3     50    Z     C     #

From stackoverflow

• If I understand correctly, it should be:

  select fname, lname, towns
  from table_a, table_b table_c
  where table_a.a_id = table_c.c_id
    and table_a.a1_id = table_b.b_id
    and table_a.a1_id <> 0;
  

  Wouter van Nifterick : Your query will return 0 rows. To get a table like the example you need left joins.

  melaos : class is in session :)

• If you buy me a beer I'll do your homework assigment.

  I think it should be something like this.

  select A_id, Cost1,  A1_id, cost2, Fname, Lname, Towns
  from Table_A
  left join Table_B on table_A.A1_id = Table_b.b_id
  left join Table_C on Table_A.A_id  = Table_C.C_id
  

  John Rasch : +1 for buying beer suggestion, maybe they could add that to the bounty system?

  alex : Haha, yes, like the wordpress plugin 'buy me a beer'

• This looks like a UNION of two distinct queries to me. I'm going to assume that the ID columns never contain negative values.

  SELECT A.A_id, A.Cost1, A.A1_id, A.Cost2, B.Fname, B.Lname, C.Town
      FROM Table_A AS A
                INNER JOIN Table_B AS B ON A.A1_id = B.B_id
           LEFT OUTER JOIN Table_C AS C ON A.A_id  = C.C_id
      WHERE A.A1_id != 0
        AND C.C_id < 0
  UNION
  SELECT A.A_id, A.Cost1, A.A1_id, A.Cost2, B.Fname, B.Lname, C.Town
      FROM Table_A AS A
           LEFT OUTER JOIN Table_B AS B ON A.A1_id = B.B_id
                INNER JOIN Table_C AS C ON A.A_id  = C.C_id
      WHERE A.A1_id = 0
        AND B.B_id < 0;
  

  I'm not completely confident about that formulation. There's a chance that the conditions on B.B_id < 0 and C.C_id < 0 need to be associated with the corresponding ON clauses.

  There's also a decent chance that using two left outer joins in a single SELECT with appropriate OR'd filters would achieve the correct result.