[RESOLVED] Using NOT IN

[chris.cavage]

I am using MySQL.

I have two tables.
1. Inventory: has rows of Serial Numbers (inventory.SN)
2. Items_Sold: has rows which have two columns of Serial Numbers: Left_SN and Right_SN

I am trying to work on a statement that will see what's left in inventory. I want to display all items in the inventory table that have not been assigned to either Left_SN or Right_SN in a row in Items_Sold.

SELECT DISTINCT inventory.SN
FROM inventory
WHERE inventory.SN NOT IN 
(SELECT items_sold.Left_SN
FROM items_sold)

The above doesn't work, to start with, but even if it did, it excludes the other column: items_sold.Right_SN

... so I then started to do this:

SELECT * FROM inventory
LEFT JOIN items_sold
ON inventory.SN = items_sold.Left_SN
WHERE items_sold.Left_SN IS NULL

This works great, but it excludes items_sold.Right_SN from the search results.

Any tips? Thanks.

[techgnome]

OK... so you want to get everything in Inventory that has not been used for Left_SN OR Right_SN... right?

To get the list of used SN parts: (from both left and right)

SELECT Left_SN AS SN
FROM items_sold
UNION
SELECT Right_SN AS SN
FROM items_sold

That will give you a list of all the SNs used.
Now we can add that to the main query:

SELECT * FROM inventory
LEFT JOIN (SELECT Left_SN AS SN
                FROM items_sold
                    UNION
                SELECT Right_SN AS SN
                FROM items_sold) S
ON inventory.SN = S.SN
WHERE S.SN IS NULL

-tg

[chris.cavage]

Thanks, Techgnome. I am going to try to build off this query. I appreciate your help.

I have a follow up question.

I probably should have went into this much detail earlier, but I wanted to work on your query to see how I did first.

The items_sold table is more like items_on_trial. The items in inventory are on a trial basis. In my items_on_trial table I have: ID, Left_SN, Left_Delivery Date, Left_Kept_Date, Left_Return_Date, Right_SN, Right_Delivery_Date, Right_Return_Date, Right_Kept_Date.

So a Serial Number (SN) is entered into the items_on_trial table in either Left_SN or Right_SN fields and then the delivery date is entered. I want the items in inventory to display the most recent time they were delivered and returned. If they are returned, then of course, they would still be in inventory. If the inventory item (inventory.SN) is kept (a value is entered into Left_Kept_Date or Right_Kept_Date) then the item would be excluded from the inventory recordset.

Here's what I'm working on:

SELECT * FROM inventory 
LEFT JOIN 
(SELECT Left_SN AS SN, Left_Kept_Date AS Kept_Date, Left_Delivery_Date AS Trial_Start, Left_Return_Date AS Return_Date
FROM items_on_trial
UNION 
SELECT Right_SN AS SN, Right_Kept_Date AS Kept_Date, Right_Delivery_Date AS Trial_Start, Right_Return_Date AS Return_Date
FROM items_on_trial) S
ON inventory.SN = S.SN 
WHERE Kept_Date IS NULL
GROUP BY inventory.SN
ORDER BY Trial_Start DESC

I used "GROUP BY inventory.SN" because if an inventory item has been returned and goes on trial with someone else, I only want it to show up once. However, the problems I have with this query:

It doesn't display the most recent Trial_Start and Return_Date info for inventory.SN

Can you help? Does the query make sense for what I want to do?

[chris.cavage]

Update on this:

I made this:

SELECT * FROM inventory 
LEFT JOIN 
(SELECT ID, Left_SN AS SN, Left_Kept_Date AS Kept_Date, Left_Delivery_Date AS Trial_Start, Left_Return_Date AS Return_Date
FROM items_on_trial 
UNION 
SELECT ID, Right_SN AS SN, Right_Kept_Date AS Kept_Date, Right_Delivery_Date AS Trial_Start, Right_Return_Date AS Return_Date
FROM items_on_trial
ORDER BY Trial_Start DESC) S
ON inventory.SN = S.SN 
GROUP BY inventory.SN
ORDER BY Kept_Date ASC, Model

I like this. I display the most recent time the inventory item is on trial: start and return (if applicable). If kept, the inventory item is displayed at the end of the recordset with the kept date.

However, I still don't understand why not all of the Serial Numbers (inventory.SN) are not displayed in my php repeat region. Only the ones that have values for Trial_Start are displayed, I noticed. The make, model, and other column information is displayed regardless.

Should I move this to the php forum, or is there something wrong with my above SQL?

[chris.cavage]

My php code to display the SN is:

<?php echo $row_inventory['SN']; ?

[chris.cavage]

I cheated, but this works:

SELECT *, CONCAT('SN', ': ', inventory.SN) AS Info FROM inventory 
...
...
...

Then...

<?php echo $row_inventory['Info']; ?

I do believe not all the SN's were displayed because of the "LEFT JOIN", so I created a new column that concatenated the SN's so I could display all of them!

If anyone has a better suggestion, please let me know.