SQL Query help (1 Viewer)

smig

Registered User.
Local time
Today, 18:08
Joined
Nov 25, 2009
Messages
2,209
Hi

I need some help creating am SQL query

I have 3 tables
tblMaterials - MaterialID, MaterialName. MaterialType
tblProducts - ProductID, ProductName
tblProductsMaterials - ProductID, MaterialID, MaterialPercent (This one is a Many to Many table)

Now I need queries to sum data
1st query - Count Materials for each productID, Where MaterialType=1
2nd query - Sum the MaterialPercent for each productID Where MaterialType=2

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:08
Joined
Oct 29, 2018
Messages
21,358
Hi. It would be easier if you could post a sample copy of your db with test data, but try the following for Query1:
Code:
SELECT ProductID, Count(*) AS ProductCount
FROM tblProductsMaterials
INNER JOIN tblMaterials
ON tblProductsMaterials.MaterialID=tblMaterials.MaterialID
WHERE MaterialType=1
GROUP BY ProductID
(untested)
 

smig

Registered User.
Local time
Today, 18:08
Joined
Nov 25, 2009
Messages
2,209
Hi. It would be easier if you could post a sample copy of your db with test data, but try the following for Query1:
Code:
SELECT ProductID, Count(*) AS ProductCount
FROM tblProductsMaterials
INNER JOIN tblMaterials
ON tblProductsMaterials.MaterialID=tblMaterials.MaterialID
WHERE MaterialType=1
GROUP BY ProductID
(untested)
Thank you for the quick reply

To be more exact I need to see all products, even those how has no MaterialType = 1
Actually those products are the most impotent for me

Thanks
 

plog

Banishment Pending
Local time
Today, 11:08
Joined
May 11, 2011
Messages
11,611
Smells like homework--which is fine. But what have you tried?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:08
Joined
Oct 29, 2018
Messages
21,358
Thank you for the quick reply

To be more exact I need to see all products, even those how has no MaterialType = 1
Actually those products are the most impotent for me

Thanks
Hi. If you want to see "all products," then just take out the WHERE clause. And for that matter, you don't even need to JOIN two tables, you just need one (tblProductsMaterials).
 

smig

Registered User.
Local time
Today, 18:08
Joined
Nov 25, 2009
Messages
2,209
Hi. If you want to see "all products," then just take out the WHERE clause. And for that matter, you don't even need to JOIN two tables, you just need one (tblProductsMaterials).
Thanks
I need products that has no MaterialType = 1
I think a left join is the right way
 

smig

Registered User.
Local time
Today, 18:08
Joined
Nov 25, 2009
Messages
2,209
What I'm trying to do is to put query Q1 into:
Code:
SELECT [Products].[ProductID]
FROM [Products] LEFT JOIN [q1] ON [Products].[ProductID] = [q1].[ProductID]
WHERE [q1].[ProductID] Is Null

Q1 query
Code:
SELECT [Products_Materials].[ProductID]
FROM [Products_Materials] INNER JOIN [Materials] ON [Products_Materials].[MaterialID] = [Materials].[MaterialID]
WHERE [Materials].[MaterialTypeID]=1 
GROUP BY [Products_Materials].[ProductID]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:08
Joined
Oct 29, 2018
Messages
21,358
Hi. I can't tell if you still need any help. If so, you might consider posting some sample data and a mockup of the result you're trying to get out of it. Cheers!
 

smig

Registered User.
Local time
Today, 18:08
Joined
Nov 25, 2009
Messages
2,209
Hi. I can't tell if you still need any help. If so, you might consider posting some sample data and a mockup of the result you're trying to get out of it. Cheers!
Hi, Thanks
Yes, I do need some help

As I wrote in my last post I would like to "integrate" Q1 into the other, but have it all written as a single SQL.

If this is not possible I will create Q1 as a built query.

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:08
Joined
Oct 29, 2018
Messages
21,358
Hi, Thanks
Yes, I do need some help

As I wrote in my last post I would like to "integrate" Q1 into the other, but have it all written as a single SQL.

If this is not possible I will create Q1 as a built query.

Thanks
Hmm, is this what you mean?
Code:
SELECT [Products].[ProductID] 
FROM [Products] 
LEFT JOIN (SELECT [Products_Materials].[ProductID] 
        FROM [Products_Materials] 
        INNER JOIN [Materials] 
            ON [Products_Materials].[MaterialID] = [Materials].[MaterialID] 
        WHERE [Materials].[MaterialTypeID]=1  
        GROUP BY [Products_Materials].[ProductID]) [q1] 
    ON [Products].[ProductID] = [q1].[ProductID] 
 WHERE [q1].[ProductID] Is Null
(untested)
 

smig

Registered User.
Local time
Today, 18:08
Joined
Nov 25, 2009
Messages
2,209
Hmm, is this what you mean?
Code:
SELECT [Products].[ProductID] 
FROM [Products] 
LEFT JOIN (SELECT [Products_Materials].[ProductID] 
        FROM [Products_Materials] 
        INNER JOIN [Materials] 
            ON [Products_Materials].[MaterialID] = [Materials].[MaterialID] 
        WHERE [Materials].[MaterialTypeID]=1  
        GROUP BY [Products_Materials].[ProductID]) [q1] 
    ON [Products].[ProductID] = [q1].[ProductID] 
 WHERE [q1].[ProductID] Is Null
(untested)

This one made my head spin :D:D:D
I'll test it and let you know if it works.

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:08
Joined
Oct 29, 2018
Messages
21,358
This one made my head spin :D:D:D
I'll test it and let you know if it works.

Thanks
Hi. If it doesn't work, we'll really need to know what you're trying to do and see some sample data to be able to tell you how to construct the proper SQL statement to get what you want. Good luck!
 

smig

Registered User.
Local time
Today, 18:08
Joined
Nov 25, 2009
Messages
2,209
After some tests I changed to Transform query.
As I have only 3 MaterialTypeID it was easy to test

Thank you all for your help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:08
Joined
Oct 29, 2018
Messages
21,358
After some tests I changed to Transform query.
As I have only 3 MaterialTypeID it was easy to test

Thank you all for your help

Hi. Congratulations. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom