'SQL statement selecting rows from a table dependent on information from another table

I have two different tables in a database, differing in number of columns. Now, I want to select a number of rows from the first table dependent on some variable (for example that the first column should have the value 1). However, I would also like to use information from my other table to select rows from my first table.

In my specific case, both table1 and table2contains the columns Group and Person. Table1 specify each person once, and declares what group he or she belongs to. However, people can also be part of secondary groups, which are listed in table2. That is, in table2, a person can be listed again with a new group number.

I would like to write an SQL statement where I select persons (that is, rows) from table1 (since I have more information about the persons in this table) that are members of a certain group, x. However, since a person can belong to several groups, I need to look through table2 as well, somehow.

How can I write this SQL statement?

sql


Solution 1:[1]

select t1.person_id,t1.group_id 
from table1
union all
select t2.person_id,t2.group_id
from table2

this will give you one table 

person group
person1 group1
person1 group2
person2 group3

no matter what tables they belong to.

This architecture seems silly however if the same data is in both tables.

Solution 2:[2]

If i have understood your question correctly, this query below will get you a person's details where the person is a member of a group 'X' and that relationship between person and that particular group 'X'is coming from a record maintained in either table1 or table2.

SELECT t1.*
FROM table1 t1 
LEFT OUTER JOIN table2 t2
    ON t1.Person = t2.Person
WHERE t1.Person = 'Y'
AND (t1.Group = 'X' OR t2.Group = 'X')

Solution 3:[3]

You will need some sort of identifier in both tables - like a candidate key. When you do your select you need to join the tables, example:

SELECT column_name(s)
FROM Table1 table_name1
INNER JOIN Table2 table_name2
ON table_name1.column_name=table_name2.column_name
WHERE table_name1.person = table_name2.person

Solution 4:[4]

You can use JOIN clause to combine rows from two or more tables, based on a related column between them.

Let's look at a selection in this example:

SELECT
       table1.user_name,
       table2.group_name,
       table1.address
FROM table1
INNER JOIN table2
ON table1.UserID = table2.ID;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mahmoud Gamal
Solution 2
Solution 3 Sklivvz
Solution 4 Farshad Badri

Related Questions