Sql Field with Multiple Id's of Other Table

MySQL Join Multiple IDs From Another Table

you need several joins, one for each fieldID

select b.Name, a.Field1Data, c.name, a.Field2Data, d.name, a.FieldeData
from table2 a
inner join table1 b on b.id = a.Field1ID
inner join table1 c on c.id = a.Field2ID
inner join table1 d on d.id = a.Field3ID

Query Multiple IDs in 1 table row to another table in 1 query

Is this what you want?

SELECT t1.name, t21.group_1, t22.group_2, t23.group_3
FROM table1 t1 LEFT JOIN
     table2 t21
     ON t1.group_1 = t21.ID LEFT JOIN
     table2 t21
     ON t1.group_2 = t22.ID LEFT JOIN
     table2 t21
     ON t1.group_3 = t23.ID 
WHERE t1.ID IN (868) 
ORDER BY FIELD(t1.ID, 868);

This uses LEFT JOIN in case any of the reference columns are NULL.

How can I display multiple ID's and value in single column SQL?

I found that. I used cursor and I'm listing my tags in one column.

SELECT kn.ID,Title,ExplainProblem,SolutionDescription,kn.CreateUserID, M.Name +' 
' + M.Surname as 'TechNameAndSurname',kn.CreateDate,IsPrivate,kn.IsActive,
case
when kn.IsPrivate=1 then 'Sadece Ben'
when kn.IsPrivate=0 then 'Seçili Gruplar'
else ''
end WhoIsShowing
into #temp FROM Knowledges kn
inner join Members M on M.ID = kn.CreateUserID where kn.IsActive=1
alter table #temp add  TagsNames nvarchar(250)

DECLARE @ID int
DECLARE db_cursor CURSOR FOR SELECT Id FROM #temp
OPEN db_cursor  FETCH NEXT FROM db_cursor INTO @ID  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  IF OBJECT_ID('tempdb..#tags') IS NOT NULL DROP TABLE #tags

  select tag.TagName,ktg.ID into #tags from KnowledgeTags ktg
  inner join Tags tag on tag.ID = ktg.TagID
  where ktg.KnowledgeID=@ID 
  order by ktg.TagID       
  
  --
  DECLARE @tags nvarchar(250) = ''   
  DECLARE @kID int, @tagName nvarchar(250)
  DECLARE db_cursor2 CURSOR FOR SELECT ID,TagName FROM #tags
  OPEN db_cursor2  FETCH NEXT FROM db_cursor2 INTO @kID,  @tagName
  WHILE @@FETCH_STATUS = 0  
        BEGIN  
            SET @tags = @tags + ', ' + @tagName
        FETCH NEXT FROM db_cursor2 INTO @kID,  @tagName  
   END CLOSE db_cursor2 DEALLOCATE db_cursor2 
  --

  update #temp set TagsNames = RIGHT(@tags, LEN(@tags)-1) where ID = @ID

  FETCH NEXT FROM db_cursor INTO @ID  
  END CLOSE db_cursor DEALLOCATE db_cursor 
  SELECT * FROM #temp

Two columns from table A contain id's that are stored in a different table B how to select and get the data stored in table B?

You were close, the trick is using an alias to reference the table on each join, this should work:

select b1.namemovie,b2.namemovie from TableA 
inner join TableB b1 on TableA.favmovieid=b1.movid 
inner join TableB b2 on TableA.leastfavmovieid=b2.movid
where TableA.id=101

How to select the ids present in another table multiple columns?

Try below ;

Select A.id
From TableA A
Where A.id in (select TableA_first_col_id From TableB) OR
      A.id in (select TableA_second_col_id from TableB)

Inner join get two name columns with id from other table

The issue is you have two values of teams in the "game" you need to "lookup". This means you need two joins in order to get the different values for home and away.

Assuming

• game has home and away foreign key ID's from teams table...
• each game consists of two teams one "home" and one "away"

We simply join from games twice, once for the "home team" data, and once for the "away team" data. We will need to alias each join "h" (home) "a" (away)
and then update fields to use the correct alias.

Giving us:

SELECT g.home, g.away, 
       h.name as home_name, h.abbreviation as home_abbr, 
       a.name as away_name, a.abbreviation as away_abbr
FROM games g
INNER JOIN teams h
  ON g.home = h.id
