SQL query to select everything from 1 table and 1 column(same name as in table 1) from another table

I have 3 tables.
table1 :

Products:
ProductId Category Model Price Quantity


table2:

OrderLines:
OrderId ProductId Quantity


table3:

Orders:
OrderId CustomerId


(theres also a customer table but it doesnt matter)

Now I want to select all the products data from Products table that have ProductId = ProductId in OrderLines table, but get the quantity data (of those same products) from the OrderLines table.

Tried all kinds of queries, couldn't find one that works.

heres the last thing i kind of tried:

SELECT tb1.* ,tb2.[Quantity]
FROM [Products] tb1, [OrderLines] tb2
WHERE tb1.[ProductId] IN (
SELECT [ProductId] FROM [OrderLines] WHERE OrderId = @orderId)
INNER JOIN [OrderLines] tb2 ON[Products].Quantity = [OrderLines].Quantity";


didn't worked.

any suggestions?

Thanks a lot.

sample data:
Orders:
OrderId CustomerId
1 2

OrderLines:
OrderId ProductId Quantity
1 3 1
1 4 5

Products:
ProductId Category Model Price Quantity
3 "wow" "hd" 30 5
4 "yay" "sd" 50 60
5 "wow" "ss" 12 5

expected output:
array of products:
each object is type Product, having the same fields in as in the table. (already defined it's class).
first object in the array is:
productId = 3, Category="wow",Model="hd", price=30,Quantity =
1 (**1! not 5)
2nd object:
productId = 4, Category="yay",Model="sd",price=50,Quantity=5

I am using c# in visual studio, Winforms.
database is Access.
this is the function:

public Product GetProductsByOrderId(int orderId)
{
DataSet ds = new DataSet();
ArrayList arrProducts = new ArrayList();

//"SELECT tb1.* ,tb2.[Quantity] FROM [Products] tb1, [OrderLines] tb2 WHERE tb1.[ProductId] IN (SELECT [ProductId] FROM [OrderLines] WHERE OrderId = @orderId) INNER JOIN [OrderLines] tb2 ON[Products].Quantity = [OrderLines].Quantity"
string cmdStr = "SELECT p.*, ol.Quantity as OrderLineQuantity from Products as p inner join OrderLines as ol on p.Id = ol.ProductId";

using (OleDbCommand command = new OleDbCommand(cmdStr))
{
command.Parameters.AddWithValue("@orderId", orderId);
ds = GetMultipleQuery(command);
}

DataTable dt = new DataTable();
try
{
dt = ds.Tables[0];
}
catch { }
foreach (DataRow tType in dt.Rows)
{
Product productData = new Product();

productData.ProductId = int.Parse(tType[0].ToString());
productData.Category = tType[1].ToString();
productData.Model = tType[2].ToString();
productData.Price = double.Parse(tType[3].ToString());
productData.Quantity = int.Parse(tType[4].ToString());
arrProducts.Add(productData);
}
return (Product)arrProducts.ToArray(typeof(Product));
}


Sorry and I Hope it's more clear now.

(1) Sample data and desired results would really help. (2) MySQL does not use square braces. Are you sure you have tagged the question correctly?
– Gordon Linoff
Jun 29 at 21:21

Sample data and expected output data plz?
– Vivek
Jun 29 at 21:24

Tag properly!!! You tag MYSQL, but your code looks like SQL Server. Which one is this????
– Eric
Jun 29 at 21:29

So add Access tag to your question so that people who do not know Access won't waste their time reading this question.
– Eric
Jun 29 at 21:53

@Eric done.done.
– Piepypye
Jun 29 at 21:56

4 Answers
4

This will get you four columns from product and 1 from orderlines based on a join.

SELECT p.producId, p.category, p.model, p.price, o.quantity
from products p
join orderlines o on p.productid = o.productid


Small style note -- don't use the plural for the table name. eg use
product not products and orderline not orderlines. No good reason except that is how everyone does it.

Spelling error in p.producId - missing "t"
– dbmitch
yesterday

p.producId

The actual query that works is:

"SELECT p.ProductId, p.Category, p.Model, p.Price,
o.Quantity FROM [Products] p INNER JOIN [OrderLines] o ON
p.ProductId = o.ProductId WHERE o.OrderId = @orderId
ORDER BY p.ProductId"


Thanks for @Hogan for getting me started in the right direction.

edited thanks to lucas mention, deleted the WHERE IN SELECT...

If you're doing an inner join on ProductId the following WHERE ProductId IN (SELECT ...) is completely reduntant.
– Lucas
Jun 30 at 0:18

WHERE ProductId IN (SELECT ...)

@Lucas but I have to have someway to select ProductId(s) that are part of the Orders table, with that OrderId. none of the solutions uses parameter that way (@orderId) which is necessary.
– Piepypye
Jun 30 at 11:34

select p.ProductId,sum(ol.Quantity)
from Products p
inner join orderlines ol on p.ProductID=ol.ProductID
group by p.ProductId


Please add an explanation, not just a raw code dump.
– mason
Jun 29 at 22:51

SELECT p.*, ol.Quantity as OrderLineQuantity from
Products as p
inner join
OrderLines as ol
on p.Id = ol.ProductId


Please add an explanation and not just a raw code dump.
– mason
Jun 29 at 22:52

@mason It's a very basic and straightforward join, really not much to explain there. If you look at Ops question he already uses an inner join so I assume he knows what it is. If other people come across this question and can't understand an inner join it's better to let them figure out basic SQL by themselves than try to hold their hand to say 1+1=2.
– Lucas
Jun 30 at 0:14

Pardon me mason, are you testing a "Please add an explanation and not just a raw code dump." spambot that downvotes answers? Do you consider 4 lines a "dump"? Have you considered the simplicity of the question is enough to warrant downvotes? Lucas said it finely.
– George Menoutis
2 days ago

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.