MySQL Join Problem

Question

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     #

Answer 1

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;

Answer 2

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

Answer 3

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.