List:General Discussion« Previous MessageNext Message »
From:Rory McKinley Date:November 20 2003 2:24pm
Subject:LEFT JOIN problem
View as plain text  
Hi List

I am currently running a query that reads records from one table (Table 1) links these
records to an id value in another table (Table 2)  and returns the result. The tables are
as follows, with sample data:

Table 1in DB 1: 

line_number | category_name | category_value | line_type
1                  | Rent                 |              100.00|  13
2                  | Usage              |                50.00|  13
3                  | Services           |                75.00|  13 

Table 2 in DB 2:

parameter_ID | parameter_trigger | parameter_value
1                   |                           1| Rent
1                   |                           2| Usage

The returned records will be line_number, category_value from Table 1 and
Parameter_Trigger from Table 2 (Table 1 and Table 2 are joined on table 1.Category Name =
Table 2.Parameter_Value). As you can see from the sample the third record in table 1 does
not have a matching entry in Table 2. In this case, I want the returned record to still
show the line_number and category_value, except in place of the parameter_trigger the
field should be set to zero (so that I can see that I have a category name that I haven't
accounted for). So I am using the following query:

SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0)
FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON a.category_name = b.parameter_value
WHERE a.line_type = 13 AND b.parameter_ID = 13

The only problem is that the query only returns the first two records and not the third
record - contrary to my expectations. I am using MySQL 4.0.15-standard together with
PHPMyAdmin 2.5.3. 

Does anybody know what I am doing wrong?



Rory McKinley
Nebula Solutions
+27 82 857 2391
rorym@stripped
"There are 10 kinds of people in this world, 
those who understand binary and those who don't" (Unknown)
Thread
LEFT JOIN problemRory McKinley20 Nov
  • Re: LEFT JOIN problemRoger Baklund20 Nov
    • [SOLVED]Re: LEFT JOIN problemNobody20 Nov
    • Re: [SOLVED]Re: LEFT JOIN problemMartijn Tonies20 Nov
      • Re: [SOLVED]Re: LEFT JOIN problemRoger Baklund20 Nov
        • Re: [SOLVED]Re: LEFT JOIN problemMartijn Tonies20 Nov
          • Re: [SOLVED]Re: LEFT JOIN problemRoger Baklund20 Nov
        • Re: [SOLVED]Re: LEFT JOIN problemMartijn Tonies20 Nov