-
Feb 13th, 2018, 02:40 AM
#1
Thread Starter
Member
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
i am sorry if iam wrong thread.
-
Feb 13th, 2018, 06:36 AM
#2
Re: help for mysql query
Originally Posted by apdentalsystem
this maybe very basic but i did not find how to.
i have table A and B and need query to result C
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!
-
Feb 13th, 2018, 04:50 PM
#3
Thread Starter
Member
Re: help for mysql query
Originally Posted by TysonLPrice
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
-
Feb 13th, 2018, 05:13 PM
#4
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...
-
Feb 13th, 2018, 05:18 PM
#5
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
-
Feb 13th, 2018, 05:53 PM
#6
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
-
Feb 13th, 2018, 09:35 PM
#7
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
-
Feb 13th, 2018, 09:39 PM
#8
Thread Starter
Member
Re: help for mysql query
Originally Posted by gibra
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?
-
Feb 13th, 2018, 10:15 PM
#9
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.
-
Feb 13th, 2018, 10:42 PM
#10
Thread Starter
Member
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
-
Feb 13th, 2018, 11:10 PM
#11
Re: help for mysql query
Originally Posted by apdentalsystem
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.
-
Feb 14th, 2018, 08:01 AM
#12
Re: help for mysql query
Originally Posted by apdentalsystem
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
-
Feb 14th, 2018, 08:22 AM
#13
Re: help for mysql query
Originally Posted by techgnome
I should have gone with my initial instinct.... a full outer join...
This produces the exact results being looked for:
-tg
Great!!!
-
Feb 16th, 2018, 09:14 AM
#14
Thread Starter
Member
Re: help for mysql query
will try another way. thanks to all my friend
-
Feb 16th, 2018, 09:52 AM
#15
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|