CODEWITHSUNDEEP
mysql
tvdw
tvdw

Reputation: 3

Retrieving rows based on a list of IDs in MySQL

I'm trying to retrieve all the rows in a table with children variables where the Foreign Key of those rows is equal to the Primary Key of rows in a table with parent variables.

Graphically it looks something like this:

Table 1. This table contains the parent rows.

ID  |  variable  |  variable  |  etc.
1   |  XX        |  BB        |  ...
2   |  YY        |  AA        |  ...

Table 2. This table contains the children rows. 

ID  |  FK (parent)  |  variable  |  etc.
1   |  1            |  BB        |  ...
2   |  1            |  AA        |  ...
3   |  1            |  AA        |  ...
4   |  2            |  AA        |  ...
5   |  3            |  AA        |  ...

I'm obviously not an expert in SQL, what I would normally do in another programming language is writing a loop that cycles through every row in the parent table, and then checks the children table if there is a match. I have, however, no idea of what would be the most efficient approach here. The parent table will have 50+ rows. The children table has 8000+ rows.

UPDATE: I want to dump the relevant data from the children table in a new table. So I do not want a combined table with data from the parent and children table, which is what a JOIN does I think.

UPDATE 2: I managed to get what I wanted through:

INSERT INTO NewTable 
select columns
from ChildrenTable t
    inner join ParentTable p
        on t.parentId = p.Id

Thanks for the help!

Upvotes: 0

Views: 146

Answers (4)

Templar
Templar

Reputation: 1871

You wrote:

I want to dump the relevant data from the children table in a new table. So I do not want a combined table with data from the parent and children table, which is what a JOIN does I think.

Well JOIN just combines two or more relevant data from chosen tables. What I mean is you can SELECT whatever columns you want i.e. if you have such tables (a bit updated columns from your original table):

parent-table

ID  |  variable1  |  variable2  |  etc.
1   |  XX         |  BB         |  ...
2   |  YY         |  AA         |  ...

child-table

ID  |  FK-ID        |  variable  |  etc.
1   |  1            |  BB        |  ...
2   |  1            |  AA        |  ...
3   |  1            |  AA        |  ...
4   |  2            |  AA        |  ...
5   |  3            |  AA        |  ...

And you want to retrieve only ID from first table, variabl2 from first table and variable from second one you would write

SELECT ID.parent-table, variable2.parent-table, variable.child-table
FROM parent-table 
JOIN child-table ON parent-table.ID = FK-ID.child-table;

Or if you don't like joins you can ignore them and just get data from both tables and specify where clause i.e.

SELECT ID.parent-table, variable2.parent-table, variable.child-table
FROM parent-table, child-table
WHERE parent-table.ID = FK-ID.child-table;

Both above written queries are equivalent. If you want you can create a new table, let's call it parent-child-table with that data which will be a separate copy. Or if you need to use it a lot you can create a VIEW (you can google about it) which is virtual table (it stores a query), for example let's call it parent-child-view if you make some changes in parent-table and child-table changes will be reflected in parent-child-view but if you create a separate new table parent-child-table changes won't be reflected because it's just a copy.

Upvotes: 0

Masroor Ejaz
Masroor Ejaz

Reputation: 1

select * from child_table_name as tblchild, parent_table_name as tblparent, where tblchild.fk_column_name=tblparent.id

Upvotes: 0

010 Pixel
010 Pixel

Reputation: 165

You can Try using:

select * from table2, table1 where table2.fk = table1.id

Upvotes: 0

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

You can try like this-

Select * from table1 left join table2 on table1.id = table2.fk

Upvotes: 1

Related Questions