How to Select Three Table With Same Column Name But Different Values

How to LEFT JOIN three tables with the same column name

If the table was as follows (I've renamed user_id, post, and favorites columns to clarify their roles as I understand them)

-------------------------------------
|Users      |posts      |favorites  |
|-----------|-----------|-----------|
|id         |id         |id         |
|username   |title      |uid        |
|password   |post_text  |post_id    |
|           |uid        |           |
|           |fav_id     |           |
-------------------------------------

This sql code can be used to get what (I think) you want

SELECT * FROM favorites 
LEFT JOIN posts ON  favorites.id = posts.fav_id
LEFT JOIN users ON posts.uid = users.id
WHERE favorites.uid='$id' and posts.active=1

This should get all the details of favorite posts from the three tables for the given user id. Hope it works for you.

How to Join two SELECT queries having same column names but different row values in both tables

UNION and UNION ALL are SQL operators used to concatenate 2 or more result sets. This allows us to write multiple SELECT statements, retrieve the desired results, then combine them together into a final, unified set.

The main difference between UNION and UNION ALL is that:

UNION: only keeps unique records

UNION ALL: keeps all records, including duplicates

UNION Example:

SELECT column1 AS datacheck from table1 
  UNION 
SELECT column1 AS datacheck from table2

Result:

+-----------+
| datacheck |
+-----------+
|   data2   |
+-----------+

UNION ALL example:

SELECT column1 AS datacheck from table1 
  UNION ALL
SELECT column1 AS datacheck from table2

Result:

+-----------+
| datacheck |
+-----------+
|   data2   |
|   data2   |
+-----------+

Select data from multiple tables with the same column names

That's because the "FROM" clause does not have the table name. Also, add aliases to the table names so that it'll be easier to use them across the query.

SELECT 
      usr.FirstName, 
      usr.LastName, 
      accounts.OtherNames,
      accounts.Gender, 
      accounts.DateOfBirth, 
      inst.Name as School, 
      eduLevel.Name as Study
FROM 
      Users usr, UserAccounts accounts, Institutions inst, EducationLevels eduLevel
....

How to join two tables that are exactly the same column names with third table with unique column names to create new view?

Looking at the expected output, and the fact that table1 and table2 are structurally identical, you probably want UNION:

SELECT table1.foo, table1.bar, table3.*
FROM      table1
LEFT JOIN table2 ON table1.lookup_column = table2.lookup_column
LEFT JOIN table3 ON table1.lookup_column = table3.lookup_column AND table3.date >= '2017-10-01'

UNION ALL

SELECT table2.foo, NULL,       table3.*
FROM      table1
LEFT JOIN table2 ON table1.lookup_column = table2.lookup_column
LEFT JOIN table3 ON table1.lookup_column = table3.lookup_column AND table3.date >= '2017-10-01'

Join multiple tables with same column name

You might want to consider adding expiredAt to your general table, and removing it from the others, to remove duplication in the schema, and to make this particular query simpler.

If you need to stick with your current schema, you can use table aliases to resolve the name ambiguity, and use two joins and a union to create the result you are looking for:

SELECT g.id, g.scenario, f.expiresAt 
FROM generalTable g
JOIN facebookTable f
ON g.id = f.id
UNION ALL
SELECT g.id, g.scenario, c.expiresAt 
FROM generalTable g
JOIN chiefTable c
ON g.id = c.id;

The outer join approach mentioned in another answer would also solve the problem.

SQL: Query many tables with same column name but different structure for specific value

Ok, try this, there are three variables; column, colValue and preview. Column should be the column you're checking equality on (Group_ID), colValue the value you're looking for (Unused_Group) and preview should be 1 to view what you'll delete and 0 to delete it.

Declare @column     Nvarchar(256),
        @colValue   Nvarchar(256),
        @preview    Bit

Set     @column     = 'Group_ID'        
Set     @colValue   = 'Unused_Group'
Set     @preview    = 1 -- 1 = preview; 0 = delete

If      Object_ID('tempdb..#tables') Is Not Null Drop Table #tables
Create  Table #tables (tID Int, SchemaName Nvarchar(256), TableName Nvarchar(256))

--      Get all the tables with a column named [GROUP_ID]
Insert  #tables
Select  Row_Number() Over (Order By s.name, so.name), s.name, so.name
From    sysobjects so
Join    sys.schemas s
        On  so.uid = s.schema_id
Join    syscolumns sc
        On  so.id = sc.id
Where   so.xtype = 'u'
And     sc.name = @column

Select  *
From    #tables

Declare @SQL Nvarchar(Max),
        @schema Nvarchar(256),
        @table Nvarchar(256),
        @iter Int = 1

--      As long as there are tables to look at keep looping
While   Exists (Select  1
                From    #tables)
Begin
        --      Get the next table record to look at
        Select  @schema = SchemaName,
                @table = TableName
        From    #tables
        Where   tID = @iter

        --      If the table we're going to look at has dependencies on tables we have not 
        --      yet looked at move it to the end of the line and look at it after we look 
        --      at it's dependent tables (Handle foreign keys)
        If      Exists (Select  1
                        From    sysobjects o
                        Join    sys.schemas s1
                                On  o.uid = s1.schema_id
                        Join    sysforeignkeys fk
                                On  o.id = fk.rkeyid
                        Join    sysobjects o2
                                On  fk.fkeyid = o2.id
                        Join    sys.schemas s2
                                On  o2.uid = s2.schema_id
                        Join    #tables t
                                On  o2.name = t.TableName Collate Database_Default
                                And s2.name = t.SchemaName Collate Database_Default
                        Where   o.name = @table
                        And     s1.name = @schema)
        Begin
                --      Move the table to the end of the list to retry later
                Update  t
                Set     tID = (Select Max(tID) From #tables) + 1
                From    #tables t
                Where   tableName = @table
                And     schemaName = @schema

                --      Move on to the next table to look at
                Set     @iter = @iter + 1
        End
        Else
        Begin
                --      Delete the records we don't want anymore
                Set     @Sql =  Case
                                When    @preview = 1 
                                Then    'Select * ' -- If preview is 1 select from table
                                Else    'Delete t ' -- If preview is not 1 the delete from table
                                End +
                                'From    [' + @schema + '].[' + @table + '] t
                                Where   ' + @column + ' = ''' + @colValue + ''''

                Exec    sp_executeSQL @SQL;

                --      After we've done the work remove the table from our list
                Delete  t
                From    #tables t
                Where   tableName = @table
                And     schemaName = @schema

                --      Move on to the next table to look at
                Set     @iter = @iter + 1

        End
End

Turning this into a stored procedure would simply involve changing the variables declaration at the top to a sproc creation so you would get rid of...

Declare @column     Nvarchar(256),
        @colValue   Nvarchar(256),
        @preview    Bit

Set     @column     = 'Group_ID'        
Set     @colValue   = 'Unused_Group'
Set     @preview    = 1 -- 1 = preview; 0 = delete
...

And replace it with...

Create  Proc DeleteStuffFromManyTables (@column Nvarchar(256), @colValue Nvarchar(256), @preview Bit = 1)
As
...

And you'd call it with...

Exec    DeleteStuffFromManyTable 'Group_ID', 'Unused_Group', 1

I commented the hell out of the code to help you understand what it's doing; good luck!

---

Related Topics