QUOTE(capty99 @ 9 Sep, 2008 - 12:25 PM)
you could just split it up.
so you select the distinct from table 1,
then when your printing your stuff, in a loop do another query to find the info in table 2 that corresponds to the name from table 1.
probably not great for a huge db but for this it shouldn't be a problem.
good coding practice to definitely change names of tables beyond table 1 and 2.
That's what I was trying to do, but it kind of didn't work, it's too complicated.
And table1 and table2 were only examples
QUOTE(baavgai @ 9 Sep, 2008 - 12:26 PM)
This
CODE
SELECT *
FROM table1 AS t1
left outer join table2 AS t2
on t1.name=t2.name
Will get you all of t1 and matches, so
CODE
Mom | 44 | Fridge
Mom | 44 | TV
Dad | 42 | NULL
Bro | 13 | Computer
If you don't want two Mom, then you need to limit table2 to one entry per name, like so:
CODE
SELECT Name, min(Wish) as FirstWish
FROM table2
group by Name
If you want the join with those results, it's a sub query:
CODE
SELECT *
FROM table1 AS t1
left outer join ( SELECT Name, min(Wish) as FirstWish FROM table2 group by Name ) AS t2
on t1.name=t2.name
Hope this helps.
Hmm... this might just work, I will try it
Thank you.
I had to put the WHERE statement after on t1.name=t2.name
| 