[RESOLVED] multiple table query

[etoast]

Table_1 contains name column and yes/no column
Table_2 contains name column and number column

Typical rows from Table_1:
John, yes
Bill, no
Pete, yes

Typical rows from Table_2:
John, 2
John, 435
John, 34
Bill, 234
Bill, 19

Here is my question - I want to run a query that says if Table_1 yes/no column = no, then ignore that person. And, if Table_1 contains yes, but the person is not found in Table_2, then ignore that person. Else return people in a SELECT query with information from other columns in both Table_1 AND Table_2.

In other words, using the above example. Bill would be ignored because he is set to "no" in Table_1 and Pete is also ignored, because dispite being set to "yes" in Table_1, he is does not have any values in Table_2.

Clear as mud? 🙂 I hope someone can give me some hints as to how to do this query. I used to have it easy as all the data was in a single table, but Piranha pointed out it broke normalisation rules. Now my tables are up to scratch but I have no idea how to write this query 🙁

Many thanks

[Piranha]

SELECT table1.name, table1.yes/no, table2.number
FROM table1
INNER JOIN table2 ON table1.name = table2.name // Inner join only returns what is in both tables. So Pete won't be returned
WHERE table1.yes/no != 'no' // Everyone that doesn't have no in table1 will be returned. Bill won't be returned. It might be better to set WHERE table1.yes/no = 'yes' instead, then it will return only the ones with yes and not with NULL, "echo" or other values.

It seems like you should read up a bit on JOINS. Unfortunatley I don't have any good tutorial for you, but I'm sure that you can find it with a simple search.

[etoast]

INNER JOIN, superb
Thanks once again Piranha!

[Piranha]

No problem m8, always glad to help people that show that they want the help and learn from it. 😃