Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts

    ###Serious query problem###

    look at those two tables:


    I have been trying to solve this problem for months but no luck.
    I need to query this DB so that it would contain this:
    1. Select date from "tbl1" and all fields from "tbl2" which are connected to tbl2 ON tbl1.id2join=tbl2.id and where the field "word" = to 'Jamey Foxx' and order it by date.
    2. Select all fields from "tbl2" where the field "word" = to 'Jamey Foxx' and order by length.
    3. UNION 1 and 2 (above) into one recordset.

    I tried and tried and no luck...
    can someone please help with this query?
    Thanks,
    ShMiL

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    select
                 t1.date
                ,t2.*
            from tbl1 t1
            join tbl2 t2 on t1.id2join = t2.id
            where t1.word = 'Jamey Foxx' 
            order by date
    union
    select 
                 null
                ,*
            from tbl2 
            where word = 'Jamey Foxx' 
            order by [length]
    This should do it though in practice I would select each field individually instead of using the *.

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question I copied it exactly and it doesn't work

    I get Syntax error:
    Code:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
    This is my query, exactly as you wrote:
    Code:
    select t1.date,t2.* from tbl1 t1 join tbl2 t2 on t1.id2join = t2.id where t1.word = 'Jamey Foxx' order by date union select null,* from tbl2 where word = 'Jamey Foxx' order by [length]

  • #4
    New Coder
    Join Date
    Aug 2002
    Location
    Islamabad
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hello

    Hello ShMiL
    Try this if you still have problem
    select t1.date , t2.*
    from table tbl1 t1, tbl2 t2
    where t1.word ='Jamey Foxx'
    order by t1.date, t1.length;
    ENGR IRFAN HAIDER JAFFERY
    BE CIVIL, MCS (STUDENT)
    ISALAMABAD

  • #5
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts
    select t1.date , t2.*
    from table tbl1 t1, tbl2 t2
    where t1.word ='Jamey Foxx'
    order by t1.date, t1.length;


    Is no good because it won't select any records where tbl2.word = 'Jamey Foxx' Only the ones from tbl1 will be chosen.

    but thanks anyways.

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SELECT t1.date,t2.* FROM tbl1 t1 JOIN tbl2 t2 ON(t1.id2join = t2.id) WHERE t1.word = 'Jamey Foxx' ORDER BY t1.date, t2.length DESC

    I don't see why that wouldn't work, but I can't test it out right now.

    It also makes it easier to type the pertinent things in the SQL statement in caps, at least for me so I can see that stuff at a glance.

    P.S. With that SQL statement you are getting one recordset, that's the purpose of the join.
    Last edited by whammy; 08-18-2002 at 03:06 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #7
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Thanks but

    Still not working, the same 'error in FROM clause'...
    PLEASE
    HELP!

  • #8
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Do this:

    Code:
    SELECTQUERY = "SELECT t1.date,t2.* FROM tbl1 t1 JOIN tbl2 t2 ON(t1.id2join = t2.id) WHERE t1.word = 'Jamey Foxx' ORDER BY t1.date, t2.length DESC"
    Response.Write(SELECTQUERY) : Response.End
    That's usually the best way to debug a SQL statement, to see what's really going on (besides using SQL Query Analyzer, that is).
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #9
    New Coder
    Join Date
    Aug 2002
    Location
    Islamabad
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    replt to ShMil

    select t1.date , t2.*
    from table tbl1 t1, tbl2 t2
    where t1.id2join = t2.id
    and t1.word ='Jamey Foxx'
    order by t1.date, t1.length;


    Yes My Fault I didnt join the tables. I hope above will work.
    ENGR IRFAN HAIDER JAFFERY
    BE CIVIL, MCS (STUDENT)
    ISALAMABAD

  • #10
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Unhappy I'm sorry but

    I seem to get the same error for all of the query you wrote.
    Could you think of a reason it would work for you and not work for me?
    Can someone please make a little example so I can test it here, at my machine?

    Thanks alot to you all!

  • #11
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just for reference:

    http://www.indus-soft.com/winsql/

    Is an excellent SQL building & debugging tool.


    Code:
    SQL = "SELECT " & _
                     "t1.date, " & _
                     "t2.* " & _
               "FROM " & _
                     "tbl1 t1 " & _
               "INNER JOIN tbl2 t2 " & _
                   "ON(t1.id = t2.id2join) " & _
               "WHERE " & _
                     "t1.word = 'Jamey Foxx' "
    That is my attempt. I did not test this but let me know if there is an error message.
    Last edited by allida77; 08-19-2002 at 02:25 PM.

  • #12
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts

    GOOD NEWS!

    I tried this:
    Code:
    select tbl1.datr,tbl2.* from tbl1 inner join tbl2 on tbl1.id2join = tbl2.id where tbl1.word = 'Jamey Foxx' order by tbl1.datr UNION SELECT 0,tbl2.* FROM tbl2 INNER JOIN tbl1 ON tbl2.id=tbl1.id2join WHERE tbl2.word = 'Jamey Foxx' and tbl1.datr=0 order by [length]
    (I guess [date] as a reserved word caused the problems)
    But now I have a bigger problem, I get an error that says the UNION can't be used with MEMO fields.
    And I have to use MEMO fields...
    What should I do?

    Thanks.

  • #13
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think the one I used should work without UNION, did you try changing date to something else?

    I still didn't test it though since I don't have access at home :-/
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #14
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Whammy:
    What about condition number 2?
    I need tbl2.word to be = to 'Jamey Foxx' too!

  • #15
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    about this last question :

    why don't you use an "or" statement to combine the conditions ?

    (their's also a small error in your request : the result of meeting one of the two conditions will result in data from the same variables, so what you defined in your orriginal post for 1 and 2 isn't possible : tbl1.date will also be selected for the record that meet condition 2)

    i'll try to set 2 simmilar tables up to check a working sql statement (if I can find the time)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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