"how would I go about retrieving any records from the invUpdate table that don't exist in the inventory table as well."
To recap, for tables you have:
INV (inventory)
UPD (invupdate)
Consider that any given "vin" may exist in...
1) just INV
2) both INV & UPD
3) just UPD
The query you posted pretty much covers 1 & 2.
Edit
@Macrunning wrote:
> hello jgreve.
> Yes I understand it covers 1 & 2.
> Yes I need 3 included on this.
> After the initial WHERE i.vin!='' statement,
> there is more based on user selection.
> These additional are concatenated using php.
> For example:
> if(isset($_GET['year'])){ $query_Recordset1.=" AND i.year='$myYear' "; }
>
> Therefor placing the union all after would impede on this.
> I thought there would be a cleaner solution by placing a
> subquery in the first LEFT JOIN statement.
Hmm... this is getting more complex. :-)
r.e. adding a subquery in the original question's first LEFT JOIN:
It seems like that will lead into a rats nest of complex logic on your WHERE clauses.
Consider this (u2 & u3 are my attempt to tie back to the 3 situations):
select i.vin as i_vin
, u2.vin as u2_vin
, u3.vin as u3_vin
from INVENTORY i
left join INVUPDATE u2 on u2.vin = i.vin
left join INVUPDATE u3 on u3.vin = i.vin
Source tables : Result set
+-----------+-----------+ : +-------+--------+--------+
| INVENTORY | INVUPDATE | : | i_vin | u2_vin | u3_vin |
+-----------+-----------+ : +-------+--------+--------+
| 100 | 200 | : | 100 | null | null |
| 200 | 300 | : | 200 | 200 | 200 |
+-----------+-----------+ : +-------+--------+--------+
Note that #300 never gets a chance to appear.
I don't think you can do this by combining LEFT JOINs.
You might be able to modify your "LEFT JOIN" to read from a sub select
instead of directly specifying the table, e.g.:
select i.vin as i_vin
, u2.vin as u2_vin
from INVENTORY i
left join (select usub.vin .... from INVUPDATE something somthing) u
But without running some experiments it is hard for me to predict
what that will yield for result sets (writing sub-selects is hard
for me to do without building it up incrementally against an actual
database).
I imagine you're going to have to limit #3 by all your PHP filters as well.
This is what I suspect will get ugly.
If MySQL supported a "full outer join" I would suggest possibly doing something like this: (see "No full joins on MySQL" part of Full Outer Join in MySQL )
-- hypothetical example, doesn't actually run in MySQL
select i.vin as i_vin
, u.vin as u_vin
from INVENTORY i
full outer join INVUPDATE u on u.vin = i.vin
Source tables : Result set
+-----------+-----------+ : +-------+-------+
| INVENTORY | INVUPDATE | : | i_vin | u_vin |
+-----------+-----------+ : +-------+-------+
| 100 | 200 | : | 100 | null |
| 200 | 300 | : | 200 | 200 |
+-----------+-----------+ : | null | 300 |
: +-------+-------+
However you would still need to filter by YEAR, MODEL, and whatever else your PHP front end suppoerts.
And when you have columns involved from various table qualifiers (e.g. i.year vs. u.year),
the logic for the WHERE clause seems ovelry complex (e.g. makes my head hurt).
So... what about the following approach?
The UNION approach looks more complex at first.
But I suspect it will simplify your filtering logic.
Because MySQL evaluates each part of the union independently,
we don't shouldn't need to worry so much about table qualifiers.
Which means you should be able to write "generic" filters...
Suppose we have the following PHP variables for filters:
$filter_YEAR = "\nAND year = '1995'"
$filter_YEAR = "\nAND model = 'XYZ'"
The goal is to generate SQL like this (start here for testing):
select '1) just INV' as record_source
, vin
, miles
...etc...
from FROM inventory i
where not exists( select 1 from invupdate u where u.vin=i.vin )
AND year = '1995'
AND model = 'XYZ'
-- In this part the "where NOT EXISTS" makes this "1) just INV"
-- ===============================================================
-- I'm avoiding @Uueerdo's neat trick with left-join on INVUPDATE
-- and filtering nulls in this part's WHERE clause because I don't
-- want to generate a $filter_YEAR_INV = "AND i.year = '1995'"
-- and another one for $filter_YEAR_UPD = "AND u.year = '1995'"
-- (note the different qualifiers).
-- ===============================================================
union all
select '2) both INV and UPD' as record_source
, vin
, miles
...etc...
from FROM invupdate u
-- the "where EXISTS" makes this "2) both INV and UPD"
where exists( select 1 from inventory i where u.vin=i.vin )
AND year = '1995'
AND model = 'XYZ'
union all
select '3) just UPD' as record_source
, vin
, miles
from FROM invupdate u
-- the "where NOT EXISTS" makes this "3) just UPD".
where not exists( select 1 from inventory i where u.vin=i.vin )
AND year = '1995'
AND model = 'XYZ'
order by record_source, vin
Since I don't have much (any) flight time in PHP I don't know if the following
will actually compile, but I hope you get the idea.
// obligatory: see "little bobby tables" https://xkcd.com/327/
// Sanitizing the input filters is beyond the scope of this answer.
$filter_YEAR = "";
if(isset($_GET['year'])){ $filter_YEAR ="\n AND year='$myYear' "; }
$filter_YMODEL = "";
if(isset($_GET['model'])){ $filter_MODEL ="\n AND year='$myModel' "; }
$filter_COLOR = "";
// At this point your $filter_WHATEVER variables either have an AND-clause
// that will work for all of the following.
// Or they are empty, in which case they wont change your resultant SQL.
$query_Recordset1 = "select '1) just INV' as record_source"
.= "\n , vin"
.= "\n , miles"
...etc...
.= "\nfrom FROM inventory i"
.= "\nwhere not exists( select 1 from invupdate u where u.vin=i.vin )"
.= $filter_YEAR$ . filter_YMODEL . $filter_COLOR;
// add SQL to get 2) both INV and UPD
$query_Recordset1 .= "union all"
.= "select '2) both INV and UPD' as record_source
.= "\n , vin
.= "\n , miles"
...etc...
.= "\nfrom FROM invupdate u
.= "\nwhere exists( select 1 from inventory i where u.vin=i.vin )"
.= $filter_YEAR$ . filter_YMODEL . $filter_COLOR;
// add SQL to get 3) just UPD
$query_Recordset1 .= .= "union all"
.= "select '3) just UPD' as record_source
.= "\n , vin
.= "\n , miles"
...etc...
.= "\nfrom FROM invupdate u
.= "\nwhere not exists( select 1 from inventory i where u.vin=i.vin )"
.= $filter_YEAR$ . filter_YMODEL . $filter_COLOR
.= "order by record_source, vin"
Original answer
(Edited to fix problem in SQL; remains here for completeness, but not so useful in light of the additional "dynamic filtering" requirements based on the PHP front end.)
(question: I just thought of this, will you ever have 2+ UPD records for a a given VIN ? Solving that is a little different, you might want to take use just th emost recent update record )
For #3 you could include with a FULL OUTER JOIN, but that will be really messy... your IF() expressions will be a headache to code and maintain.
Seems cleaner to me to do as @UUeerdo suggested in their comment.
This example adds a "sourceTables" column to help you trace down which table(s) a given record came from.
SELECT IF(u.vin IS NULL
, '1) Just INV'
, '2) Both INV & UPD' ) AS sourceTables
, IF(u.vin IS NULL, i.miles, u.miles) AS miles
, ...etc...
FROM inventory i
LEFT JOIN invUpdate u ON i.vin=u.vin
LEFT JOIN photos p ON i.vin=p.vin AND position=0
WHERE i.vin != ''
union all
SELECT '3) Just UPD' AS sourceTables
, u.miles AS miles
, ...etc...
from invUpdate u
-- edit: fixed "where" before LEFT JOIN error.
-- Also modified as per @Uueerdo's insightful comment.
LEFT JOIN inventory AS i ON u.vin = i.vin
LEFT JOIN photos p ON u.vin=p.vin AND position=0
WHERE u.vin != ''
AND i.vin IS NULL – Uueerdo 3 hours ago
Trivia point: note in the last LEFT JOIN we're joining "photos" on u.vin, not i.vin as above. Same for the WHERE u.vin != '' (I'm assuming you have a good reason for blocking empty vin #'s).
