May 11, 2009 at 8:21 am
I'm trying to select all of the users Emails who have NO image on there listing.
I tried this:
SELECT DISTINCT email FROM Member usr
INNER JOIN Selling_Photo pho
ON usr.Member_Id = pho.SellingPhoto_Id
WHERE Roles = 'PREMIUM'
but it isnt working correctly, It seems to be selecting both people who have and do not have images on there listings.
The following is the column layout for the Member table:
Member_Id | Login_Id | Login_Pwd | First_Name | Last_Name | Address1 | Address2 | City | State | County | ZipCode | Email | Phone | Realtor | Company_Name | Company_Logo | Active | Created_Date | Roles | Enable_Messaging | Email_Freq | Email_Day_Gap | Old_Member_Id | Last_Login_Date | Last_Email_Date | UnSubscribe | Buying_City | Buying_State | Selling_Price | IsUpdate | IsNewMember | ML_Optin
The following is the column layout for the Selling_Photo table:
SellingPhoto_Id | Listing_Id | Added_Date | IsPrimary | File_Path
May 11, 2009 at 8:23 am
the inner join would only show members with records int he detail table...you want a left outer join instead:
SELECT DISTINCT email
FROM Member usr
LEFT OUTER JOIN Selling_Photo pho
ON usr.Member_Id = pho.SellingPhoto_Id
WHERE Roles = 'PREMIUM'
AND pho.SellingPhoto_Id IS NULL
Lowell
May 11, 2009 at 1:09 pm
I tried this query, but its still not pulling the correct information. I think ive got the wrong columns or something. Because its pulling 7k people most of which actually do have images on there listings..so im a bit lost
I THINK the way this database is setup, the table Selling_Photo is only people who HAVE images on there listings. Is there any way I could maybe compare those ID's to Members and get the list of people who do not have photo's?
EDIT
I just found this in our database under Stored procedures its called Get_ListingPix
USE [OHT]
GO
/****** Object: StoredProcedure [dbo].[Get_ListingPix]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Get_ListingPix]
@Listing_Id as bigint,
@mid bigint
AS
SET NOCOUNT ON
SELECT 'pid' = sp.sellingphoto_id, 'lid' = sp.Listing_Id,sp.IsPrimary FROM Selling_photo sp,Listing 1
WHERE l.listing_id = sp.listing_id AND
l.listing_id = @listing_id AND
l.member_id = @mid
maybe this will work if converted to a SQL query?
May 11, 2009 at 8:14 pm
I can't see why Lowell's solution does not work, unless everyone, regardless of whether or not they have a photo present, exists in the table selling_photo.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 11, 2009 at 8:25 pm
Greg Snidow (5/11/2009)
I can't see why Lowell's solution does not work, unless everyone, regardless of whether or not they have a photo present, exists in the table selling_photo.
I'm with Greg, the only thing i thought wierd was i expected the member_ID would have the same name in both tables:
ON usr.Member_Id = pho.SellingPhoto_Id --<--should that be usr.Member_Id = pho.Member_Id instead?
without the CREATE TABLE schema of both tables, all we can do is assume the example inner join was correct.
Lowell
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy