SQL Query - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query

Author

Topic

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2013-06-25 : 07:20:42

Hi All,
I have been struggling with this query for the last couple of days, just wondering if anyone could point me back on track, any help or advice would really be appreciated.

I have a table containing requested customer Feedback data linked directly to the Product we would like to receive feedback on, with these Products then linked to the City the take place in. The third table should contain the customer feedback details if they completed one, which is linked to the Feedback table.

I'm trying to create a query to display a record for all requested feedback(Feedback) and whether or not feedback has been completed(TripAdvisorEmail) for a Product within the same City as the City this Feedback Product is linked to.

http://www.freeimagehosting.net/newuploads/e6w7s.jpg
Thanks again

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-25 : 07:30:18

-- You can tweet the below query for your requirement...
SELECT FeedbackColumn, City_id , product_id-- list of columns
FROM d_Feedback df
JOIN d_feedback_tripAdvisorEmail tae ON df.feedback_id = tae.feedback_id
JOIN d_product dp dp.Product_id = df.Product_id

If this is not the exact solution, elaborate your requirement

--
Chandu

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2013-06-25 : 07:44:05

thanks for taking the time to reply.
I was really hoping to have an additional column that has maybe a 1 if the completed feedback is in the same city as any previously completed feedback from this customer or a 0 if not.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-25 : 07:51:15

quote:
Originally posted by tadhg88

thanks for taking the time to reply.
I was really hoping to have an additional column that has maybe a 1 if the completed feedback is in the same city as any previously completed feedback from this customer or a 0 if not.

Can you explain with sample input and expected output?
without sample data/complete table structures how can we get previous completed feedback ?
Do you have any DateTime Column ?

--
Chandu

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-25 : 12:11:38

FYI, Here are some link that can help you prepare your question with DDL, DML and Expected output. THat will help help us to help you better.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2013-06-26 : 05:50:29

Thanks for you're replies I really appreciate it, i have uploaded a pic of the results i would like to see here
http://www.freeimagehosting.net/newuploads/qhsnx.jpg

So if you look at the highlighted user tw, the last column displays whether or not the user has completed the feedback for this feedback_id(Completed), if they have not completed it(None) and i guess the difficult one where they have completed feedback within the same city, thanks again

---------------------------------------------------
-- Script
---------------------------------------------------
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#d_Feedback','U') IS NOT NULL
DROP TABLE #d_Feedback

CREATE TABLE #d_Feedback
(
[Feedback_id] [int] NOT NULL,
[User_email] NVARCHAR(15) NULL,
[Product_id] [int] NULL,
[booking_id] [int] NULL
)

IF OBJECT_ID('TempDB..#d_Product','U') IS NOT NULL
DROP TABLE #d_Product

CREATE TABLE #d_Product
(
[Product_id] [int] NOT NULL,
[city_id] [int] NULL
)

IF OBJECT_ID('TempDB..#d_Feedback_TA','U') IS NOT NULL
DROP TABLE #d_Feedback_TA

CREATE TABLE #d_Feedback_TA
(
[Feedback_id] [int] NOT NULL
)

--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY

INSERT INTO #d_Feedback
(feedback_id, User_email, Product_id, booking_id)
SELECT '39715','tw@dr.com','348','621747' UNION ALL
SELECT '39714','tw@dr.com','413','621746' UNION ALL
SELECT '39713','sm@dr.com','431','529787' UNION ALL
SELECT '39712','sm@dr.com','431','529787' UNION ALL
SELECT '39711','tw@dr.com','435','621665' UNION ALL
SELECT '39710','rf@ab.com','290','608919' UNION ALL
SELECT '39709','rf@ab.com','291','608920' UNION ALL
SELECT '39708','rf@ab.com','413','608911' UNION ALL
SELECT '39707','rf@ab.com','413','608912' UNION ALL
SELECT '39706','js@ao.com','324','596255'

INSERT INTO #d_Product
(product_id, city_id)
SELECT '290','1' UNION ALL
SELECT '291','1' UNION ALL
SELECT '324','1' UNION ALL
SELECT '348','1' UNION ALL
SELECT '364','1' UNION ALL
SELECT '413','3' UNION ALL
SELECT '431','1' UNION ALL
SELECT '435','1' UNION ALL
SELECT '456','1'

INSERT INTO #d_feedback_TA
SELECT 39715 UNION ALL
SELECT 39712 UNION ALL
SELECT 39707

SELECT df.Feedback_id, User_email, df.product_id, City_id, tae.Feedback_id Completed -- list of columns
FROM #d_Feedback df
LEFT JOIN #d_product dp ON dp.Product_id = df.Product_id
LEFT JOIN #d_feedback_TA tae ON df.feedback_id = tae.feedback_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 05:58:34

Sorry but code posted doesnt have any columns by which you can group the rows together to check if feedback is completed

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

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2013-06-26 : 08:39:53

the feedback has been completed if there is a record in the #d_Feedback_TA table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 09:18:48

