How can I INNER JOIN these 3 tables correctly?

55
April 16, 2019, at 04:20 AM

I have 3 tables. Orders, copacks, copack_products. I am trying to gather only 6 columns from orders but need to make sure that the products from those orders belong to those copacks. My Schema for these tables are as follows:

Table: orders
Columns:
idorders int(11) AI PK 
orderid varchar(45) 
orderstatus varchar(45) 
orderdate date 
subtotal int(11) 
shippingcost int(11) 
couponcode varchar(45) 
couponname varchar(45) 
coupontotal int(11) 
ordertotal int(11) 
refundamount int(11) 
customerid varchar(45) 
customername varchar(100) 
customeremail varchar(100) 
customerphone varchar(45) 
shipmethod varchar(45) 
paymentmethod varchar(45) 
storecreditredeemed int(11) 
totalquantity int(11) 
dateshipped date 
ordernotes varchar(400) 
customermessage varchar(400) 
shippingname varchar(45) 
shippingfirstname varchar(45) 
shippinglastname varchar(45) 
shippingcompany varchar(100) 
shippingstreet1 varchar(200) 
shippingstreet2 varchar(100) 
shippingsuburb varchar(45) 
shippingstate varchar(45) 
shippingzip varchar(45) 
shippingcountry varchar(45) 
shippingemail varchar(100) 
customergroupname varchar(45) 
productid int(11) 
productqty int(11) 
productsku varchar(200) 
productname varchar(200) 
productvariation varchar(200) 
productunitprice int(11) 
producttotalprice int(11) 
uniqueproductsinorder int(11) 
ordersource varchar(45) 
importid varchar(100) 
customerrep varchar(100)
Table: copacks
Columns:
idcopacks int(11) AI PK 
copackname varchar(100)
Table: copack_products
Columns:
idcopack_products int(11) AI PK 
copacknameid varchar(45) 
copackname varchar(100) 
productname varchar(100) 
productvariation varchar(100) 
mfgprice decimal(10,0)

The query is taking longer than 30 seconds and it times out.

I have tried the following code:

 Using cmd As New MySqlCommand("
SELECT orders.orderdate, orders.customername, orders.productqty, orders.productname, orders.productvariation, orders.producttotalprice
    , copacks.copackname, copack_products.productname, copack_products.productvariation  
FROM orders, copacks, copack_products 
INNER JOIN orders productname ON (productname.productname = copack_products.productname)  
INNER JOIN orders productvariation ON (productvariation.productvariation = copack_products.productvariation)  
WHERE copacks.copackname = '" & lblcopackname.Text & "' 
    AND orders.orderdate BETWEEN '" & date1.Value.ToString("yyyy-MM-dd") & "' AND '" & date2.Value.ToString("yyyy-MM-dd") & "' 
GROUP BY orders.productname
", con)
Answer 1

The first problem is this part:

FROM orders, copacks, copack_products 

This joins three tables before you start adding the other INNER JOIN steps, and does it with out any join conditions, such that you end up with every possible combination of of all of those tables.

Also, it is not okay to use string concatenation to put user-supplied values into an SQL query!!!. You need to learn about parameterized queries. I'd like to show an example using the SQL from the question, but unfortunately things are done too poorly in the question to post a good example :(

Finally, this schema design needs a lot of help. Traditionally, an orders table will not have products directly. Instead, there will be an order_header table and an order_detail or order_lines table. The order_detail table will have a foreign key to the order_id from the order_header table, such that you can have several order_detail records for the same order_header. It is also the order_detail table, and only the order_detail table, which will reference individual products.

There is a similar issue with the copack_products table. It is not good to reference references products and copacks by name in that table. A better design for the table would look like this:

Table: copack_products
Columns:
idcopack_products int(11) AI PK 
copackid int(11)
productid int(11) 

Just put the minimum needed in that table to connect back to the authoritative copack and products tables, and only use other columns if you allow differences from the main tables at this point.

Answer 2

Your query is not efficient. You are not joining the table properly. you should use below query

SELECT orders.orderdate,
       orders.customername,
       orders.productqty,
       orders.productname,
       orders.productvariation,
       orders.producttotalprice,
       copacks.copackname,
       copack_products.productname,
       copack_products.productvariation
FROM orders productname
INNER JOIN copack_products ON (productname.productname = copack_products.productname)
AND (productname.productvariation = copack_products.productvariation)
INNER JOIN copacks ON copacks products.copacknameid = copacks.Idcopacks
WHERE copacks.copackname = '" & lblcopackname.Text & "'
  AND orders.orderdate BETWEEN '" & date1.Value.ToString("yyyy-MM-dd") & "' AND '" & date2.Value.ToString("yyyy-MM-dd") & "'
GROUP BY orders.productname ", con)
READ ALSO
which hash function to choose for indexing uuid string on mysql? md5,crc64 or fnv

which hash function to choose for indexing uuid string on mysql? md5,crc64 or fnv

I have a column store the uuid stringI add a new column to store its integer(64bits) hash value for indexing

25
MySQL Error seems unfixable: Index column size too large

MySQL Error seems unfixable: Index column size too large

I'm getting: ERROR 1709 (HY000): Index column size too largeThe maximum column size is 767 bytes

37
How do I pass my url params to async functions in javascript files separate from the route with Node.js?

How do I pass my url params to async functions in javascript files separate from the route with Node.js?

I'm just learning to code and I'm building an application for my businessI'm having an issue with passing url parameters on my show route

42
MySql combine records from same table

MySql combine records from same table

I'm attempting to preform a sql command to update rows, and combine rows with matching data

36