Coldfusion/MySQL Query Help

Hi everyone, I'm having a bit of trouble coming up with the right approach to a problem. I think it can be solved using a query, but I may have to resource to some custom functions, I don't know.

First, I'm using MySQL. Let's say I have two tables:
Table 1
A
B
C
D

Table 2
A | 1
A | 3
B | 2
C | 1

I want to first get all the values out of table one and then somehow flag the values in table one that are also included in table 2 where the second column in table 2 is equal to "1". The resulting table migh look something like this:
Table 3
A | Flag
B
C | Flag
D

Any ideas about the best way to handle this using MySql?

You probably want to ask this in the MySQL forum.

Thanks.

I thought about that, but I'm not sure this is a MySQL solution. What I really think has to happen is that I need to have two queries, one for each table. Then I need to convert one to a struct and loop through the other to add a flag to the struct. I'm not sure what the right approach is.

No, this doesn't have anything do do with CF. It sounds like you want to join the tables together, this is standard SQL. Something like:

select t1.theAorBcolumn
from table1 t1,
table2 t2
where t1.theAorBcolumn = t2.theAorBcolumn
and t2.the1or2column = 1

Actually, this is a little more than that. If I used your method, I would only get records between the two tables where the value = 1. That's not what I want. I want all the records in table 1 and then I want to flag the records in table 1 where a) the record exists in table 2 AND b) field 2 in table two is equal to 1. A simple left join doesn't work either. Here's an example:

Select * from Table1
Left Join Table2
ON Table1.Col1 = Table2.Col1

The results would look like the following:
Table1.col1 | Tbl2.col1 | Tbl2.col2
A | A | 1
A | A | 3
B | B |2
C | C | 1
D |D | Null

An inner join would do the same, but it wouldn't include D because there is no D value linked in Table 2.

Again, that's not what I want. I want something that tells me that in table 2, A and C are linked with value "1" and I don't care about everything else:
A | Yes
B | No
C | Yes
D | No

No duplicates and the data is filtered or flagged based on a data value I'm looking for. If there's a standard SQL query for this, I'm happy to try it.

You want an OUTER JOIN as you're showing in this example. You should just be able to add additional WHERE clause elements to limit which ones have 1 in the second table. Or it could get more complex and you'll need an inline view and/or conditional logic like case statements in the SELECT clause. Any way you do it, it's definitely a SQL issue, so like I said you may want to post in the MySQL forum which is specifically for this sort of thing.