SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL JOIN PROBLEM
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yonkouturko
Yak Posting Veteran

Philippines
57 Posts

Posted - 05/16/2013 :  22:23:22  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 05/17/2013 :  00:29:06  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
57 Posts

Posted - 05/17/2013 :  00:52:20  Show Profile  Reply with Quote
@VISAKH16 thanks for your reply..
your idea is good but do you have a sample for JOINS?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/17/2013 :  00:55:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/17/2013 :  00:58:40  Show Profile  Reply with Quote
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

Edited by - visakh16 on 05/17/2013 00:59:24
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/17/2013 :  01:05:50  Show Profile  Reply with Quote
--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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/17/2013 :  01:15:33  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/17/2013 :  01:59:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000