[code]
SELECT user_email,
CASE WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city' ELSE 'Feedback in different city' END
FROM #d_Feedback f
INNER JOIN #d_Product p
ON p.Product_id = f.Product_id
INNER JOIN #d_feedback_TA ta
ON ta.feedback_id = f.feedback_id
[/code]

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

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2013-06-26 : 11:06:49

thanks its closer to what i need with a left join on TA table but it still doesnt return none in this column if there is no feedback completed, any ideas??

SELECT user_email,
CASE WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city' ELSE 'Feedback in different city' END
FROM #d_Feedback f
INNER JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2013-06-26 : 11:17:17

thanks but that just gives the same results unfortunately

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 11:19:16

[code]
SELECT user_email,
CASE WHEN ta.feedback_id IS NULL
THEN 'None'
WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city'
ELSE 'Feedback in different city'
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id
[/code]

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-26 : 11:24:22

Try this:
[CODE]
SELECT p.product_id, f.Feedback_id, user_email,
CASE WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city' ELSE 'Feedback in different city' END
FROM #d_Product p
LEFT JOIN #d_Feedback f ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id

[/CODE]

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2013-06-26 : 12:04:29

thanks this is very close to what i need but as you can see here
http://www.freeimagehosting.net/newuploads/qhsnx.jpg
if you focus on the user 'tw@dr.com' you can see the 3 different results this user can see in the last column
"Completed" if feedback_id in #d_feedback_TA - 39715
"SameCity" if feedback completed for same user within the same city - 39711
"None" if no feedback is completed, no record in #d_feedback_TA - 39714

SELECT f.feedback_id, city_id, user_email,
CASE WHEN ta.feedback_id IS NULL
THEN 'None'
WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city'
ELSE 'Feedback in different city'
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 13:00:42

Sounds like this then


SELECT f.feedback_id, city_id, user_email,
CASE 
WHEN ta.feedback_id IS NOT NULL
THEN 'Completed'  
WHEN p.city_id = MAX(CASE WHEN ta.feedbackid IS NOT NULL THEN city_id END) OVER (PARTITION BY User_Email) THEN 'SameCity'
WHEN ta.feedback_id IS NULL 
THEN 'None'
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-26 : 13:30:56

Try this:
[CODE]

;WITH
CTE1 AS
(SELECT city_id, user_email
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id
WHERE ta.feedback_id IS NOT NULL)
SELECT f.feedback_id, p.city_id,f.user_email,
CASE
WHEN ta.feedback_id IS NOT NULL
THEN 'Complete'
WHEN ta.feedback_id IS NULL and p.city_id = C1.city_id
THEN 'Feedback in same city'
WHEN ta.feedback_id IS NULL and C1.city_id IS NOT NULL and
p.city_id <> C1.city_id
THEN 'Feedback in different city'
ELSE 'None'
END as Comment
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id
LEFT JOIN CTE1 C1 on C1.User_email = f.User_email
ORDER BY f.User_email

[/CODE]

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2013-06-27 : 09:40:59

Spot on this is exactly what i was looking for, thanks a million for taking the time to look into this for me I really appreciate it, I have never used the the OVER or PARTITION keywords before, I would imagine there's thousands of tutorials online but is there any you would recommend to get a good understanding of how they work and can be correctly used??

thanks to you too MuMu88 i appreciate the effort and it was very close to what i was looking for thank you!!

quote:
Originally posted by visakh16

Sounds like this then

SELECT f.feedback_id, city_id, user_email,
CASE
WHEN ta.feedback_id IS NOT NULL
THEN 'Completed'
WHEN p.city_id = MAX(CASE WHEN ta.feedbackid IS NOT NULL THEN city_id END) OVER (PARTITION BY User_Email) THEN 'SameCity'
WHEN ta.feedback_id IS NULL
THEN 'None'
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 09:49:10

quote:
Originally posted by tadhg88

Spot on this is exactly what i was looking for, thanks a million for taking the time to look into this for me I really appreciate it, I have never used the the OVER or PARTITION keywords before, I would imagine there's thousands of tutorials online but is there any you would recommend to get a good understanding of how they work and can be correctly used??

thanks to you too MuMu88 i appreciate the effort and it was very close to what i was looking for thank you!!

quote:
Originally posted by visakh16

Sounds like this then

SELECT f.feedback_id, city_id, user_email,
CASE
WHEN ta.feedback_id IS NOT NULL
THEN 'Completed'
WHEN p.city_id = MAX(CASE WHEN ta.feedbackid IS NOT NULL THEN city_id END) OVER (PARTITION BY User_Email) THEN 'SameCity'
WHEN ta.feedback_id IS NULL
THEN 'None'
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id

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

Welcome

I've learned these techniques mostly by following posts here and i use BOL/MSDN for reference

http://msdn.microsoft.com/en-us/library/ms189461.aspx

------------------------------------------------------------------------------------------------------
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