Trouble with one to many relationship in single SQL query

[Plater]

(I don't even know how to title this)
The bossman says I need to add a certain field into a query and I have yet to figure out out to do it.
I will try and make a trimmed down example.


Table1's columns:

myUniqueIDField | (other columns)

Table2's columns:

aUniqueID | LinkToTable1myUniqueIDField | StringColumn | (other columns)

So what I'm saying is: Any entry in Table1 can have 0-n number of entries in Table2. Nothing special there.
I need ALL columns from all entries in Table1, regardless of if they are in Table2 (left join, nothing big yet)
The problem comes with the extra column he wants.
The contents of that extra column need to be dependant on what is found in Table2 and based on Table2's [StringColumn].

For example some entries in Table2 code be:

1  234  "Action Needed"
2  367  "Action Taken"
3  234  "Bad Data"

My computed column needs to know if for a given record of Table1, if there are any entries in Table2 that say "Action Needed" or "Action Taken".
The computed column (for all it matters) could have three possible values then:
It could be blank (meaning it found nothing of use in Table2)
It could say "Action Needed" (meaning it found an entry in Table2 that matches)
It could say "Action Taken" (meaning it found an entry in Table2 that matches)

If this were a one - to - one relationship it would be easy enough to be like Table2.StringCo lumn and just display it's contents, but there will be many entries in Table2.

So if anyone made any sense of that and has any suggestions I'm all ears.
(It would even be acceptable to me if the extra column contained a concatinated string of all the StringColumns in Table2 that matched that record, I could post process)

[JovieUrbano]

Am confused with all those lines.. please make a display of your desired output.. :)

[Plater]

Well maybe this sample set will help make things more understandable. It's hard to explain.

Sample Table1 Data:

200 (some data)
201 (some data)
202 (some data)
203 (some data)
204 (some data)
205 (some data)

Sample Table2 Data:

1   200   "SomeValue"
2   200   "SomeOtherValue"
3   200   "Action Required"
4   202   "Acion Taken"
5   202   "SomeValue"
6   203   "SomeValue"
7   204   "Action Required"

Sample Output of the query:

200   (some data)   "Action Required"
201   (some data)   ""
202   (some data)   "Action Taken"
203   (some data)   ""
204   (some data)   "Action Required"
204   (some data)   ""

[ck9663]

Well maybe this sample set will help make things more understandable. It's hard to explain.

Sample Table1 Data:

200 (some data)
201 (some data)
202 (some data)
203 (some data)
204 (some data)
205 (some data)

Sample Table2 Data:

1   200   "SomeValue"
2   200   "SomeOtherValue"
3   200   "Action Required"
4   202   "Acion Taken"
5   202   "SomeValue"
6   203   "SomeValue"
7   204   "Action Required"

Sample Output of the query:

200   (some data)   "Action Required"
201   (some data)   ""
202   (some data)   "Action Taken"
203   (some data)   ""
204   (some data)   "Action Required"
204   (some data)   ""

Llet's see. you need all the rows and columns in table1 regardless if the key exists in table 2. You also need the status (am assuming this column name) in table2 if the key is existing in table2 and the status has the word 'action' on it.

If am wrong, don't continue reading this.

Otherwise, try something like...

select table1.*, table2.someothercolumn, 
from table1 left join table2 on table1.key = table2.key
left join (select key, somecolumn, statusoftask from table2 where statusoftask like '%action%') status on status.key = table1.key

If you have two or more status with the word 'action' on it, it will grab the first one. If there are no status with the word 'action', it'll be NULL. just use IsNull as necessary.

Good luck,

-- CK

[Plater]

That looks like what I am after.
I was not aware I could use a select clause like that in the FROM section.
I will have to investigate it when back at work tomorrow and let you know if it works out. Thanks.

[ck9663]

Llet's see. you need all the rows and columns in table1 regardless if the key exists in table 2. You also need the status (am assuming this column name) in table2 if the key is existing in table2 and the status has the word 'action' on it.

If am wrong, don't continue reading this.

Otherwise, try something like...

select table1.*, table2.someothercolumn, 
from table1 left join table2 on table1.key = table2.key
left join (select key, somecolumn, statusoftask from table2 where statusoftask like '%action%') status on status.key = table1.key

If you have two or more status with the word 'action' on it, it will grab the first one. If there are no status with the word 'action', it'll be NULL. just use IsNull as necessary.

Good luck,

-- CK

Also, try:

select table1.*, table2.someothercolumn, stat.statusoftask 
from table1 
left join table2 on table1.key = table2.key
left join table2 stat on table1.key = stat.key and  statusoftask like '%action%'

If this works, try this one coz I think this is faster, because there's no subquery. You might also want to create index for faster performance.

I also think I might've missed a column. It should've been:

select table1.*, table2.someothercolumn, stat.statusoftask 
from table1 left join table2 on table1.key = table2.key
left join (select key, somecolumn, statusoftask from table2 where statusoftask like '%action%') status on status.key = table1.key

Happy coding

-- CK

[Plater]

Oh yeah, that 2nd one using LIKE works great.
Learning all kinds of new things. Was unaware the JOINs could have more then one "requiremen t" thing in there. Thought it could only have the key=key thing.

Thanks for the help on that. I had been trying like group by and distinct but wasn't getting anywhere.