Querying data from different tables

Question

I am using a query like this on my postgres database,

SELECT TableA.id FROM TableA , TableB WHERE TableA.id = 100;

Each TableA.id is unique (its an autoincrement), I am gettting more than 1 result. Am i missing something in here?

Answer 1

You're doing a cross-join - effectively every row in TableB against the single row in TableA. If you select something from TableB as well, this will be more obvious :) The fact that you don't currently select anything from TableB doesn't stop the (TableA, TableB) pairs from being the result of the join, before the projection.

Answer 2

You need a join:

SELECT TableA.ID from TableA
INNER JOIN TableB 
ON TableB.TableAID = TableA.ID 
WHERE TableA.ID = 100

Answer 3

which is the relation between TableA and TableB?

you might need to do something like this

Where TableA.id = 100 And TableB.TableA_Id = TableA.id

Answer 4

You're getting one row from TableA but all rows from TableB. Perhaps you meant:

SELECT TableA.id FROM TableA, TableB WHERE TableA.id=TableB.id AND TableA.id = 100

Answer 5

You need a join before the where clause:

INNER JOIN TableB ON TableA.Id = TableB.Id