SQL Server Query

Hi All,

I have an scenario in sql server  we have a table name visit body. In this table we have multiple questions like Q1,Q2,Q3 and Q1 is related with product p1,p2. and Q2 is related with product p1 and Q3 is related with p1,p2, p3. Query is that when user select p1 product then only Q2 should come and if user select p1 and p2 then Q1 should come. Please help me out how to achieve it through SQL query.

Thanks in advance

March 26th, 2015 7:31am

Hi Naveena

If you want us to provide ab accurate suggestion,please post CREATE TABLE+INSERT INTO+ desired result. 

Always state what version you are using

Free Windows Admin Tool Kit Click here and download it now
March 26th, 2015 7:37am

Sorry but (in technologic terms) why if you select p1 only Q1 should come? and if you select p1 and p2 only Q2 should come?

If it is a rule you need some code in order to set up the query or the SP. Let us know.

March 26th, 2015 9:58am

Please post your table structure as mentioned by Uri Dimant, in order to get help.

Cheers

Ronnie

Free Windows Admin Tool Kit Click here and download it now
March 26th, 2015 10:12am

Sounds like you're trying to use a "bridge table" (also know as a "junction table" or a "many to many table"), but maybe missing the point...

The idea is that a single product can have many questions and a single question can apply to many products. This is known as a "many to many" relationship and it modeled using a bridge table that holds all of the various combinations that can occur between the two entities.

Below is an example...

IF OBJECT_ID('tempdb..#products') IS NOT NULL
DROP TABLE #products
GO
CREATE TABLE #products (
	ProductID INT NOT NULL PRIMARY KEY,
	ProductName VARCHAR(20) NOT NULL 
	)
INSERT #products (ProductID,ProductName) VALUES
(1,'Hats'),
(2,'Cars'),
(3,'Ice Cream'),
(4,'Shampoo')

IF OBJECT_ID('tempdb..#questions') IS NOT NULL
DROP TABLE #questions
GO
CREATE TABLE #questions (
	QuestionID INT NOT NULL PRIMARY KEY,
	QuestionText VARCHAR(50) NOT NULL 
	)
INSERT #questions (QuestionID,QuestionText) VALUES
(1,'Do you like the interior?'),
(2,'Do you like the way it smells?'),
(3,'Doess it tase good?'),
(4,'Did it make you hair fall out?'),
(5,'Was it visually appealing?'),
(6,'Did make your eyes burn?')

IF OBJECT_ID('tempdb..#Product_questions') IS NOT NULL
DROP TABLE #Product_questions
GO
CREATE TABLE #Product_questions (
	ProductID  INT NOT NULL,
	QuestionID INT NOT NULL,
	PRIMARY KEY (ProductID,QuestionID)
	)
INSERT #Product_questions (ProductID, QuestionID) VALUES
(1,1),
(1,4),
(1,5),
(2,1),
(2,2),
(2,5),
(3,3),
(3,5),
(4,2),
(4,4),
(4,6)


DECLARE @ProductID INT
SET @ProductID = 4

SELECT 
	p.ProductID,
	p.ProductName,
	q.QuestionID,
	q.QuestionText
FROM 
	#Product_questions pq
	JOIN #products p
		ON pq.ProductID = p.ProductID
	JOIN #questions q
		ON pq.QuestionID = q.QuestionID
WHERE 
	pq.ProductID = @ProductID

Notice that #product_questions is the bridge table and that it has a compound primary key, comprised of both the productid and the questionid.

Changing the @ProductID variable causes the query to display, not only the correct product but also the questions that apply to that product.

HTH,

Jason

March 27th, 2015 12:59am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics