Select DISTINCT using EXISTS (How to use Columns within the select statement)

SELECT distinct id from lists where exists (Select User.Username, User.FName, User.LName, User.email, Lists.ZoneID FROM User INNER JOIN Lists ON User.id = Lists.ID WHERE Lists.ZoneID=?)

try this

Select distinct id FROM User INNER JOIN Lists ON User.id = Lists.ID WHERE Lists.ZoneID=?

I cannot do that, I need the other columns.
Please re-read my post

Why do you need EXISTS, then?

getUser.commandtext="SELECT distinct lists.id User.Username, User.FName, User.LName, User.email, Lists.ZoneID from lists INNER JOIN User ON User.id = Lists.ID"

Select allOpen in new window

That does not do an distinct, sorry.
I need to get a single record, for example.

id = 1
id = 2
id = 3
id = 2
id = 3

When you do it like you have preposed, it will return all records.
Doing it as I have done it, it will return only the Distinct # only.
ID = 1, 2, 3

Your code does work, however, it does not return all the records.

Example

ZoneID = 1

The following users belong to "1"
Users : 1, 2, 3

Now, if we do
ZoneID = 6

The following users belong to "6"
Users : 1, 3

In the case of your example, of which I had already tried a while back, it will only display the users that belong to that ZoneID.
To where, the code that I supplied, will get all the users, that are in the list.
Which is what I am needing, I need all the users, DISTINCT to the list.
And then I can "hopefully" complete the rest of the script.

Thanks guys.
Carrzkiss

I can't understand, can you please post your actual query, you are using?

If you want all users regardless of zoneID then you can remove the where condition.

Try This:

 

SELECT id from lists where exists (Select User.Username, User.FName, User.LName, User.email, Lists.ZoneID FROM User INNER JOIN Lists ON User.id = Lists.ID WHERE Lists.ZoneID=?) AND ZoneID = ?

Select allOpen in new window

if you all the users, DISTINCT to the list.
then this query is correct

Select Distinct Lists.ID,User.Username, User.FName, User.LName, User.email, Lists.ZoneID FROM User INNER JOIN Lists ON User.id = Lists.ID WHERE Lists.ZoneID=?

I will get back with everyone here, once I get some rest, and clear my head.
As I am getting frustrated with what I am trying to do, and it is not working.
And right now, it is hard to explain exactly what I am doing, and providing all the code for what I am doing, I am unable to do.

@rush.
Once I get my mindset right, I will give you a better explanation.

carrzkiss,

Once you've had a chance to recharge your batteries, please post a few example records from the two tables, and the output you would expect given that sample input.

Patrick

Select User.Username, User.FName, User.LName, User.email, Lists.ZoneID FROM User INNER JOIN Lists ON User.id = Lists.ID WHERE Lists.ZoneID=? group by ID

OK, rest, and I have a better code example, that "almost" works.

<%
Set getMemEmail = Server.CreateObject("ADODB.Command")
getMemEmail.ActiveConnection=objConn
getMemEmail.Prepared = true
getMemEmail.commandtext="SELECT Zones.ZoneID, MIN(Zones.ZoneName) AS ZoneName, MIN(Media.MedName) AS MedName, MIN(Media.MedID) AS MedID, MIN(Media.Closed) AS Closed FROM Zones INNER JOIN Media ON Zones.ZoneID = Media.ZoneID GROUP BY Zones.ZoneID HAVING (MIN(Media.Closed) = 0)"
set rsZone = getMemEmail.execute
while not rsZone.eof
if not rsZone.eof then
ZoneID = rsZone("ZoneID")
end if


Set getUser = Server.CreateObject("ADODB.Command")
getUser.ActiveConnection=objConn
getUser.Prepared = true
getUser.commandtext="SELECT MIN(Lists.ZoneID) AS Zone, AskUser.askemail, MIN(AskUser.askid)as askid FROM Lists INNER JOIN AskUser ON Lists.AskID = AskUser.askid GROUP BY AskUser.askemail, Lists.ZoneID HAVING Lists.ZoneID=?"
getUser.Parameters.Append getUser.CreateParameter("@ZoneID", adInteger, adParamInput, , ZoneID)
set rsUser = getUser.execute
if not rsUser.eof then
Email = rsUser("askemail")
askid = rsUser("askid")
end if
%>

<%=ZoneID%> - <%=askid%> - <%=Email%><br />
<%
rsZone.movenext
wend
%>

Select allOpen in new window

Now, the rsZone returns the following example
(These are the ZoneID only)
<%=rsZone("ZoneID")%>
1
2
6

Now, the above rsZone was originally
13 ZoneID=1
1 ZoneID=2
1 ZoneID=6

(Thanks AngelII for the link, and rushShah for the example,
even though this was different than the original posted code example.
How a little rest helps to ease the mindset)


Now. When I run a Query against the zoneid's against the rsUser query, it gives me double, if a user is identified with more than 1 zoneid.

The results from this will be.

zoneid - askid - email
1         6      emailaddress (6)
2         6      emailaddress (6)
6         1      emailaddress (1)

As you can see in the above example, the askid=6, is duplicated.
I need it to only display each email address once, regardless of how many zones they are identified with.

Wish I could have posted this to begin with.
Hope this will help you all assist with this issue.

Once again, thanks AngelII and Rush.
----

Since I am doing a LOOP from the rsZone, around the rsUser.
I am not real sure how to check and see, rather the user has been displayed yet or not.
The only thing that I can think of is, to create a table, and once the User (askid) as been displayed, to insert it into the table, and then run a check against the table.
However, that seems to be a little to much

I am just not sure.
Any and all idea's are welcome.

Thanks all for sticking in here with me, even though last night, my mind was a little wiggy :)

OK, for an update.
I created a new table, and am looping through, and inserting the askid into the new table, then I am looking at the new table, and displaying the unique users information that relations with that table.

So, right now, it is giving me each user, regardless of how many zones the user is identified with.

So, that may not be the best way to do it, however, it is working so far.
I just have to check the rest of it, and make sure that everything is going to work properly with the rest of the code.

Will update when I progress further.

IT WORKS!!!!!!!!!!!!!!!!

OK, to wrap this us.
Thanks AngelII for the link, and rushShah
I did as you  2 suggested, to get the unique records returned.
Once I did that, for both of the queries.
I created a table, inserted the unique id of the user.
Then I checked the Table, to see if the ID exist, and then matched it with the User table, to get the name and other information.
Then processed the rest of the page.

This is completed.

Thanks guys.
Carrzkiss

Thanks guys