Stormchild69
asked on
Using alternative to LIMIT command and using INNER JOIN in MS SQL Query
Hi,
I have the follwoing SQL:
SELECT a.ContactID, a.CompanyID, a.ContactPrefix, a.ContactFName, a.ContactLName, a.ContactSurfix, a.ContactJobTitle, a.ContactPhone, a.ContactEmail,
b.CompanyName, b.CompanyPhone, b.CompanySegment, a.ContactMobile, a.ContactCustRelat, a.ContactAccntMng, a.ContactNotes,
b.CompanyFascimile, b.CompanyWeb, a.ContactRcdMng
FROM Contact a INNER JOIN
Company b ON a.CompanyID = b.CompanyID
WHERE (b.CompanySegment LIKE 'Finance') AND (a.ContactRemoved IS NULL) AND (b.CompanyRemoved IS NULL)
ORDER BY a.ContactID
This works fine but I also want to add a 'LIMIT' function.
I have the following code which performs the alternative to MySQL LIMIT command.
SELECT contactid, contactlname
FROM (SELECT TOP 15 contactid, contactlname
FROM (SELECT TOP 45 contactid, contactlname
FROM contact
ORDER BY contactid ASC) AS newtbl
ORDER BY contactid DESC) newtbl2
ORDER BY contactid
I need to use get the LIMIT capability of the second query into the first.
The reason for this is that I am using AJAX to call SQL lines on demand.
Any help would be really appreciated.
Thank you,
SC69
I have the follwoing SQL:
SELECT a.ContactID, a.CompanyID, a.ContactPrefix, a.ContactFName, a.ContactLName, a.ContactSurfix, a.ContactJobTitle, a.ContactPhone, a.ContactEmail,
b.CompanyName, b.CompanyPhone, b.CompanySegment, a.ContactMobile, a.ContactCustRelat, a.ContactAccntMng, a.ContactNotes,
b.CompanyFascimile, b.CompanyWeb, a.ContactRcdMng
FROM Contact a INNER JOIN
Company b ON a.CompanyID = b.CompanyID
WHERE (b.CompanySegment LIKE 'Finance') AND (a.ContactRemoved IS NULL) AND (b.CompanyRemoved IS NULL)
ORDER BY a.ContactID
This works fine but I also want to add a 'LIMIT' function.
I have the following code which performs the alternative to MySQL LIMIT command.
SELECT contactid, contactlname
FROM (SELECT TOP 15 contactid, contactlname
FROM (SELECT TOP 45 contactid, contactlname
FROM contact
ORDER BY contactid ASC) AS newtbl
ORDER BY contactid DESC) newtbl2
ORDER BY contactid
I need to use get the LIMIT capability of the second query into the first.
The reason for this is that I am using AJAX to call SQL lines on demand.
Any help would be really appreciated.
Thank you,
SC69
ASKER
Hello,
Many thanks but I got an error on line 11 (second ORDER from the bottom). I added bach 'AS newtbl' and it ran but produced a mess of duplicated contactids. Contactid is unique in that there should only be one per contact and I would hope that contactid 30-45 would be displayed. This is what I tried
SELECT a.ContactID, a.CompanyID, a.ContactPrefix, a.ContactFName, a.ContactLName, a.ContactSurfix, a.ContactJobTitle, a.ContactPhone, a.ContactEmail,
b.CompanyName, b.CompanyPhone, b.CompanySegment, a.ContactMobile, a.ContactCustRelat, a.ContactAccntMng, a.ContactNotes,
b.CompanyFascimile, b.CompanyWeb, a.ContactRcdMng
FROM Contact a INNER JOIN
Company b ON a.CompanyID = b.CompanyID
WHERE (b.CompanySegment LIKE 'Finance') AND (a.ContactRemoved IS NULL) AND (b.CompanyRemoved IS NULL) AND (a.ContactID IN
(SELECT TOP 15 a.contactid
FROM (SELECT TOP 45 a.contactid
FROM contact a
ORDER BY a.contactid ASC) AS newtbl
ORDER BY a.contactid DESC))
ORDER BY a.ContactID
Many thanks for trying,
SC69
Many thanks but I got an error on line 11 (second ORDER from the bottom). I added bach 'AS newtbl' and it ran but produced a mess of duplicated contactids. Contactid is unique in that there should only be one per contact and I would hope that contactid 30-45 would be displayed. This is what I tried
SELECT a.ContactID, a.CompanyID, a.ContactPrefix, a.ContactFName, a.ContactLName, a.ContactSurfix, a.ContactJobTitle, a.ContactPhone, a.ContactEmail,
b.CompanyName, b.CompanyPhone, b.CompanySegment, a.ContactMobile, a.ContactCustRelat, a.ContactAccntMng, a.ContactNotes,
b.CompanyFascimile, b.CompanyWeb, a.ContactRcdMng
FROM Contact a INNER JOIN
Company b ON a.CompanyID = b.CompanyID
WHERE (b.CompanySegment LIKE 'Finance') AND (a.ContactRemoved IS NULL) AND (b.CompanyRemoved IS NULL) AND (a.ContactID IN
(SELECT TOP 15 a.contactid
FROM (SELECT TOP 45 a.contactid
FROM contact a
ORDER BY a.contactid ASC) AS newtbl
ORDER BY a.contactid DESC))
ORDER BY a.ContactID
Many thanks for trying,
SC69
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is this SQL Server 2005?
ASKER
Hi,
I am running SQL Server 2000.
Thank you for your help.
I added 'AS newtbl' and then your solution worked as required,
Thanks,
SC69
I am running SQL Server 2000.
Thank you for your help.
I added 'AS newtbl' and then your solution worked as required,
Thanks,
SC69
While I appreciate the points, you appear to have awarded me in error. See here:
I accepted the wrong answer. Now what?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi17
I accepted the wrong answer. Now what?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi17
SELECT a.ContactID, a.CompanyID, a.ContactPrefix, a.ContactFName, a.ContactLName, a.ContactSurfix, a.ContactJobTitle, a.ContactPhone, a.ContactEmail,
b.CompanyName, b.CompanyPhone, b.CompanySegment, a.ContactMobile, a.ContactCustRelat, a.ContactAccntMng, a.ContactNotes,
b.CompanyFascimile, b.CompanyWeb, a.ContactRcdMng
FROM Contact a INNER JOIN
Company b ON a.CompanyID = b.CompanyID
WHERE (b.CompanySegment LIKE 'Finance') AND (a.ContactRemoved IS NULL) AND (b.CompanyRemoved IS NULL)
AND a.ContactID IN
(SELECT TOP 15 contactid
FROM (SELECT TOP 45 contactid
FROM contact
ORDER BY contactid ASC)
ORDER BY contactid DESC)
ORDER BY a.ContactID