Help needed with a query

Hi everyone,
I am implementing a dating club as a project for school.
I have a table called "Dates". It has the columns:
DateID, FirstMember, SecondMember, DateToMeet, Place
The table "MEMBERS" contains many things and of course:
MemberID , Username
Now, I want to make a query where I can join the two tables and display:
USERNAME1, USERNAME 2 (the 2 members that are going to meet), DateToMeet and
PLACE.
But the problem is tha both "FirstMember" and "SecondMember" of the table
"Dates" refer to he same column (namely "MemberID" ) of the table MEMBERS.
How can make such a query?
ThanksYou use a technique called aliasing where you give each logical table in
your query a unique name to which you will refer to it by in the rest of the
query, you can then refer to the same table multiple times.
SELECT Dates.DateToMeet, Dates.Place, First.UserName as Member1,
Second.UserName as Member2
FROM Dates
JOIN Members as First ON First.ID=Dates.FirstMember
JOIN Members as Second ON Second.ID=Dates.SecondMember
the 'as' is optional but it helps you to see where aliasing is used.
Mr Tea
http://mr-tea.blogspot.com
"Silver" <argytzak@.med.auth.gr> wrote in message
news:ct03t1$8p8$1@.nic.grnet.gr...
> Hi everyone,
> I am implementing a dating club as a project for school.
> I have a table called "Dates". It has the columns:
> DateID, FirstMember, SecondMember, DateToMeet, Place
> The table "MEMBERS" contains many things and of course:
> MemberID , Username
> Now, I want to make a query where I can join the two tables and display:
> USERNAME1, USERNAME 2 (the 2 members that are going to meet), DateToMeet
> and
> PLACE.
> But the problem is tha both "FirstMember" and "SecondMember" of the table
> "Dates" refer to he same column (namely "MemberID" ) of the table MEMBERS.
> How can make such a query?
> Thanks
>|||Silver wrote:
> Hi everyone,
> I am implementing a dating club as a project for school.
> I have a table called "Dates". It has the columns:
> DateID, FirstMember, SecondMember, DateToMeet, Place
> The table "MEMBERS" contains many things and of course:
> MemberID , Username
> Now, I want to make a query where I can join the two tables and
> display: USERNAME1, USERNAME 2 (the 2 members that are going to
> meet), DateToMeet and PLACE.
> But the problem is tha both "FirstMember" and "SecondMember" of the
> table "Dates" refer to he same column (namely "MemberID" ) of the
> table MEMBERS.
> How can make such a query?
> Thanks
You can join to the same table as many times as you want, using table
aliases to distinguish them (this example is untested, but should give you
the basic idea):
select
u1,UserName Dater1,
u2.Username Dater2,
DateToMeet,
Place
FROM Dates d
inner join members u1 on d.FirstMember=u1.MemberID
inner join members u2 on d.SecondMember=u1.MemberID
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thank you both for the quick reply.
I tried both ways and managed to get results. The problem is, I get mopre
results than I should. It's like somewhere happens a cartesion product.
My table Dates is like this:
DateID - User1 - User2 - DateToMeet - Place
----
--
1 1 2 1956-10-30 22:00:00.000 Plat.
Aristotelous
2 1 3 1996-10-30 22:00:00.000
Tsimisky,Gounari
3 4 5 2004-01-02 22:00:00.000 Colonial
4 3 6 2003-04-04 22:00:00.000
Terkenli,Aristotel
5 2 5 2003-02-06 21:30:00.000 Ster Century
I should only get 5 results, with the Usernames of the Members.
i'm working on it right now, but if you have any idea, pls share
Thanks again!!|||Silver wrote:
> Thank you both for the quick reply.
> I tried both ways and managed to get results. The problem is, I get
> mopre results than I should. It's like somewhere happens a cartesion
> product.
> My table Dates is like this:
www.aspfaq.com/5006

