Join query with two tables

Question

I have this SQL query that didn't return any rows. I checked those tables which contain data so I think my query is incorrect.

)]2]1

my query is:

SELECT payment_type,with_service_total,sale_nm 
FROM `ospos_sales_payments` 
INNER JOIN ospos_sales_items ON ospos_sales_payments.sale_id = ospos_sales_items.sale_id 
WHERE 'sale_id' = '236'

[way ahead of you...](http://stackoverflow.com/questions/42144124/join-query-with-two-tables#comment71454744_42144124) @JonathanKuhn ;-) — Funk Forty Niner, Feb 09 '17 at 18:32
@JonathanKuhn True; I voted too soon earlier, darn it *lol* I only typed it all out. — Funk Forty Niner, Feb 09 '17 at 18:33
@Fred-ii- mine was the auto comment from just copying your link. — Jonathan Kuhn, Feb 09 '17 at 18:33

score 4 · Answer 1 · answered Feb 09 '17 at 18:29

The error is in your syntax. You are doing WHERE 'sale_id' = '236', so you are comparing two different strings, and this condition will always evaluate to FALSE.

Your query should be written like this:

SELECT 
  payment_type,
  with_service_total,
  sale_nm 
FROM `ospos_sales_payments` 
INNER JOIN ospos_sales_items 
  ON ospos_sales_payments.sale_id = ospos_sales_items.sale_id 
WHERE sale_id = '236'

Also, if the sale_id field is numeric, you can compare to 236 also without using quotes.

score 2 · Answer 2 · answered Feb 09 '17 at 18:26

2

Remove quote and add table name:

WHERE ospos_sales_payments.sale_id = '236'

or

WHERE `ospos_sales_payments`.`sale_id` = '236'

answered Feb 09 '17 at 18:26

Calos

1,783
19
28

Join query with two tables

2 Answers2