CODEWITHSUNDEEP
sqlpostgresql
Mohit Ranka
Mohit Ranka

Reputation: 4271

Querying data from different tables

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 (it's an autoincrement), I am getting more than 1 result. Am I missing something in here?

Upvotes: 0

Views: 560

Answers (5)

Chris Parkinson
Chris Parkinson

Reputation: 216

You need a join before the where clause:

INNER JOIN TableB ON TableA.Id = TableB.Id

Upvotes: 0

Jason Cohen
Jason Cohen

Reputation: 83121

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

Upvotes: 3

Pablo Fernandez
Pablo Fernandez

Reputation: 105258

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

Upvotes: 1

Ben McEvoy
Ben McEvoy

Reputation: 654

You need a join:

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

Upvotes: 7

Jon Skeet
Jon Skeet

Reputation: 1504052

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.

Upvotes: 6

Related Questions