CODEWITHSUNDEEP
sqlpostgresql
Trentfrompunchbowl1
Trentfrompunchbowl1

Reputation: 109

Select with table join

I want to get * from concrete_samples but only method_name and not the id from concrete_compaction_methods

SELECT * FROM concrete_samples,concrete_compaction_methods WHERE concrete_compaction_methods.id = concrete_samples.compaction_method AND workorder_id=1

This is currently returning everything I want EXCEPT it's giving me the id column of the methods table which I don't want.

the pseudo code of the statement I want to do is 

SELECT * FROM concrete_samples,  SELECT method_name FROM concrete_compaction_methods WHERE concrete_compaction_methods.id = concrete_samples.compaction_method AND workorder_id=1

I've done some research. I've tried to use a union but i don't think that's the correct or neatest solution

Thank you

Upvotes: 1

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I strongly advise:

  • Learn to use proper JOIN syntax.
  • Use table aliases in your query.
  • Qualify all column references.

So the query looks more like this:

SELECT cs.*, ccm.method_name
FROM concrete_samples cs JOIN
     concrete_compaction_methods ccm
     ON ccm.id = cs.compaction_method 
WHERE cs.workorder_id = 1;

I am guessing that workorder_id comes from concrete_samples rather than the other table.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try below -

SELECT concrete_samples.*,method_name 
   FROM concrete_samples inner join concrete_compaction_methods 
   on concrete_compaction_methods.id = concrete_samples.compaction_method 
where workorder_id=1

Upvotes: 1

Related Questions