#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2008
    Posts
    75
    Rep Power
    11

    SQL Query Multiple Tables Question


    Hi I am trying to query 2 tables (Messages and Comments) and the two columns (username and c_name).

    This isn't quite working out right. I'm using ASP with Access.

    sSQL="SELECT MESSAGES.username" & _
    "FROM MESSAGES" & _
    "WHERE MESSAGES.username LIKE '%" & search_unqs & "%'" & _

    "UNION" & _

    "SELECT COMMENTS.c_name" & _
    "FROM COMMENTS" & _
    "WHERE COMMENTS.c_name LIKE '%" & search_unqs & "%'" & _
    "ORDER BY ID ASC"

    I also tried

    sSQL="SELECT MESSAGES.username, COMMENTS.c_name FROM MESSAGES, COMMENTS WHERE username = '%" & search_unqs & "%' Order by ID ASC;"

    Not a winner either..

    Any help is much appreciated.

    Thanks
  2. #2
  3. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Mar 2006
    Location
    South Wales
    Posts
    3,674
    Rep Power
    2047
    Hi,

    The one thing that I noticed in both of your statements is that you dont seem to be joining the tables on a common field. I would expect something like:
    Code:
      
    sSQL="SELECT MESSAGES.username, COMMENTS.c_name " & _
    "FROM MESSAGES, COMMENTS WHERE username = '%" & search_unqs & "%' " & _
    "AND MESSAGES.MESSAGEID = COMMENTS.MESSAGEID " & _
    "Order by ID ASC;"
    Any chance you could post a description of your tables along with an explanation of the link between the two tables so we can try and help you.
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2008
    Posts
    75
    Rep Power
    11
    Hi thanks so much for responding.

    The two tables both have an autonumber ID field in common as well as a field called uniquen (ID as primary key).

    Basically the "search_unqs" variable is a user input for a search. I am trying to output results based on message and comment usernames (MESSAGES.username & COMMENTS.c_name).


    EDIT: Would this be easier to do if the username and c_name fields had the same name or is possible to do either way?..
    Last edited by mendir200; February 24th, 2009 at 09:53 AM.
  6. #4
  7. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Mar 2006
    Location
    South Wales
    Posts
    3,674
    Rep Power
    2047
    What is the relationship between the two tables, ie. How is the messgaes table linked to the comments table? You must have a foreign key in one of the tables.

    If you establish the link you just need to edit the bit of code which I highlighted in my last post so that it reflects the correct fieldnames.
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2008
    Posts
    75
    Rep Power
    11
    The messages table corresponds to the comments by the uniquen number that is created on original submit.

    I tried your code joining them by uniquen and it is not finding any results. The thing is I need the query to grab ALL of username and ALL of c_name as if they were one column to output by the search_unqs variable.


    Edit: If the SQL statement could understand this, this is what I am trying to do:

    sSQL="SELECT * FROM MESSAGES WHERE username LIKE '%" & search_unqs & "%' AND COMMENTS WHERE c_name LIKE '%" & search_unqs & "%'"
    Last edited by mendir200; February 24th, 2009 at 10:07 AM.
  10. #6
  11. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2008
    Posts
    75
    Rep Power
    11
    You gave up on me didn't you
  12. #7
  13. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Mar 2006
    Location
    South Wales
    Posts
    3,674
    Rep Power
    2047
    Originally Posted by mendir200
    You gave up on me didn't you
    No I didnt give up on you, I was busy with other things!!

    You can concatenate your values like this:
    Code:
    sSQL="SELECT * FROM MESSAGES WHERE username LIKE '%" & search_unqs & "%' + CAST(COMMENTS AS VARCHAR) WHERE c_name LIKE '%" & search_unqs & "%'"
    But I would have to see a sample of your data to see if this would work.
    Last edited by sync_or_swim; February 25th, 2009 at 04:38 AM.

Similar Threads

  1. query AND/OR for textboxes
    By gilgalbiblewhee in forum ASP Development
    Replies: 1
    Last Post: February 4th, 2005, 06:39 PM
  2. Need help with asp Sql problem
    By bleutiger in forum Programming Help
    Replies: 1
    Last Post: January 22nd, 2005, 09:44 PM
  3. "where" clause problem
    By gilgalbiblewhee in forum ASP Development
    Replies: 6
    Last Post: August 18th, 2004, 09:43 PM
  4. function for dropdown with 3 checkboxes
    By gilgalbiblewhee in forum ASP Development
    Replies: 1
    Last Post: August 18th, 2004, 08:07 PM
  5. How to add a multiple keyword search
    By gilgalbiblewhee in forum ASP Development
    Replies: 1
    Last Post: August 11th, 2004, 12:56 AM

IMN logo majestic logo threadwatch logo seochat tools logo