• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

2nd query based on the result of the first query ...

hi there; my actual query is much more involved than this, so i'm going to first break it down into a very simple example.  from there, if i'm still having issues, then i'll post the whole thing :)

what i'd like to do is, in my stored procedure, write a query that will produce a list of ID's.  then, in the same procedure, i would like to feed each of those ID's into a seperate query and have those results returned (i.e. binded) to my datagrid on my .aspx page.

for example:  lets say i have

SELECT fldListingID FROM tblListing WHERE fldPrice > @price

this will result in, say, 15 fldListingID's.

then, i want to do a new query based on each fldListingID returned from the first query.

SELECT fldPhotoID FROM tblPhoto WHERE fldListingID = <the listing ID's from the first query>

i think that's pretty self explanatory and fairly straightforward to do.  i'm just not sure of the correct syntax.  

again, this is just a simple example, but i think if i'm shown how, i can apply it to my own procedure.

thanks all.
0
vbnewbie01
Asked:
vbnewbie01
  • 3
  • 3
  • 2
2 Solutions
 
MannaraprayilCommented:
Hi

SELECT fldPhotoID
FROM tblPhoto WHERE
fldListingID IN ( SELECT fldListingID FROM tblListing WHERE fldPrice > @price )

Will get the final result.

Thanks
0
 
MannaraprayilCommented:
If you dont need to use a sub query then the following can be used. This uses an INNER JOIN.

SELECT P.fldPhotoID
FROM tblPhoto P INNER JOIN tblListing L
ON P.fldListingID = L.fldListingID
WHERE L.fldPrice > @price
0
 
rafranciscoCommented:
Here's another way of doing this:

SELECT fldPhotoID
FROM tblPhoto
WHERE EXISTS (SELECT 'X' FROM tblListingID
                        WHERE tblPhoto.fldListingID = tblListingID.fldListingID AND
                                   tblListingID.fldPrice > @price)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
vbnewbie01Author Commented:
hi, guys, thanks for the responses.  

okay, i didn't want it to have to come to this, but that didn't work so i'll post my full queries below.  also, i've increased the points from 200 to 300.


this first query will result in one or more fldListingID's

SELECT     tblListing.fldListingID, tblListing.fldListingDescription, tblRealityListing.fldMLS, a_users.fullname, tblListing.fldListingVisibilityStatus,
                      tblRealityListing.fldPrice

FROM         tblListing INNER JOIN
                      a_users ON tblListing.fldClientID = a_users.fldAuto LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID


WHERE     (@listingID IS NULL OR tblListing.fldListingID = @listingID)
      AND (@keyword IS NULL OR tblListing.fldListingDescription LIKE '%' + @keyword + '%')
      AND (@MLS IS NULL OR tblRealityListing.fldMLS = @MLS)
      AND (@clientSpell IS NULL OR a_users.fullname LIKE N'%' + @clientSpell + '%')
      AND (@priceEquals IS NULL OR tblRealityListing.fldPrice = @priceEquals)
      AND (tblListing.fldListingVisibilityStatus = 1)




this second query currently returns results based on a single fldListingID

SELECT     tblListing.fldListingID, MIN(tblListingPhotoLink.fldSortOrder), MIN(tblListingPhotoLink.fldPhotoID) AS fldPhotoName, MIN(tblExtension.fldExtension),
                      tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice, tblListing.fldListingTitle,
                      tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
FROM         tblCity INNER JOIN
                      tblListing ON tblCity.fldCityID = tblListing.fldCityID LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID LEFT OUTER JOIN
                      tblPhoto INNER JOIN
                      tblListingPhotoLink ON tblPhoto.fldPhotoID = tblListingPhotoLink.fldPhotoID INNER JOIN
                      tblExtension ON tblPhoto.fldPhotoExtensionID = tblExtension.fldExtensionID ON tblListing.fldListingID = tblListingPhotoLink.fldListingID
GROUP BY tblListing.fldListingID, tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice,
                      tblListing.fldListingTitle, tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
HAVING      (tblListing.fldListingVisibilityStatus = @listingID) AND (tblListing.fldListingID = @listingID)
ORDER BY MIN(tblListingPhotoLink.fldSortOrder)





so, the way it looks to me is i somehow need to insert the first query into @listingID as a subquery ... like this:




