Complex SELECT statement problem

acidbox

I have 2 tables.

Table one has:

has a VARCHAR id

         table_1

+-----------------------------+
| ID | NAME | LOCATION |
+-----------------------------+
| A-5 | JOHN | A PLACE |
+-----------------------------+

            table_2
    +--------------+
    |   ID   |  VALID  | 
    +--------------+
    |  A-5  |     1      |
    +--------------+

I need to select all the information from table one if the ID in table 2 is valid (ie: valid=1).

Here is what I have so far, but it doesn't work. It selects all the information, but it sets the valid = 1 for EVERYTHING, no matter if it is 1 or 0 in the database to begin with. Can anyone help me with this?

SELECT DISTINCT c.id, c.name, c.location, d.valid FROM table_1 c, table_2 d WHERE d.valid = 1 AND c.id LIKE '%'

tomhath

Well, it does work. Or I should say, it's return exactly what you asked for (unfortunately, that's not what you want 😃 )

Anyway, add this to your WHERE clause: AND c.id = d.id

examancer

here is a sql statement that should return what you want:

SELECT c.id AS id, name, location, valid FROM c, d WHERE c.id = d.id AND valid = 1

you only need to specify the table (as in c.id, d.id) when the same column name exists in more than one of the tables you are selecting from. Unique column names can just be called by their name.

acidbox

thanks to the both of you, it worked like a charm!😃

[deleted]

" Unique column names can just be called by their name."

But it's generally a good idea to always mention the table name anyway, because it 's easy to get confused about which table each column comes from, especially when using more than two columns.

Also, try to stay away from abbreviations like a,b,c, because that again doesn't help to clear up which tables you are talking about.