CODEWITHSUNDEEP
sqlsql-server
user850234
user850234

Reputation: 3463

Join on same table in sql server

I have got a table product and the data is as shown below.

id     type     account_id    start_date    end_date
1      APPLE    100           2015-01-27    2015-02-25
2      MANGO    100           2015-01-25    2015-02-25
3      GRAPES   100           2015-02-25    2015-04-25
4      MANGO    100           2015-02-25    2015-04-25
5      APPLE    101           2015-03-25    2015-04-25
6      MANGO    101           2015-03-25    2015-04-25

What I want to achieve here is filter out all the records of type MANGO based on the account_id and add the other types associated with the same account based on end_date.

The result should look like as below.

id     type     account_id    start_date    end_date     other_types
2      MANGO    100           2015-01-25    2015-02-25   APPLE
4      MANGO    100           2015-02-25    2015-04-25   GRAPES
6      MANGO    101           2015-03-25    2015-04-25   APPLE

I have tried the below query on MySQL and it works but not able to get it working in SQL server. Any help would be great. The above result is all that I need to achieve in SQL server.

SELECT yt1.`id`, yt1.`type`, yt1.`account_id`, yt1.`start_date`, yt1.`end_date`, yt2.`type` AS other_types
FROM `product` yt1, `product` yt2
WHERE 
yt1.`account_id` = yt2.`account_id` AND
yt1.`type` = 'MANGO' AND
yt1.`end_date` = yt2.`end_date`
GROUP BY yt1.`id`

Upvotes: 1

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is the equivalent query in SQL Server:

SELECT yt1.id, yt1.type, yt1.account_id, yt1.start_date, yt1.end_date,
       yt2.type AS other_types
FROM product yt1 JOIN
     product yt2
     ON yt1.account_id = yt2.account_id AND
        yt1.end_date = yt2.end_date
WHERE yt1.type = 'MANGO' AND yt2.type <> 'MANGO';

Your MySQL query was not really working. It was choosing an arbitrary value for the other types, which on your sample data just happened not to be "Mango".

Actually, this will work on both databases.

Upvotes: 3

Related Questions