SELECT     tblListing.fldListingID, MIN(tblListingPhotoLink.fldSortOrder), MIN(tblListingPhotoLink.fldPhotoID) AS fldPhotoName, MIN(tblExtension.fldExtension),
                      tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice, tblListing.fldListingTitle,
                      tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
FROM         tblCity INNER JOIN
                      tblListing ON tblCity.fldCityID = tblListing.fldCityID LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID LEFT OUTER JOIN
                      tblPhoto INNER JOIN
                      tblListingPhotoLink ON tblPhoto.fldPhotoID = tblListingPhotoLink.fldPhotoID INNER JOIN
                      tblExtension ON tblPhoto.fldPhotoExtensionID = tblExtension.fldExtensionID ON tblListing.fldListingID = tblListingPhotoLink.fldListingID
GROUP BY tblListing.fldListingID, tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice,
                      tblListing.fldListingTitle, tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
HAVING      (tblListing.fldListingVisibilityStatus = 1) AND (tblListing.fldListingID =


(

SELECT     tblListing.fldListingID, tblListing.fldListingDescription, tblRealityListing.fldMLS, a_users.fullname, tblListing.fldListingVisibilityStatus,
                      tblRealityListing.fldPrice

FROM         tblListing INNER JOIN
                      a_users ON tblListing.fldClientID = a_users.fldAuto LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID


WHERE     (@listingID IS NULL OR tblListing.fldListingID = @listingID)
      AND (@keyword IS NULL OR tblListing.fldListingDescription LIKE '%' + @keyword + '%')
      AND (@MLS IS NULL OR tblRealityListing.fldMLS = @MLS)
      AND (@clientSpell IS NULL OR a_users.fullname LIKE N'%' + @clientSpell + '%')
      AND (@priceEquals IS NULL OR tblRealityListing.fldPrice = @priceEquals)
      AND (tblListing.fldListingVisibilityStatus = 1)

)

)
ORDER BY MIN(tblListingPhotoLink.fldSortOrder)





however, if i try this, the error i receive is: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

0
 
rafranciscoCommented:
SELECT     tblListing.fldListingID, MIN(tblListingPhotoLink.fldSortOrder), MIN(tblListingPhotoLink.fldPhotoID) AS fldPhotoName, MIN(tblExtension.fldExtension),
                      tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice, tblListing.fldListingTitle,
                      tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
FROM         tblCity INNER JOIN
                      tblListing ON tblCity.fldCityID = tblListing.fldCityID LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID LEFT OUTER JOIN
                      tblPhoto INNER JOIN
                      tblListingPhotoLink ON tblPhoto.fldPhotoID = tblListingPhotoLink.fldPhotoID INNER JOIN
                      tblExtension ON tblPhoto.fldPhotoExtensionID = tblExtension.fldExtensionID ON tblListing.fldListingID = tblListingPhotoLink.fldListingID
GROUP BY tblListing.fldListingID, tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice,
                      tblListing.fldListingTitle, tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
HAVING      (tblListing.fldListingVisibilityStatus = 1) AND (tblListing.fldListingID =
(
SELECT     tblListing.fldListingID
FROM         tblListing INNER JOIN
                      a_users ON tblListing.fldClientID = a_users.fldAuto LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID


WHERE     (@listingID IS NULL OR tblListing.fldListingID = @listingID)
     AND (@keyword IS NULL OR tblListing.fldListingDescription LIKE '%' + @keyword + '%')
     AND (@MLS IS NULL OR tblRealityListing.fldMLS = @MLS)
     AND (@clientSpell IS NULL OR a_users.fullname LIKE N'%' + @clientSpell + '%')
     AND (@priceEquals IS NULL OR tblRealityListing.fldPrice = @priceEquals)
     AND (tblListing.fldListingVisibilityStatus = 1)

)

)
ORDER BY MIN(tblListingPhotoLink.fldSortOrder)

I simple removed all the other columns in your subquery and left fldListingID.
0
 
vbnewbie01Author Commented:
hi, rafrancisco;

thanks for your response, however, your solution only allows returns of one record.  multiple records result in the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

any other suggestions suggestions?
0
 
vbnewbie01Author Commented:
cancel that.  i got it.  i just changed the "=" to an "IN".

please copy and paste the following code into a new comment and i'll award you the points.  thanks again.


SELECT     tblListing.fldListingID, MIN(tblListingPhotoLink.fldSortOrder), MIN(tblListingPhotoLink.fldPhotoID) AS fldPhotoName, MIN(tblExtension.fldExtension),
                      tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice, tblListing.fldListingTitle,
                      tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