> DateID - User1 - User2 - DateToMeet - Place
> ----
--
> --
> 1 1 2 1956-10-30 22:00:00.000 Plat.
> Aristotelous
> 2 1 3 1996-10-30 22:00:00.000
> Tsimisky,Gounari
> 3 4 5 2004-01-02 22:00:00.000
> Colonial 4 3 6 2003-04-04 22:00:00.000
> Terkenli,Aristotel
> 5 2 5 2003-02-06 21:30:00.000 Ster
> Century
>
> I should only get 5 results, with the Usernames of the Members.
> i'm working on it right now, but if you have any idea, pls share
> Thanks again!!
Show us your DDL, sample data and current query.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Bob's query has a common copy and paste oversight in it that will give you a
cartesian product, see if you can spot it.. or use mine :)
Mr Tea
http://mr-tea.blogspot.com
"Silver" <argytzak@.med.auth.gr> wrote in message
news:ct06f5$bd7$1@.nic.grnet.gr...
> Thank you both for the quick reply.
> I tried both ways and managed to get results. The problem is, I get mopre
> results than I should. It's like somewhere happens a cartesion product.
> My table Dates is like this:
> DateID - User1 - User2 - DateToMeet - Place
> ----
--
> --
> 1 1 2 1956-10-30 22:00:00.000 Plat.
> Aristotelous
> 2 1 3 1996-10-30 22:00:00.000
> Tsimisky,Gounari
> 3 4 5 2004-01-02 22:00:00.000 Colonial
> 4 3 6 2003-04-04 22:00:00.000
> Terkenli,Aristotel
> 5 2 5 2003-02-06 21:30:00.000 Ster
> Century
>
> I should only get 5 results, with the Usernames of the Members.
> i'm working on it right now, but if you have any idea, pls share
> Thanks again!!
>|||My current query is this
SELECT DISTINCT FirstU.Username as Member1,SecondU.Username as Member2,
D.DateToMeet,D.Place
FROM Dates AS D
INNER JOIN MEMBERS AS FirstU ON FirstMember=D.FirstMember
INNER JOIN MEMBERS AS SecondU ON SecondMember=D.SecondMember
WHERE (FirstU.Username != SecondU.Username)
I have already given the sample data for the table "Dates". It contains 2
columns, "FirstMember" and "SecondMember" that contain an int value, which
refers to the table "MEMBERS", column "MemberID". That is, a "1" in
"FirstMember" corresponds to a tuple in the table "MEMBERS" that has "1" as
MemberID and "Nick" as UserName
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OdXIwvUAFHA.3016@.tk2msftngp13.phx.gbl...
> Silver wrote:
> www.aspfaq.com/5006
>
> ----
--
> Show us your DDL, sample data and current query.
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>|||Silver wrote:
> My current query is this
> SELECT DISTINCT FirstU.Username as Member1,SecondU.Username as
> Member2, D.DateToMeet,D.Place
> FROM Dates AS D
> INNER JOIN MEMBERS AS FirstU ON FirstMember=D.FirstMember
> INNER JOIN MEMBERS AS SecondU ON SecondMember=D.SecondMember
> WHERE (FirstU.Username != SecondU.Username)
> I have already given the sample data for the table "Dates".
If you want me to take a deeper look at this you will provide DDL scripts
(CREATE TABLE statements) and sample data in the form of INSERT statements
(The faq article, www.aspfaq.com/5006, links to a script that shows you how
to generate those INSERT statements) so I can reproduce your problem on my
server. You're asking me for help. Does it seem polite to make me to go to
extra work to provide that help? :-)
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Lee Tudor wrote:
> Bob's query has a common copy and paste oversight in it that will
> give you a cartesian product, see if you can spot it.. or use mine :)
>
Yes it does, darnit. But yours should work, and he says it doesn't ...
Bob
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||On Sun, 23 Jan 2005 15:17:15 +0300, Silver wrote:

>My current query is this
>SELECT DISTINCT FirstU.Username as Member1,SecondU.Username as Member2,
> D.DateToMeet,D.Place
>FROM Dates AS D
> INNER JOIN MEMBERS AS FirstU ON FirstMember=D.FirstMember
> INNER JOIN MEMBERS AS SecondU ON SecondMember=D.SecondMember
>WHERE (FirstU.Username != SecondU.Username)
Hi Silver,
Your join criteria are wrong. The unqualified FirstMember is taken from
the Dates table, as there is no other table with a column of that name;
the other FirstMember is also from Dates because that is what the alias D
is used for. And since all rows will satisfy the requirement that
FirstMember is equal to itself (and ditto for SecondMember), you'll get a
full cartesian product from the join, that is then slightly reduced by the
WHERE clause.
Try this:
SELECT FirstU.Usersname as Member1, SecondU,Username AS Member2,
D.DateToMeet, D.Place
FROM Dates AS D
INNER JOIN Members AS FirstU
ON FirstU.MemberID = D.FirstMember
INNER JOIN Members AS SecondU
ON SecondU.MemberID = D.SecondMember
The WHERE clause is not needed. I assume the dates table doesn't contain
any dates between a member and him-/herself. The only effect of the where
clause would be to remove valid dates between two people who happen to
have the same name.
I also removed the DISTINCT, as it doesn't appear to be necessary.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)