INNER JOIN teams a
  ON g.away = a.id
WHERE g.date >= '2022-01-01'

Note:
For clarity I aliased all 3 tables and each of the fields in select/join/where

Outstanding questions
You said, " need get home and away teams names and abbreviations with union query from "teams" table."

Why is a union needed at all? Was this how you thought you could get both teams names/abbreviations? If so this approach doesn't make sense to me as a Join will suffice. unless you're looking for results for a game on different lines: which your example output didn't show. So i think union is the wrong approach given desired results. This isn't to say it couldn't be done with a union and a max and a group by; but it's more work and likely slower than a simple join.

How to join 2 tables with one of them containing multiple values in a single column

In the case you cannot create an additional database table in order to normalize the data...

Here's a solution that creates an ad hoc, temporary user_interests table within the query.

SELECT users.id user_id, username, interests, interests.interest
FROM users
LEFT JOIN (
  SELECT
    users.id user_id,
    (SUBSTRING_INDEX(SUBSTRING_INDEX(users.interests, ',', ui.ui_id), ',', -1) + 0) ui_id
  FROM users
    LEFT JOIN (SELECT id AS ui_id FROM interests) ui 
      ON CHAR_LENGTH(users.interests) - CHAR_LENGTH(REPLACE(users.interests, ',', '')) >= (ui.ui_id - 1)
) user_interests ON users.id = user_interests.user_id
LEFT JOIN interests ON user_interests.ui_id = interests.id
ORDER BY user_id, ui_id;

Outputs:

user_id | username | interest_ids | interest
--------+----------+--------------+---------
1       | fred     | 3,4,8,6,10   | fishing
1       | fred     | 3,4,8,6,10   | sports
1       | fred     | 3,4,8,6,10   | religion
1       | fred     | 3,4,8,6,10   | science
1       | fred     | 3,4,8,6,10   | philanthropy
2       | joe      | 7,11,8,9     | art
2       | joe      | 7,11,8,9     | science
2       | joe      | 7,11,8,9     | politics
2       | joe      | 7,11,8,9     | cooking

As you can see...

SELECT
  users.id user_id,
  (SUBSTRING_INDEX(SUBSTRING_INDEX(users.interests, ',', ui.ui_id), ',', -1) + 0) ui_id
FROM users
  LEFT JOIN (SELECT id AS ui_id FROM interests) ui 
    ON CHAR_LENGTH(users.interests) - CHAR_LENGTH(REPLACE(users.interests, ',', '')) >= (ui.ui_id - 1)

...builds and populates the temporary table user_interests with the users.interests field data normalized:

user_id | ui_id
--------+------
1       | 3
1       | 4
1       | 6
1       | 8
1       | 10
2       | 7
2       | 8
2       | 9
2       | 11

...which is then LEFT JOIN'ed between the users and interests tables.

Try it here: https://onecompiler.com/mysql/3yfhmgq3y

-- create
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(20),
  interests VARCHAR(20)
);

CREATE TABLE interests (
  id INT PRIMARY KEY,
  interest VARCHAR(20)
);

-- insert
INSERT INTO users VALUES (1, 'fred', '3,4,8,6,10'), (2, 'joe', '7,11,8,9');
INSERT INTO interests VALUES (1, 'business'), (2, 'farming'), (3, 'fishing'), (4, 'sports'), (5, 'technology'), (6, 'religion'), (7, 'art'), (8, 'science'), (9, 'politics'), (10, 'philanthropy'), (11, 'cooking');

-- select
SELECT users.id user_id, username, interests, interests.interest
FROM users
LEFT JOIN (
  SELECT
    users.id user_id,
    (SUBSTRING_INDEX(SUBSTRING_INDEX(users.interests, ',', ui.ui_id), ',', -1) + 0) ui_id
  FROM users
    LEFT JOIN (SELECT id AS ui_id FROM interests) ui 
      ON CHAR_LENGTH(users.interests) - CHAR_LENGTH(REPLACE(users.interests, ',', '')) >= (ui.ui_id - 1)
) user_interests ON users.id = user_interests.user_id
LEFT JOIN interests ON user_interests.ui_id = interests.id
ORDER BY user_id, ui_id;

Inspired by Leon Straathof's and fthiella's answers to this SO question.

Related Topics