FROM         tblCity INNER JOIN
                      tblListing ON tblCity.fldCityID = tblListing.fldCityID LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID LEFT OUTER JOIN
                      tblPhoto INNER JOIN
                      tblListingPhotoLink ON tblPhoto.fldPhotoID = tblListingPhotoLink.fldPhotoID INNER JOIN
                      tblExtension ON tblPhoto.fldPhotoExtensionID = tblExtension.fldExtensionID ON tblListing.fldListingID = tblListingPhotoLink.fldListingID
GROUP BY tblListing.fldListingID, tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice,
                      tblListing.fldListingTitle, tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
HAVING      (tblListing.fldListingVisibilityStatus = 1) AND (tblListing.fldListingID IN
(
SELECT     tblListing.fldListingID
FROM         tblListing INNER JOIN
                      a_users ON tblListing.fldClientID = a_users.fldAuto LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID


-- these set the parameters of the search
WHERE     (@listingID IS NULL OR tblListing.fldListingID = @listingID)
      AND (@keyword IS NULL OR tblListing.fldListingDescription LIKE '%' + @keyword + '%')
      AND (@MLS IS NULL OR tblRealityListing.fldMLS = @MLS)
      AND (@clientSpell IS NULL OR a_users.fullname LIKE N'%' + @clientSpell + '%')
      AND (@priceEquals IS NULL OR tblRealityListing.fldPrice = @priceEquals)
      AND (@priceLessThan IS NULL OR tblRealityListing.fldPrice < @priceLessThan)
      AND (@priceGreaterThan IS NULL OR tblRealityListing.fldPrice > @priceGreaterThan)
     AND (tblListing.fldListingVisibilityStatus = 1)

)

)
ORDER BY MIN(tblListingPhotoLink.fldSortOrder)
0
 
rafranciscoCommented:
SELECT     tblListing.fldListingID, MIN(tblListingPhotoLink.fldSortOrder), MIN(tblListingPhotoLink.fldPhotoID) AS fldPhotoName, MIN(tblExtension.fldExtension),
                      tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice, tblListing.fldListingTitle,
                      tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
FROM         tblCity INNER JOIN
                      tblListing ON tblCity.fldCityID = tblListing.fldCityID LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID LEFT OUTER JOIN
                      tblPhoto INNER JOIN
                      tblListingPhotoLink ON tblPhoto.fldPhotoID = tblListingPhotoLink.fldPhotoID INNER JOIN
                      tblExtension ON tblPhoto.fldPhotoExtensionID = tblExtension.fldExtensionID ON tblListing.fldListingID = tblListingPhotoLink.fldListingID
GROUP BY tblListing.fldListingID, tblRealityListing.fldMLS, tblRealityListing.fldNumBedrooms, tblRealityListing.fldSquareFootage, tblRealityListing.fldPrice,
                      tblListing.fldListingTitle, tblCity.fldCityName, tblListing.fldListingVisibilityStatus, tblExtension.fldExtension
HAVING      (tblListing.fldListingVisibilityStatus = 1) AND (tblListing.fldListingID IN
(
SELECT     tblListing.fldListingID
FROM         tblListing INNER JOIN
                      a_users ON tblListing.fldClientID = a_users.fldAuto LEFT OUTER JOIN
                      tblRealityListing ON tblListing.fldListingID = tblRealityListing.fldListingID


-- these set the parameters of the search
WHERE     (@listingID IS NULL OR tblListing.fldListingID = @listingID)
     AND (@keyword IS NULL OR tblListing.fldListingDescription LIKE '%' + @keyword + '%')
     AND (@MLS IS NULL OR tblRealityListing.fldMLS = @MLS)
     AND (@clientSpell IS NULL OR a_users.fullname LIKE N'%' + @clientSpell + '%')
     AND (@priceEquals IS NULL OR tblRealityListing.fldPrice = @priceEquals)
     AND (@priceLessThan IS NULL OR tblRealityListing.fldPrice < @priceLessThan)
     AND (@priceGreaterThan IS NULL OR tblRealityListing.fldPrice > @priceGreaterThan)
     AND (tblListing.fldListingVisibilityStatus = 1)

)

)
ORDER BY MIN(tblListingPhotoLink.fldSortOrder)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now