Joining tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining tables

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-10 : 12:10:35

Hi

This code below maps all products in a SQL table to two customer levels

DELETE FROM ProductCustomerLevel
WHERE CustomerLevelID IN (5, 7)

INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID
)
SELECT ProductID,
CustomerLevel
FROM dbo.Product p
CROSS JOIN (
SELECT 5 AS CustomerLevel UNION ALL
SELECT 7
)c

I have another table called Section and rather than map all the products to these two levels as above I just want to map those from the Section table with SectionID of 5345 and 5346

Any idea how I can do this?

And the same for this query

DELETE FROM ExtendedPrice
WHERE CustomerLevelID IN (5, 7)

INSERT ExtendedPrice
(
VariantID,
CustomerLevelID,
[ExtensionData],
Price,
[CreatedOn],
[UpdatedOn]
)
SELECT p.VariantID,
c.Custlevel,
NULL,
p.SalePrice,
GETDATE(),
GETDATE()
FROM ProductVariant p
CROSS JOIN (
SELECT 5 AS CustLevel UNION ALL
SELECT 7
)c
WHERE p.SalePrice IS NOT NULL

Again I just want it to it relates to SectionID 5345 and 5346 from a table called Section

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-10 : 14:36:27

guessing here because you didn't tell us anything about how section table relates to anything. maybe this?


SELECT p.ProductID,
       c.CustomerLevel
FROM dbo.Product p
inner join dbo.section s on s.productid = p.productid
CROSS JOIN (SELECT 5 AS CustomerLevel 
       UNION ALL SELECT 7
       )c
where  s.SectionID in (5345,5346)

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 23:11:01

how is productvariant related to sections? In case posting how all three tables are related would help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-11 : 06:48:21

Ok so basically I have three SQL tables that I am trying to run. In my first post the two SQL queries work perfect. But they apply to all products. I need to only have these assigned to two Section ID's.

Script

INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID
)
SELECT ProductID,
CustomerLevel
FROM dbo.Product p
CROSS JOIN (
SELECT 5 AS CustomerLevel UNION ALL
SELECT 7
)c

This maps all ProductID values to Customer Levels 5 & 7. What I want to do here is map all Products only to SectionID's 5345 and 5346. These Section ID's are held within a table Called Sections. There is also another table called ProuductSection. The value of ProductID is in both Product.dbo and ProductSection.dbo.

Within ProductSection the fields are ProdcutID,SectionID so I need to link these sectionid to the query above

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-11 : 07:44:23

I have this SQL statement that now selects what I want but how can I insert this into the code above?

SELECT p.ProductID FROM dbo.Product p
INNER JOIN dbo.ProuductSection ps ON (p.ProductID = ps.ProductID)
WHERE ps.SectionID IN (5345, 5346)

when I try I get this error

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-11 : 12:41:18

[code]
INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID
)
SELECT p.ProductID,
p.CustomerLevel
FROM dbo.Product p
INNER JOIN dbo.ProuductSection ps ON (p.ProductID = ps.ProductID)
CROSS JOIN (
SELECT 5 AS CustomerLevel UNION ALL
SELECT 7
)c
WHERE ps.SectionID IN (5345, 5346)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources