SQL - Select Rows from Two Different Tables

SQL - Select rows from two different tables

I'm not sure if this is the best way of doing this, but it returns what I was looking for:

select itemID, itemTitle, deleted, userID
FROM(
SELECT i.id_itemID, i.itemTitle, M.deleted, M.userID
FROM
    MyList M
    right OUTER JOIN Items I ON I.itemID= M.itemID
) as MyTableItems
where itemID = 3 or itemID is null

Selecting different rows from two different tables in one query

You have to use either a Join or a Union, preferably a Join.

If you want to return lets say columns "1" and "2" in table "a" and columns "3" and "4" in table "b" use the following:

SELECT 1.a, 2.a, 3.b, 4.b FROM a join b ON key.a = key.b;

a and b refer to table names
1,2,3,4 refer to the attribute you wish to return.
key is the Primary and foreign key for your tables, essentially a shared column

You could also use a Union between two select statements, each selecting the appropriate data from a single table but the join is generally the way to go

For your example, the following would work:
SELECT a,b,c,d,e,f,g,h from table1 join table2;
If however you had a repeated attribute in both tables such as id or name, you would have to specify id.table1, id.table2, name.table1, name.table2 etc

SQL Select and Sort from Multiple tables

If by display 5 latest/newest products, included from each table you mean 5 latest from the combined result set, a view using a UNION will do the job:

create view testvw as select * from 
(
(select productname,manufacturer,arrivaldate from Clothes)
UNION
(select productname,manufacturer,arrivaldate from Toys)
UNION
(select productname,manufacturer,arrivaldate from Tools)
) x 
order by arrivaldate desc limit 5;

Select multiple rows from different tables

Consider using LEFT Join

SELECT mmbr.group_id, grp.group_name, grp.group_image, msg.time, msg.message FROM `GroupsMembers` AS mmbr 
JOIN `Groups` AS grp ON grp.group_id = mmbr.group_id 
LEFT JOIN `Messages` AS msg ON msg.group_id =mmbr.group_id AND msg.time = (SELECT MAX(time) FROM `Messages` WHERE group_id = mmbr.group_id) 
WHERE mmbr.user_id = '002' 

Read this tutorial https://www.w3schools.com/sql/sql_join_left.asp

How to select data from two different tables and count rows

SELECT A.id AS Aid, A.Name, A.status, SUM(B.completed) AS completed, SUM(b.exception) AS exception, COUNT(B.completed + B.exception) AS TOTAL
FORM TableA AS A
LEFT JOIN TableB AS B
  ON A.id = B.Aid
GROUP BY A.id, A.Name, A.status, B.Aid

Function to select rows from multiple tables based on conditions from different tables

The way I understand it you have two tables with schema like this:

table Control (Number int, Function nvarchar, Enable bit)
table Repository (Function nvarchar, TableName nvarchar)

Control and Repositories are related via Function column.

You also have a number of other tables and names of those tables are saved in Repositories tables. All those tables have ID column.

You want to get those table names based on a number and then select from all those tables by their ID column.

If that indeed is what you are trying to do, code bellow should be enough to solve your problem.

declare
    -- arguments
    @id int = 123,
    @number int = 123456,
    -- helper variables we'll use along the way
    @function nvarchar(4000),
    @tableName nvarchar(256),
    @query nvarchar(4000)

-- create cursor to iterate over every returned row one by one
declare cursor #tables readonly fast_forward
for
select
    c.Function,
    r.TableName
from [Control] as c
join [Repository] as r on r.Function = c.Function
where c.Number = @number
and c.Enable = 1

-- initialise cursor
open #tables
-- get first row into variables
fetch next from #tables
    into @function, @tableName

-- will be 0 as long as fetch next returns new values
while @@fetch_status = 0
begin
    -- build a dynamic query
    set @query = 'select * from ' + @tableName + ' where ID = ' + @id

    -- execute dynamic query. you might get permission problems
    -- dynamic queries are best to avoid, but I don't think there's another solution for this
    exec(@query)

    -- get next row
    fetch next from #tables
        into @function, @tableName
end

-- destroy cursor
close #tables
deallocate #tables

SQL SELECT only rows having MAX value of a column from two different tables

DISTINCT with CROSS APPLY:

SELECT DISTINCT t1.tail,
                t2.other_column,
                t3.[value]
FROM table1 t1
CROSS APPLY (
    SELECT  tailid,
            MAX([value]) as [value]
    FROM table2
    WHERE tailid = t1.id
    GROUP BY tailid
    ) as t3
INNER JOIN table2 t2
    ON t2.tailid = t3.tailid AND t3.[value] = t2.[value]
WHERE t1.some_coloum = "a sepecific string" 

How to get different rows from two tables in SQL Server

Your query is incorrect from UNION ALL on.

You can try it like this:

string searchquery = "SELECT * FROM NewList UNION ALL SELECT * FROM Restaurants ";

However, you need to be aware that:

1. It does not remove duplicate rows between the various SELECT statements (all rows are returned)
2. Each SELECT statement within the UNION ALL must have the same number of columns in the result sets with similar data types

Read here for more details.

Updated

You should use EXCEPT instead of UNION ALL if you want to get rows in one table that are not present in the other one.

Get rows from two tables with multiple matches in the other

One way of getting all records from table_1 which have more than one matching record in table_2 is to count the number of matching records in a subquery, and put a condition on it:

SELECT * 
FROM table_1 t1 
WHERE (SELECT count(*) 
       FROM table_2 t2 
       WHERE t1.field_1 = t2.field_1) > 1

If you're looking to have both sides of this in one query, you can combine them with a UNION:

SELECT * 
FROM table_1 t1 
WHERE (SELECT count(*) 
       FROM table_2 t2 
       WHERE t1.field_1 = t2.field_1) > 1
UNION
SELECT * 
FROM table_2 t2 
WHERE (SELECT count(*) 
       FROM table_1 t1 
       WHERE t1.field_1 = t2.field_1) > 1

---

Related Topics