SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP ADO SQL Query

    I'm having trouble thinking how to write a query ... i have two tables customer and contact ...they share a common element of customerID ... their is a login form based on the contactID, contactPassword within the contact table, based on that i want the contactID and customerID of contact, to display the fields of the customer table ...

    Anyone keep up with that?

  2. #2
    HardCoder md_irfan_amu's Avatar
    Join Date
    May 2005
    Location
    Asia
    Posts
    215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select * from customer a, contact b where a.customerid=b.contactid

    by this query you get all result .. now foe particular id

    select * from customer a, contact b where a.customerid=b.contactid where customerID= <ID from login form>
    Irfan
    Find Tutors Easyway to get connect with your tutor in india

  3. #3
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by md_irfan_amu
    select * from customer a, contact b where a.customerid=b.contactid

    by this query you get all result .. now foe particular id

    select * from customer a, contact b where a.customerid=b.contactid where customerID= <ID from login form>
    Thanks, ill give that a try

  4. #4
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    where customerID= <ID from login form>
    Just one problem with this ... which customerID is it? customer.customerID or contact.customerID

    Code:
    WHERE customer.customerid = contact.contactid"
    Oh wait, a second problem they wont ever be equal .... so are you talking customer.customerID = contact.customerID

  5. #5
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by md_irfan_amu
    select * from customer a, contact b where a.customerid=b.contactid

    by this query you get all result .. now foe particular id

    select * from customer a, contact b where a.customerid=b.contactid where customerID= <ID from login form>
    No, no, no! JOIN is the command you're looking for... I have to admit, I usually do it the way above, but only because I'm lazy as sin by my own admission and at least I know I'm wrong!

    Which DBMS are you using?

    MySQL:
    http://dev.mysql.com/doc/mysql/en/join.html

    MS SQL Server:
    http://www.dotnetspider.com/technolo...ages/1134.aspx

    Access:
    http://www.techonthenet.com/access/queries/joins2.htm
    (Follow those steps and copy and paste from the SQL window - if you have SQL Server and Enterprise Manager you can do the same in that too - the interface is identical. That's what we usually do! )

    If you're still struggling, throw this in to Databases forum - it's a database question really. There's a few SQL wizzes hanging around in there who could sort you out in no time. Better to learn yourself though, otherwise you never remember! That's what I think, anyway...

    Cheers,

    G

  6. #6
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In case you come back to this, (I think) this would be correct for MySQL:

    Code:
    "SELECT customer.*, contact.contactID As ContactID, contact.contactPassword As ContactPassword " &_
    "FROM customer " &_
    "INNER JOIN contact " &_
    "ON customer.customerID = contact.customerID"
    That SQL string should return all data from the Customer table and contactID and contactPassword from the Contact table when the customerID's of the two tables match. The ON line contains what is known as the JOIN CONDITION which replaces the WHERE in the previous example - you don't need the WHERE because the ON does it instead. This is more efficient and should always be used if possible according to the MySQL docs. Quite why, I'm not exactly sure. I take their word for it...

    You can call the data held by your aliases for the Contact table like this:

    objRecordSet("ContactID") will be the Contact ID from the Contact table.
    objRecordSet("ContactPassword") will be the Contact Password from the Contact table.

    You retrieve the values from the Customer table in the usual way.

    Hope that helps.

    G


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •