PDA

Click to See Complete Forum and Search --> : touble with joins

crippsy
09-20-2006, 04:36 PM
I am having trouble creating a "who had treats", please could someone help. I have two tables Table1 has a "treats1" field with data
sweets
Toys
Clothes
Drinks

the next table, Table2, has fields for "treats2" and "Who" with data :
Sweets, Fred
Toy, Fred
Sweets, John
Drink, John

Now I would like to create a join that shows all the treats and highlight just the ones given to Fred i.e to come with the results

sweets, Fred
Toys, Fred
Clothes, null
Drinks, null

Now if I do a Left Outer join on Table 1 and filter on Fred for "who" on table 2 it only shows Sweets and toys and not the clothes and drinks. and suggestions for the SQL so that all treats are shown and fred is selected against the ones he has chosen???
cridley
09-22-2006, 09:57 AM
select * from Table1 left join Table2 on Table1.treats1=Table2.treats2 where who LIKE 'Fred' ?
cridley
09-22-2006, 10:19 AM
that doesn't work...
mattyblah
09-22-2006, 03:57 PM
select distinct treats2, who from Table1 left join Table2 on Table1.treats1=Table2.treats2 where who = 'Fred' or who is null

That should work.
cridley
09-25-2006, 04:24 AM
what if 'who' is 'john' and there are no null entries for it (as is the case with the data posted). those rows still won't be returned.
sridhar_423
09-25-2006, 05:08 AM
small change in Cridley's query..
give it a try ..
select * from Table1 left join Table2 on Table1.treats1=Table2.treats2 AND who LIKE 'Fred'
cridley
09-25-2006, 05:13 AM
that works except for access you need brackets :


select * from Table1 left join Table2 on (Table1.treats1=Table2.treats2 AND Table2.who LIKE 'Fred')
mattyblah
09-29-2006, 01:30 PM
what if 'who' is 'john' and there are no null entries for it (as is the case with the data posted). those rows still won't be returned.

I'm really not sure what that means. Who can be anything, including john or null. The result sets your query returns will not include any sets where who is null.

sweets, Fred
Toys, Fred
Clothes, null
Drinks, null

If he wants that result set, the query I wrote will return that. granted he might have to "order by who" which I did not include but other than that my results will return that record set. of course, it would be much better if this person let us know if they were able to get something working.