dcsimg
Results 1 to 15 of 15

Thread: help for mysql query

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    51

    help for mysql query

    this maybe very basic but i did not find how to.
    i have table A and B and need query to result C
    Name:  PROBLEM.png
Views: 137
Size:  11.1 KB
    i am sorry if iam wrong thread.

  2. #2
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,136

    Re: help for mysql query

    Quote Originally Posted by apdentalsystem View Post
    this maybe very basic but i did not find how to.
    i have table A and B and need query to result C
    Name:  PROBLEM.png
Views: 137
Size:  11.1 KB
    i am sorry if iam wrong thread.
    Just free form in MSSQL and not tested:

    Select a.b, b.c
    from tableA a
    join tableB b on b.a = a.a
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    51

    Re: help for mysql query

    Quote Originally Posted by TysonLPrice View Post
    Just free form in MSSQL and not tested:

    Select a.b, b.c
    from tableA a
    join tableB b on b.a = a.a

    just display that have join. record that not have joint result not display

  4. #4
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,570

    Re: help for mysql query

    I think you can't, because Jon and ur5 are on rows that have nothing in common, apart having the A field to NULL on both the tables.
    Perhaps trying with a cursor...

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: help for mysql query

    If those are the only rows that ever have nothing for the a col... this might work:
    Code:
    Select a.b, b.c
    from tableA a
    join tableB b on (b.a = a.a) or (b.a is null and a.a is null)
    So it'll join where the a cols match, or where it is null on both sides... although I'd question that data integrity...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,570

    Re: help for mysql query

    This is very close to the desired result:

    Code:
    SELECT TableA.A, TableA.B, TableB.C 
    FROM TableA LEFT JOIN TableB  ON TableA.A = TableB.A 
    
    UNION 
    
    SELECT TableA.A, TableA.B, TableB.C  
    FROM TableA RIGHT JOIN TableB ON TableA.A = TableB.A
    Code:
    A    B      C    
                ur3    
                ur5    
                ur6    
         Jon        
    1    Lis    ur1    
    2    Kor    ur2    
    3    Rud        
    6    Victor

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: help for mysql query

    I should have gone with my initial instinct.... a full outer join...
    This produces the exact results being looked for:
    Code:
    create table Table_A (
    col_A integer null, col_B nvarchar(10) null
    )
    create table Table_B (
    col_A integer null, col_C nvarchar(10) null
    )
    go
    insert into Table_A values (1, 'Lis')
    insert into Table_A values (2, 'Kor')
    insert into Table_A values (3, 'Rud')
    insert into Table_A values (6, 'Victor')
    insert into Table_A values (null, 'Jon')
    go
    insert into Table_B values (1, 'url1')
    insert into Table_B values (2, 'url2')
    insert into Table_B values (4, 'url3')
    insert into Table_B values (null, 'url5')
    insert into Table_B values (5, 'url6')
    go
    
    select coalesce(a.col_A,b.col_a,0) as ColA, A.col_B, B.col_C
    from Table_A A
    full outer join Table_B B on coalesce(A.col_A, 0) = coalesce(B.col_a, 0)
    go
    
    drop table Table_A
    drop table Table_B
    go
    In addition to the full outer join, the key is to use a coalesce on the two cols on the join.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    51

    Re: help for mysql query

    Quote Originally Posted by gibra View Post
    This is very close to the desired result:

    Code:
    SELECT TableA.A, TableA.B, TableB.C 
    FROM TableA LEFT JOIN TableB  ON TableA.A = TableB.A 
    
    UNION 
    
    SELECT TableA.A, TableA.B, TableB.C  
    FROM TableA RIGHT JOIN TableB ON TableA.A = TableB.A
    Code:
    A    B      C    
                ur3    
                ur5    
                ur6    
         Jon        
    1    Lis    ur1    
    2    Kor    ur2    
    3    Rud        
    6    Victor
    yes tgis is very close my friend.maybe any little trick?

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,775

    Re: help for mysql query

    Here's an idea: instead of providing one example and nothing more, how about you describe the actual rules that you want the query to implement? Examples are a fine idea but they are not a substitute for a FULL and CLEAR explanation. If you don't provide an explanation then, unless your example covers every possible scenario, getting a proper solution will just be luck. If you make us guess then we can guess wrong and that's a waste of everyone's time and effort.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  10. #10

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    51

    Re: help for mysql query

    I dont know but not work,there error 1064.
    I have report from 2 table above and want result like above,because the table is different so condition is above

  11. #11
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,775

    Re: help for mysql query

    Quote Originally Posted by apdentalsystem View Post
    I dont know but not work,there error 1064.
    I have report from 2 table above and want result like above,because the table is different so condition is above
    Full? No. Clear? No. Explanation? No.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: help for mysql query

    Quote Originally Posted by apdentalsystem View Post
    I dont know but not work,there error 1064.
    I have report from 2 table above and want result like above,because the table is different so condition is above
    Did you look at the sample I provided? how is it different from what you described and requested? Did it work? IT created two tables that mirror what your example showed, filled them with the same data and then returned the joined result... then drops the tables... that's because I didn't want the sample tables cluttering up my database... but the key is the full outer join in the middle there... that's what you're after... that with the coalesce on the join is what should get you the results, assuming you've told us everythign about your data that we need to know... But the fact that you're using Col_A to attempt to join the tables together even though there are holes, is concerning... because that means there's a problem with your referential data integrity that shouldn't be happening. Bottom line: Col_A appears that it is a foreign key of some kind, but it isn't being filled in properly which means you may have bigger issues than this query.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,570

    Re: help for mysql query

    Quote Originally Posted by techgnome View Post
    I should have gone with my initial instinct.... a full outer join...
    This produces the exact results being looked for:
    -tg
    Great!!!

  14. #14

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    51

    Re: help for mysql query

    will try another way. thanks to all my friend

  15. #15
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: help for mysql query

    I'm not sure what the problem is... I gave you the solution... a Full Outer Join ... there really is no other way... But again, the real problem is that you appear to have a data integrity issue... but that aside, given the data sample you gave, the solution is what I outlined... a full outer join with a coalesce in the join condition.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width