Two table join.. one listbox row return

Getting Started
1

Good evening,
I have two identical tables,Table1 and Table2. Each have two fields, name and state…

The first table has values John and Florida, the second table has values Sally and Florida.

I want to write a join statement and display the results on one line of a listbox.in each table.

My intended output is a one row listbox that states “ John, Sally, Florida”. Aka show me all instances where two people share the same state.

Can anyone help me with the join statement, and more importantly, how do I structure the list box?

What I have so far…

SQL = “ SELECT * from Table1 inner join Table2 on Table1.state = Table2.state where Table1.state = Table2.state;”

With a two column listbox of name and state, I get:
John Florida
Sally Florida

How do I combine the results into a single three column listbox…? (Assuming my join is correct)

Thanks in advance… -John

2 
SELECT a.name,b.name as name2,a.state
FROM table1 a, table2 b
where table1.state=table2.state

if table1 has a state that table2 does not , there will be no output for that state

if you want that then

SELECT a.name,b.name as name2 ,a.state
FROM table1 a
left join table2 b
on table1.state=table2.state

or both ways

SELECT DISTINCT name,name2,state 
FROM (
SELECT a.name,b.name as name2,a.state
FROM table1 a
left join table2 b
on table1.state=table2.state
UNION
SELECT a.name,b.name as name2,a.state
FROM table2 a
left join table1 a
on table1.state=table2.state)
3

Thanks Dave. Can my three column listbox headers be a.name, b.name, and a.state?

And are their any periods on the FROM LINE?

4

look at my post, I just updated it.