SQL join 3 tables with empty record when no info

Question

I am having difficulty to understand what I would like to achieve :S - sorry about my English

I have a product table with a location history ID I have a history table with a location ID and last transferred date I have a location table with the location names

Tables:

**site_product**
product_id
product_name

**site_trans**
trans_id
trans_product - link the product
trans_inventory - for the location
trans_date2 - date when it was assigned

**site_location**
location_id
location_name

I use PHP to build an table to gather all the info together it is working and I have all columns sorted a part of the location.

Here is my working version BUT without the location names... (if that helps)

SELECT * FROM site_trans WHERE trans_product=product_id (e.g. 659) ORDER BY trans_date2 DESC Limit 1

It gives the location ID number but I would like to go further and get the location name see above. If there is one it does nothing so leaves.

Question:

how to link the location to this to make it work? Please.

Could someone to light the way how I could do a Select if within please?

(I am a beginner in many ways so even a lecture would be appreciated to start with)

UPDATE1: LIMIT 1 is designed for the last location

@Latheesan Kanes isn't `left join` exactly the same as `left outer join` in mysql? http://stackoverflow.com/a/2809609/520857 — Populus, Mar 19 '14 at 16:20
Hi thanks for that unfortunately didn't work I only get one record — user3319377, Mar 19 '14 at 16:24
post your tables structure. you don't use any alias in there idk how your query works it should say that on clause is ambiguous... — Marius.C, Mar 19 '14 at 16:30
A [fiddle](http://sqlfiddle.com/) with your schema and a few rows will help a lot. — Vatev, Mar 19 '14 at 16:34

score 0 · Answer 1 · answered Mar 19 '14 at 18:42

0

Brother, when you put "LIMIT 1" you're saying that you want just 1 record, so the sgbd will retrieve just 1 row.

You also should take a look at all kind of joins: http://www.devmedia.com.br/inner-cross-left-rigth-e-full-joins/21016

Maybe with the left join you can lose some data.

answered Mar 19 '14 at 18:42

Alan D.

89
4

I have rephrased my question see if that make more sense. Thanks for your help. – user3319377 Mar 20 '14 at 11:18
SELECT trans.*, loc.* FROM site_trans as trans, site_location as loc WHERE loc.location_id = trans.trans_inventory; -- If I understood what you want, and this will work if the trans_inventory references location_id. – Alan D. Mar 20 '14 at 21:06

score 0 · Answer 2 · answered Mar 20 '14 at 14:33

SELECT s.*, sl.location_name
FROM site_trans AS s
LEFT JOIN site_location AS sl ON sl.location_id = s.location_id
WHERE s.trans_product=646 
ORDER BY s.trans_date2 DESC 
Limit 1

I have had some help and here is the solution. Thanks for everyone. Amazing team

SQL join 3 tables with empty record when no info

2 Answers2