2

This topic is closed.. i found an answer which shows the desired output using subqueries.

SELECT table_a.id, table_a.col as col, (select col2 from table_b WHERE col1 = col AND col3 = 'me' LIMIT 1) as table_b_col2, (select col3 from table_b WHERE col1 = col AND col3 = 'me' LIMIT 1) as table_b_col3 FROM table_a

I have two tables:

Table A

ID  | COL
1   |   A
2   |   B
3   |   C
4   |   D

Table B

ID1  | COL1  | COL2  | COL3

1   |     A     |      60  |    me
2   |     B     |      45  |    me

Now i wanted to fetch records like this

Expected result:

ID1  | COL1  | COL2  | COL3
1   |     A     |    60  |    me
2   |     B     |    45  |    me
3   |     C     |          |   
4   |     D     |          |   

I already tried using left join but it only gives the rows from table b with values

TEST TABLE:

ID  | COL1  | COL2  | COL3
1   |     A     |    60  |    me
2   |     B     |    45  |    me

Any ideas on how to implement this or maybe you can share your codes.. thank you

Mark Pagtalunan
  • 29
  • 5
  • You need to do a left outer join, which will fill the values missing on the right-side table with NULLs. – vlumi Oct 28 '19 at 06:56
  • 4
    Hi Mark Pagtalunan, why does the `B` appear on row 3 and 4 on the expected result? could you visit the [table join primer QA](https://stackoverflow.com/q/13997365/4648586)? – Bagus Tesa Oct 28 '19 at 07:04
  • What does "tried using left join" mean? Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular formatted initialization. – philipxy Oct 28 '19 at 22:30
  • @philipx, this is my code select table_a.id, table_a.col, table_b.col2, table_b.col3 from table_a left join table_b on table_a.id = table_b.id_1 where table_b.col3 = 'me' ; and the result is the test table no error encountered however, this is not what i want. – Mark Pagtalunan Oct 29 '19 at 07:15
  • Please clarify via edits, not comments. Please act on my earlier comment. PS Part of a [mcve] is a clear specification--a description of how the result you want is a function of the input. But you don't give one. We only have an example so we can only guess what that is. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. PS What are those blanks? Null strings? NULLs? – philipxy Oct 29 '19 at 08:26
  • Please clarify via edits, not comments. Please don't edit a question to give an answer, post an answer. Please before you post look at the formatted version of your post below the edit box. Read the edit help re inline & block formats for code & quotations. – philipxy Oct 31 '19 at 03:24

4 Answers4

1

I guess you are trying to left join Table A from Table B.If you left join as above then you will be getting the joined records which has the column values in Table B (COL1 A & B). Since there's no COL2 value for C,D in Table B it will return the value in the test table that you have provided in the question.

You can use LEFT JOIN to get the expected result.

SELECT a.col,b.COL1,b.COL2  
FROM Table A a
LEFT JOIN table_B b ON a.ID = b.ID
Hirumina
  • 738
  • 1
  • 9
  • 23
1

I would expect the results you want to be:

ID  |  COL1     | COL2
1   |     A     |    60
2   |     B     |    45
3   |     C     |   
4   |     D     |

For this you would use LEFT JOIN

select a.id, a.col, b.col2
from a left join
     b
     on a.id = b.id ;

For your actual results, it is a bit hard to tell where the extra "b"s come from. Here is a simple method:

select a.id, coalesce(b.col1, 'b'), b.col2
from a left join
     b
     on a.id = b.id ;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • i tried this one already i did not use the id in left join instead i use the col, but the result is still the tested output row 3 and 4 did not show up – Mark Pagtalunan Oct 28 '19 at 10:24
  • @MarkPagtalunan . . . A `left join` will keep all rows in the first table, so rows 3 and 4 would still be in the result set. – Gordon Linoff Oct 28 '19 at 21:44
  • heres my query sir. select table_a.id, table_a.col, table_b.col2, table_b.col3 from table_a left join table_b on table_a.id = table_b.id_1 where table_b.col3 = 'me' – Mark Pagtalunan Oct 28 '19 at 23:52
  • @MarkPagtalunan . . . Your query has a `where` clause. This answer does not. – Gordon Linoff Oct 29 '19 at 01:15
  • @ Gordon Linoff... meaning, it is not possible to get the expected result? – Mark Pagtalunan Oct 29 '19 at 01:56
  • @MarkPagtalunan . . . Given the data in your question, this *does* return the desired results. Often with an outer join, you need to move `where` conditions to the `on` clause, but you have not asked a question that has appropriate sample data and explanations. But you can try that. – Gordon Linoff Oct 29 '19 at 10:49
0

use left join and case when

 select a.id,case when a.COL='A' then 'A' else 'B' end as col1
 ,b.col2 from
 tablea a left join tableb b on a.id=b.id
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

In your case you can use left join on column A.COL and B.COL1.

select A.ID, A.COL as COL1, B.COL2 from A left join B on A.COL = B.COL1

LF00
  • 27,015
  • 29
  • 156
  • 295