Joining two tables and displaying all unique items in both

Databases
1

Hi,
I have a situation and would like to know if this is possible.

I have two tables,

nabs_databody (12,600 records) unique SKU
nabs_new_items (2,400 records) also unique SKU

They both have a column DATEADDED which adds the date they were inserted into the database.

My problem is I need to create an php-sql select to display ALL items in nabs_databody and display NEW DATEADDED also.

here is an example of what I need:

nabs_databody

SKU,DATEADDED
somenumber,9/19/2011
someothernumber,9/12/2011

nabs_new_items
somenumber,9/15/2011

I need the script to print out the NEW date added and not the old without showing duplicate SKU…?

Is there a way to do this?

Thanks,
Kevin

Here is the code I was using but with no luck at all as it wasn’t displaying ALL the records in nabs_databody:



<?php

SELECT * FROM 

	nabs_databody
JOIN nabs_new_items ON (nabs_databody.SKU = nabs_new_items.SKU)
ORDER BY 	nabs_databody.SKU ASC LIMIT 25
?>
2

Mmmh.
Gonna flag this for moving to the MySQL forums. I’m gonna take a stab at it, but i have a feeling it can be done better/actually work.

SELECT SKU,CASE WHEN nabs_new_items.DATEADDED IS NOT NULL THEN nabs_new_items.DATEADDED ELSE nabs_databody.DATEADDED END CASE AS latestdate 
FROM nabs_databody 
LEFT JOIN nabs_new_items 
ON (nabs_databody.SKU = nabs_new_items.SKU)
ORDER BY     nabs_databody.SKU ASC LIMIT 25
3

Thank you, StarLion. I will give it a shot and see.

4

That worked !!! Thank you !!!