Left Join

Losing data

[wheelie34]

I have 2 tables, table1 has column1, column2, column3 when a product is not available details will be held in table2 until its available again then those details will be deleted from table2

As a test I have added 3 rows of product to table1 and added unavailable details in table2 for 2 of them, when doing a stock check, if I search in order to get all results out of table1 it does return the correct amount of results BUT it is dropping the column1 content for the results that are NOT in table2 in any form BUT still displays column2 and column3 contents for it, where has it gone? it returns * for the 2 that ARE mentioned in table2.

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column WHERE table2.column2!='$variable' OR table2.column IS NULL

Thanks

[Demaestro]

Try this just to see what you get...

SELECT
table1.column as tbl1_col1,
table1.column2 as tbl1_col2,
table1.column3 as tbl1_col3,
table2.column as tbl2_col1,
table2.column2 as tbl2_col2,
table2.column3 as tbl2_col3

FROM table1 LEFT JOIN table2 ON table1.column = table2.column WHERE table2.column2!='$variable' OR table2.column IS NULL

It might be cause you are selecting * from table1 only.

The way you described the functionality has me worried though.... you shouldn't be writing and erasing data like that.

[edited by: Demaestro at 7:12 pm (utc) on Jan. 17, 2008]

[physics]

Not sure but I think maybe you need to use a LEFT OUTER JOIN.

[en.wikipedia.org...]

[wheelie34]

Hi sorry for taking time to come back, the LEFT JOIN wasn't going in the right direction.

The problem I have is its a calendar type booking system for products only available for certain periods of time, I am trying to not allow an overlap, I am working on a system that is already in place but has one flaw, when date range searching using between the 2 dates it DOES bring back the correct results, but IF you searched for dates that also appeared outside of the NON result area that could overlap that would end up within the results, does that make any sense?

example: a product can be entered with dates into the db for the following

product ---- date -- status
product_1 2008-01-02 2
product_1 2008-01-03 2
product_1 2008-01-04 2
product_1 2008-01-05 2
product_1 2008-01-06 1
product_2 2008-01-02 1
product_2 2008-01-03 1
product_2 2008-01-04 1
product_2 2008-01-05 1

If you search between
start: 2008-01-02 end: 2008-01-05

You would get zero result for product_1

If you search between
start: 2008-01-02 end: 2008-01-06

You would get one hit as product_1 has one of the dates you wanted to check available, it currently does this and prints out "One or more dates are available for this product" which leaves the user guessing as to which one.

Is there any way of capturing that available date and printing it in the result, heres the current select statement.

$sql = "SELECT DISTINCT product FROM table WHERE status =1 AND date BETWEEN '$start' and '$end'";

Ideally the user would be presented with the option of taking that day and a few either before or after it instead of their first choice, how would I accomplish this, any ideas?