Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 10th, 2007, 05:32 PM
Registered User
 
Join Date: Nov 2007
Location: , , Canada.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql query

I have two tables:

one is calleed tbl_Geniuses and is as follows:

intGeniusID - int
strGeniusEmail - text
strGeniusAccept - text
intRegIDRequestor - int
intRegIDAcceptor - int

The other is tbl_Registration and the important fields that I need to pull are:
intRegID - int
strPicture - text
strVideo - text
strAudio - text

My sql statment is in ASP is:

SELECT * FROM tbl_Geniuses WHERE strGeniusEmail is not null and strGeniusAccept=yes and intRegIDRequestor=" & session("Member")& " or intRegIDAcceptor=" & session("Member")& "

I need to change this to join so the above joins with: tbl_Registration where tbl_Geniuses.intRegIDRequestor is equal to tbl_Registration.intRegID OR tbl_Geniuses.intRegIDAcceptor is equal to tbl_Registration.intRegID.

I honestly have no clue how to write this sql statement.

I was going to write a routine in Vbscript that get each value for intRegIDRequestor and intRegIDAcceptor from the first sql statement recordset and check each value using:

select * from tbl_Registration where intRegID=intRegIDRequestor or intRegID=intRegIDAcceptor.

There must be a more efficient way of doing things.

any help would be appreciated,
Luis

Reply With Quote
  #2 (permalink)  
Old December 10th, 2007, 06:08 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

You can join the tables and then put your conditions in the WHERE statement
Select * from tbl_Geniuses INNER JOIN tbl_Registration ON tbl_Geniuses.intRegID = tbl_registration.intRegIDRequestor WHERE strGeniusEmail is not null and strGeniusAccept=yes and intRegIDRequestor=" & session("Member")& " or intRegIDAcceptor=" & session("Member")& "

Reply With Quote
  #3 (permalink)  
Old December 10th, 2007, 06:28 PM
Registered User
 
Join Date: Nov 2007
Location: , , Canada.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am using this now and it partially works:

SELECT * FROM tbl_Geniuses INNER JOIN tbl_RegistrationInfo ON tbl_Geniuses.intRegIDRequestor = tbl_RegistrationInfo.intRegID WHERE strGeniusEmail is not null and strGeniusAccept=yes and tbl_Geniuses.intRegIDRequestor=" & session("Member")& " or tbl_Geniuses.intRegIDAcceptor=" & session("Member")& "

I need the results to have

"tbl_Geniuses.intRegIDRequestor = tbl_RegistrationInfo.intRegID"

as well as

"tbl_Geniuses.intRegIDAcceptor = tbl_RegistrationInfo.intRegID"

a union of both conditions that goes before the 'where'

Is that possible?

Luis

Reply With Quote
  #4 (permalink)  
Old December 10th, 2007, 06:58 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Before I get lost in this. I didn't know about his table, tbl_RegistrationInfo. Let's make sure you database design is going to work first.

So tbl_Geniuses holds email address, genius ID, ID Acceptor, and ID Requestor

What is the data in the Registration table:
Who's picture, video and Audio?
strPicture - text
strVideo - text
strAudio - text

And what data is in the RegistrationInfo table?

If you just want to JOIN to the RegistrationInfo table then:

Select * from tbl_Geniuses INNER JOIN tbl_Registration ON tbl_Geniuses.intRegID = tbl_registration.intRegIDRequestor INNER JOIN tbl_RegistrationInfo ON tbl_Geniuses.intRegID = tbl_RegistrationInfo.intRegID WHERE strGeniusEmail is not null and strGeniusAccept=yes and intRegIDRequestor=" & session("Member")& " or intRegIDAcceptor=" & session("Member")& "







Reply With Quote
  #5 (permalink)  
Old December 10th, 2007, 11:24 PM
Registered User
 
Join Date: Nov 2007
Location: , , Canada.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

tbl_Geniuses:

intGeniusID - int
strGeniusEmail - text
strGeniusAccept - text
intRegIDRequestor - int
intRegIDAcceptor - int

tbl_Registrationinfo:

intRegID
strVideo
strAudio
strContent
strUserName

The audio and video belong to the registrant.

When they add friends to their network, the info gets written to tbl_Geniuses

When I need to display a list of friends. i need to join these two tables in order to get the list.

The tricky part is that I want to join the list on:

"tbl_Geniuses.intRegIDRequestor = tbl_RegistrationInfo.intRegID"

And

"tbl_Geniuses.intRegIDAcceptor = tbl_RegistrationInfo.intRegID"

The reason for this is that it doesn't matter who requested or accepted to be put on the friends list (you have to accept an email invitation in order to get on the list)so I need to check both intRegIDAcceptor and intRegIDRequestor

I hope that makes sense.

Luis




Reply With Quote
  #6 (permalink)  
Old December 11th, 2007, 12:11 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Then you probably shouldn't join to both fields. Just put the intRegID in the Genius table. That will give you the Registrants ID in the Genius table regardless if it was the Acceptor or Requestor. I realize I may be missing something though. Create a database diagram and send it to me. So, the friends list is the Genius table?

What's the difference between the Registration table and the Registration Info table? I have a feeling that you're wanting one of these tables to be an intermediary table that you can use to create a many to many relationship so that Geniuses can be related to different registrants. This would be a lot easier if you just lined out the spec of what you want to do, create the tables accordingly and then the queries would be a lot easier to write.

Reply With Quote
  #7 (permalink)  
Old December 17th, 2007, 11:17 PM
Registered User
 
Join Date: Nov 2007
Location: , , Canada.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

THe Registration and RegistrationInfo tables are the same - I just didn't type them properly.

Yes, you are right - the genius table is the friends table. I want to be able to pull the all the friends for any member, and yes it is a many to many relationship so that geniuses can be related to many registrants. correct.

My dilema is that I want to be able to keep track of who has accepted their invitations to become members (intRegIDAcceptor) and who did the inviting (intRegIDRequestor ). Right now it does't really matter but down the road the more accepted invites, the better for the registrant (bonuses, prizes etc...).

This is not my db and I am hesitant to change anything as I am working on this temporarily. However this is one of the mandatory tasks.

where do I send a db diagram?

Thanks,
Luis

Reply With Quote
  #8 (permalink)  
Old December 18th, 2007, 04:52 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

You can look up my username here and email it to me.

Look for rstelma

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query i need seearam MySQL 7 November 30th, 2008 03:14 AM
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
SQL Query!! dpkbahuguna Beginning VB 6 5 October 12th, 2007 12:39 AM
Help with SQL query sattaluri Access 2 August 11th, 2006 09:26 AM
SQL query PinkyCat Classic ASP Databases 3 March 11th, 2005 01:41 PM



All times are GMT -4. The time now is 01:02 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.