Where same products DONT exist in two tables

Databases
1

hi all

i have 3 tables

abd_table

  1. product_name
  2. price
  3. quantity
  4. total_cost
  5. unique_id
  6. date

order_detail_table

  1. order_id
  2. product_name
  3. price
  4. quantity
  5. total_cost
  6. unique_id
  7. order_date

order_table

  1. order_id
  2. payment_status
  3. order_date
  4. unique_id

I want to retreive rows

  1. WHERE same PRODUCT DETAILS dont exists or match in abd_table and order_detail_table

  2. WHERE payment_Status = PENDING

I tried with LEFT JOIN but its retreiving matching rows.

$qry="select a.date,a.quantity,a.total_cost,a.user_name,a.product_name,a.unique_id,a.price,o.order_id,nw.payment_status   
from abd_table AS a
INNER JOIN
order_detail_table as o
INNER JOIN
order_table AS nw
ON
a.unique_id=o.unique_id and a.unique_id=nw.unique_id
where nw.payment_status='PENDING'";

so how to retreive rows

  1. WHERE same PRODUCT DETAILS dont exists or match in abd_table and order_detail_table.

  2. WHERE payment_Status = PENDING

Vineet

2

So, just to ensure I understand you correctly, you want to only show the rows from the abd_table if:

  • there is NO matching record (by unique_id) on the order_detail_table
    OR

  • the values on the abd_table do not match what the corresponding values on the order_detail_table for the same unique_id

IF that’s correct, then your query would be something like this

SELECT a.date
     , a.quantity
     , a.total_cost
     , a.user_name
     , a.product_name
     , a.unique_id
     , a.price
     , nw.order_id
     , nw.payment_status   
  FROM order_table AS nw
  LEFT JOIN order_detail_table as o ON o.unique_id = nw.unique_id
 INNER JOIN abd_table AS a ON a.unique_id = o.unique_id
 WHERE nw.payment_status='PENDING'
   AND (o.order_id IS NULL OR 
	(a.product_name <> o.product_name OR
	 a.price <> o.price OR
	 a.quantity <> o.quantity OR
	 a.total_cost <> o.total_cost))

That being said, you have a real normalization problem in your database, and you’re going to run into issues trying to maintain this the longer you go. Just some of the issues I see:

  • You’re using a field name as a primary key on one table and a secondary key on another.
  • You’ve got duplicate fields across multiple tables. This can cause data to get out of sync (which you’ve obviously found)

IF you’re not too far down the rabbit hole with this project, I would suggest tweaking your database structure to something much more streamlined.

Some notes:

  • unique_id will now be the key JUST for the table it’s on. In other words, unique_id on abd_table is NOT the same as unique_id on order_table
  • if you see *table_id, that’s the foreign key to the appropriate table. For example, abd_table_id will be equal to a unique_id on abd_table
  • I’m assuming that order_id is just a “user friendly” order number and not just a sequential number - if it is just a sequential number, get rid of it and use the unique_id
  • total_cost COULD be derived (in other words calculated), but I left it here for now…

abd_table
unique_id
product_name
price

order_table
unique_id
order_id (see note above)
order_date
payment_status

order_detail_table
unique_id
order_table_id
abd_table_id
quantity
total_cost (see note above)

So to select the full order details, it would be something like this:

SELECT ot.order_id
     , ot.payment_status
     , ot.order_date
     , abd.product_name
     , abd.price
     , od.quantity
     , (abd.price * od.quantity) AS total_cost
  FROM order_table AS ot
  JOIN order_detail_table AS od ON ot.unique_id = od.order_table_id
  JOIN abd_table adt ON abd.unique_id = or.abd_table_id
 WHERE ot.payment_status = 'PENDING'
1 Like
3

Hi DaveMaxwell

can you explain a bit more the above sentence. I am not able to easily understand it.

what should you suggest i keep the names of the columns. If suppose there is product_name field and i want to have product_name field in different tables.

order_id is the id of the order that customer processed. it gets increment in the database when ever a new order is placed on website.
total_cost is already calculated and stored in database.

unique id automatically gets add to the column field of different tables.
unique id is kind of session_id stored in every table for the purpose of matching and fetching data that is of same session.
so when you say
unique_id on abd_table is NOT the same as unique_id on order_table
this makes me confused.

vineet

4

so unique_id is like a sessionID? Doesn’t make a lot of sense, but OK. You can use that to tie order_table to order_detail_table (though I would rename it from unique_id which makes a little more sense to someone coming in to maintain your code). HOWEVER, it doesn’t really belong on the abd_table since that looks to be a list of products, so there shouldn’t be a match to the order table. If, however, abd_table is a list of products which apply to the order, then what’s the purpose of the order_detail_table?

I’m suggesting getting rid of any place where confusion can reign, and where the same data can be held.

My point was if a field can be calculated just using values already in the database, you don’t NEED to keep it on the table as you can calculate it on the fly. You can if you like, it’s just not necessary

See my comment above re: unique_id.

It might behoove you to read up a little on database normalization. You’ll thank me later. Here’s a pretty good intro to normalization. I personally would stop after 3NF since I have never seen anything past it used in real life (a lot of times 2NF is enough)

5

thanks for tutorial link about database normalization.

but at this stage its not possible to change database structure.

vineet

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.