How to Get Value Using Join Table with Different Values

How to join 2 tables when it has different values in the column

Hope this solution can help you, (I am used SQL_Server syntax)

SELECT isnull(date1,date2) as Date3, ISNULL(RM, 0 ),ISNULL(KM, 0 )
FROM table1 
FULL JOIN table2 
ON table1.Date1 = table2.Date2
order by Date3;

[RESULT]:

[EDIT]:
Live demo

create table Table1 (DATE1 date, RM int);

INSERT INTO Table1 VALUES ('1/4/2020' , 1);
INSERT INTO Table1 VALUES ('2/1/2020' , 4);
INSERT INTO Table1 VALUES ('2/10/2020' , 4);

GO

3 rows affected

create table Table2 (DATE2 date, KM int);

INSERT INTO Table2 VALUES ('2/2/2020' , 1);
INSERT INTO Table2 VALUES ('2/10/2020' , 3);
INSERT INTO Table2 VALUES ('3/5/2020' , 2);
GO

3 rows affected

select * from Table1;
GO

DATE1      | RM
:--------- | -:
2020-01-04 |  1
2020-02-01 |  4
2020-02-10 |  4

select * from Table2;
GO

DATE2      | KM
:--------- | -:
2020-02-02 |  1
2020-02-10 |  3
2020-03-05 |  2

SELECT isnull(date1,date2) as Date3, ISNULL(RM, 0 ),ISNULL(KM, 0 )
FROM table1 
FULL JOIN table2 
ON table1.Date1 = table2.Date2
order by Date3;
GO

Date3      | (No column name) | (No column name)
:--------- | ---------------: | ---------------:
2020-01-04 |                1 |                0
2020-02-01 |                4 |                0
2020-02-02 |                0 |                1
2020-02-10 |                4 |                3
2020-03-05 |                0 |                2

db<>fiddle here

Joining two tables and getting values

You can do it using a left join:

SELECT t.id, s.name, s.value, s.type, s.description
FROM  App_Config s
LEFT JOIN Client_Config t
 ON(t.name = s.name and t.id = @id)

How to join a column of same values to another different table in SQL?

Get into the habbit of always specifying the column names:

INSERT INTO names_with_company (CompanyID, Name, Age, Gender)
SELECT 1234, Name, Age, Gender
FROM names_table;

As you can see, you can provide "literal" values for any column.

JOIN tables with condition to return different values in same column

I think you can just do a straight series of joins, and then use a CASE expression to render the final value of the source system:

SELECT
    CASE WHEN ds.NAME = 'Interactive' THEN 'NIL' ELSE c.SOURCE_SYSTEM END AS SOURCE_SYSTEM
FROM CUSTOMERS c
INNER JOIN ALERT_CASE_HEADER ach
    ON ach.CUSTOMER_ID = c.CUSTOMER_ID
INNER JOIN DATA_SOURCE ds
    ON ach.DATASOURCE_ID = ds.ID

As a note, you might want to make one or both of the above joins LEFT JOIN if you suspect that records might be filtered off due to not matching the other table. Also, in your original query you were joining twice to ALERT_CASE_HEADER, which is unnecessary.

Join tables having multiple values in a cell

One solution that should work in the majority of databases just uses like to match on the strings.

Also note the sample data is incorrect for B - Orange does not appear in the results.

select t1.name, t2.fruit, t2.value
from t1 join t2 on t1.fruits like Concat('%',t2.fruit,'%')

sql joining on same column with different values

Although you've already answered this yourself, I want to provide an alternate method, joining the school table twice.

 Select hteam.name as hometeamname,
            ateam.name as awayteamname,
            mi.homeTeam,
            mi.awayTeam
 From matchInformation mi 
 Inner Join school hteam
         On hteam.ncaaId = mi.homeTeam
 Inner Join school ateam
         On ateam.ncaaId = mi.awayTeam
 Where hteam.name = 'FOO' Or ateam.name='FOO'

How to join to 2 different tables based on field value of main table?

You need to SELECT the columns that you want. You are only selecting columns from the menus table, so that is all you get.

I would suggest selecting columns from the other two tables, but combining them using COALESCE():

SELECT m.*,
       COALESCE(p.name, c.name) as name,  -- or whatever the columns are
       -- repeat for additional columns that you want
FROM menus m LEFT JOIN
     products p
     ON p.id = m.item_id AND m.item_type = 'product' LEFT JOIN
     categories c
     ON c.id = m.item_id AND m.item_type = 'category'

How do I count values in different tables using JOIN and/or UNION in MYSQL?

We can do this with count from Union all. I would rather advise you to have one table with a month column.

create table jan (pname char(1));
create table feb (pname char(1));
insert into jan values('A'),('A'),('C');
insert into feb values ('A'),('B'),('C');
select
  pname, 
  count(j) jan,
  count(f) feb
from
  (select pname,pname j,null f from jan
    union all
  select pname,null,pname from feb) jf
group by pname

pname | jan | feb
:---- | --: | --:
A     |   2 |   1
B     |   0 |   1
C     |   1 |   1

db<>fiddle here

---

Related Topics