MySQL Query Help Needed Please

[rogue_php]

I can't seem to figure this one out... been working on it most of the day. Can anyone please take a look?

Thanks
Charles

My table structure is like so:

tbl_1

id area_1 area_2
1 2 3
2 1 2
3 5 0

tbl_2

id area_name
1 funding
2 research
3 new
4 ongoing
5 other

So, I need to display all the records in tbl_1 and show the values for the fields area_1 and area_2 as their area_name field from tbl_2. I must display each record from tbl_1 only once. I know I am close, but I just can't get this to work. The closest I have gotten is this:

SELECT DISTINCT a.area_name area_a, b.area_name area_b FROM tbl_1 x, tbl_2 a, tbl_2 b
WHERE x.area_1 = a.id
OR x.area_2 = b.id GROUP BY x.id

But this ALWAYS returns the area_name (funding) in the value of area_b (have no idea why)

[tomhath]

First, you don't need a GROUP BY unless you're using row aggregate functions (like SUM or COUNT). The DISTINCT will give you the unique values you want.

Second, change the OR to an AND. I didn't test it but this seems like it should work:

SELECT DISTINCT 
  a.area_name area_a, 
  b.area_name area_b 
FROM tbl_1 x, tbl_2 a, tbl_2 b
WHERE x.area_1 = a.id 
  AND x.area_2 = b.id