Help with querying two databases

Hello, I'm in a beginner level coding bootcamp course.
Below is pseudocode but I do not know the syntax to do it. I am basically have two tables in mysql and I need to run another query with the results I get from the first search, and in that query filter by all the charity id' found in the first query.

User favorites db query -> with results {
create an array to story final results
for loop to iterate over results{
Inside for loop query the favities charity with the id filter -> with results {
push to final results array.
}
}
return final results array
}

Reply 1

winterscoming

Do you have both tables inside the same database? it sounds to me like you're trying to apply a procedural solution to a problem which you should be able to solve entirely in SQL

SQL isn't a procedural language, it's a query language - so a typical approach is to JOIN data together and then perform your filtering on the JOIN'ed data.

For example, imagine you've got two tables called [Department] and [Employee] where a Department has a one-to-many relationship with Employee

 
+--------+----------+----------+
| DeptId | DeptName | DeptCode |
+--------+----------+----------+
|   1    |  Sales   |  DEP001  |
|   2    |   IT     |  DEP002  |
+--------+----------+----------+

+--------+--------+-----------+-----------+
| EmpId  | DeptId | FirstName | LastName  |
+--------+--------+-----------+-----------+
|   1    |   1    |  Bob      | Smith     |
|   2    |   1    |  Fred     | Flintstone|
|   3    |   2    |  Jane     | Bloggs    |
|   4    |   2    |  Betty    | Rubble    |
+--------+--------+-----------+-----------+

Note - the second table has a field called 'DeptId' which might be used for a JOIN with the first table's 'DeptId'

If you did a SQL join with those two tables using DeptId - like this:


SELECT * 
FROM [Department] d
JOIN [Employee] e ON e.DeptId = d.DeptId

This creates a set of query results where rows in the Employee table are JOIN'ed to rows in their corresponding row in the Department table. the JOIN .. ON syntax tells the database how to connect rows in each table together (i.e. two rows are joined together if their respective DeptID fields match. Note that the column/field doesn't need to be the same - i've just made them the same here so that you can see what's going on...)

Then you'd end up with query results which looks like this - data from both tables joined together - notice that there's duplication - this is OK because they're just query results (it doesn't affect the content of any of the tables) - it's entirely correct and intentional, this is how SQL is supposed to work when you've got rows from two or more tables being joined together:

 

+----------+------------+------------+---------+----------+-------------+-------------+
| d.DeptId | d.DeptName | d.DeptCode | e.EmpId | e.DeptId | e.FirstName | e.LastName  |
+----------+------------+------------+---------+----------+-------------+-------------+
|     1    |    Sales   |   DEP001   |    1    |     1    |    Bob      |  Smith      |
|     1    |    Sales   |   DEP001   |    2    |     1    |    Fred     |  Flintstone |
|     2    |    IT      |   DEP002   |    3    |     2    |    Jane     |  Bloggs     |
|     2    |    IT      |   DEP002   |    4    |     2    |    Betty    |  Rubble     |
+----------+------------+------------+---------+----------+-------------+-------------+

But since that's a query, you can extend it with filters on that JOIN'ed data. You can also modify the SELECT to only choose the columns you want from the JOIN. (e.g. there's no need to include the DeptId from both tables)

If all of this seems strange or difficult, then I'd strongly recommend you spend some time learning how to use SELECT/WHERE/JOIN/GROUP BY by following some of these lessons:
https://www.codecademy.com/learn/learn-sql

Help with querying two databases

Quick Reply

Related discussions

Latest

Trending

Trending

Articles for you