SQL JOIN PROBLEM - 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 2012 Forums

Transact-SQL (2012)

SQL JOIN PROBLEM

Author

Topic

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-05-16 : 22:23:22

SELECT ReferenceNumber,
(SELECT TOP (1) MODIFIED_DATE
FROM Table_FOLLOW_UP
WHERE (REFERENCENUMBER = Table_Customer_Record.ID)
ORDER BY ID DESC) AS LAST_FOLLOW_UP_DATE,
(SELECT TOP (1) REMARKS_STATUS
FROM Table_FOLLOW_UP AS Table_FOLLOW_UP_1
WHERE (REFERENCENUMBER = Table_Customer_Record.ID)
ORDER BY ID DESC) AS LAST_FOLLOW_UP_STATUS,
Company_Name, Contact_Person,
Email_Address, Line_of_Business, Telephone_No, Mobile_No, Fax_No, Address, Area,
Sales_Representative,Credit_Limit, Remaining_Balance, Website, Terms, Mode_of_payment,
Type_of_payment, Local_Service, Site_Service, Member, Sales_End_User, Sales_Corporate,
ID, Internet_Cafe, ACTIVE
FROM Table_Customer_Record
ORDER BY ID

NOW I HAVE Two(2) tables.. CUSTOMER RECORDS TABLE and FOLLOW UP TABLE
The goal of this sql query is to get Customer Records WITH the LAST FOLLOW DONE
for each Customer RECORDS..

actually that query did it but my problem was... The Query Result is Slow TO display
in my vb.net application now one of my friends did tell me that there is another way of getting my goal done with out using this type of query(because its slow to get records, cause my query has PROCESS within PROCESS thats why its slow and that is his explanation of things)

he said to me that i must use JOIN(or OUTER JOIN Specifically)
now i've tried LEFT OUTER JOIN but My CUSTOMER RECORDS is Duplicated...

if anyone knows some idea on how to use JOINS properly please lend me your Intelligence on this topic...

thank you Very much!!! any idea or help is very much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 00:29:06

[code]
SELECT ReferenceNumber,
t.MODIFIED_DATE AS LAST_FOLLOW_UP_DATE,
t.REMARKS_STATUS AS LAST_FOLLOW_UP_STATUS,
Company_Name, Contact_Person,
Email_Address, Line_of_Business, Telephone_No, Mobile_No, Fax_No, Address, Area,
Sales_Representative,Credit_Limit, Remaining_Balance, Website, Terms, Mode_of_payment,
Type_of_payment, Local_Service, Site_Service, Member, Sales_End_User, Sales_Corporate,
ID, Internet_Cafe, ACTIVE
FROM Table_Customer_Record c
OUTER APPLY (SELECT TOP 1 MODIFIED_DATE,REMARKS_STATUS
FROM Table_FOLLOW_UP
WHERE REFERENCENUMBER = Table_Customer_Record.ID
ORDER BY ID DESC)t

ORDER BY ID
[/code]

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

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-05-17 : 00:52:20

@VISAKH16 thanks for your reply..
your idea is good but do you have a sample for JOINS?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 00:55:38

You can refer this link for basic Joins..
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

If you post sample data of your tables and also expected output, then it will be useful to get quick and optimized code

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 00:58:40

joins approach will require two additional joins to be added to get latest. thats why i prefer APPLY


SELECT c.ReferenceNumber,
d.MODIFIED_DATE AS LAST_FOLLOW_UP_DATE,
d.REMARKS_STATUS AS LAST_FOLLOW_UP_STATUS,
Company_Name, Contact_Person,
Email_Address, Line_of_Business, Telephone_No, Mobile_No, Fax_No, Address, Area,
Sales_Representative,Credit_Limit, Remaining_Balance, Website, Terms, Mode_of_payment, 
Type_of_payment, Local_Service, Site_Service, Member, Sales_End_User, Sales_Corporate,
ID, Internet_Cafe, ACTIVE
FROM Table_Customer_Record c
LEFT JOIN (SELECT f.REFERENCENUMBER,f.MODIFIED_DATE,f.REMARKS_STATUS
           FROM Table_FOLLOW_UP f
           INNER JOIN (
                       SELECT REFERENCENUMBER,MAX(ID) AS MaxID
                       FROM Table_FOLLOW_UP
                       GROUP BY REFERENCENUMBER
                       )f1
                    ON f1.REFERENCENUMBER = f.REFERENCENUMBER
                    AND f1.MaxID = f.ID
          )d
        ON d.REFERENCENUMBER = c.ID
ORDER BY c.ID

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 01:05:50

--May be this?


SELECT 
	ReferenceNumber,
	A.LAST_FOLLOW_UP_DATE,
	A.LAST_FOLLOW_UP_STATUS,
	Company_Name,
	Contact_Person,
	Email_Address,
	Line_of_Business, 
	Telephone_No, 
	Mobile_No, 
	Fax_No, 
	Address, 
	Area,
	Sales_Representative,
	Credit_Limit, 
	Remaining_Balance, 
	Website, 
	Terms, 
	Mode_of_payment, 
	Type_of_payment, 
	Local_Service, 
	Site_Service, 
	Member, 
	Sales_End_User, 
	Sales_Corporate,
	ID, 
	Internet_Cafe, 
	ACTIVE
FROM Table_Customer_Record TCR
LEFT JOIN (SELECT REFERENCENUMBER, MODIFIED_DATE, REMARKS_STATUS,
				  ROW_NUMBER() OVER(PARTITION BY REFERENCENUMBER ORDER BY ID DESC) AS RN
			FROM Table_FOLLOW_UP
		 ) A 
ON A.REFERENCENUMBER = TCR.ID

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 01:15:33

quote:

Originally posted by bandi

--May be this?


SELECT 
	ReferenceNumber,
	A.LAST_FOLLOW_UP_DATE,
	A.LAST_FOLLOW_UP_STATUS,
	Company_Name,
	Contact_Person,
	Email_Address,
	Line_of_Business, 
	Telephone_No, 
	Mobile_No, 
	Fax_No, 
	Address, 
	Area,
	Sales_Representative,
	Credit_Limit, 
	Remaining_Balance, 
	Website, 
	Terms, 
	Mode_of_payment, 
	Type_of_payment, 
	Local_Service, 
	Site_Service, 
	Member, 
	Sales_End_User, 
	Sales_Corporate,
	ID, 
	Internet_Cafe, 
	ACTIVE
FROM Table_Customer_Record TCR
LEFT JOIN (SELECT REFERENCENUMBER, MODIFIED_DATE, REMARKS_STATUS,
				  ROW_NUMBER() OVER(PARTITION BY REFERENCENUMBER ORDER BY ID DESC) AS RN
			FROM Table_FOLLOW_UP
		 ) A 
ON A.REFERENCENUMBER = TCR.ID
AND A.RN = 1

--
Chandu

this will still return duplicates unless you put additional condition as above
I didnt suggest this as I'm not sure OP is on SQL 2005 or above seeing the reluctance to use APPLY operator previously

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 01:59:06

Hi visakh,
I forgot to put that additional condition in ON clause...
I think OP can also use FIRST_VALUE() or LAST_VALUE() functions in MSSQL 2012 to get first/last values based on window definitions...

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources