Turn on thread page Beta
    • Thread Starter
    Offline

    2
    ReputationRep:
    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
    }
    Offline

    18
    ReputationRep:
    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

    Code:
    +--------+----------+----------+
    | 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:
    Code:
    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:
    Code:
    +----------+------------+------------+---------+----------+-------------+-------------+
    | 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
 
 
 
Reply
Submit reply
Turn on thread page Beta
Updated: March 14, 2018
The home of Results and Clearing

2,816

people online now

1,567,000

students helped last year

University open days

  1. SAE Institute
    Animation, Audio, Film, Games, Music, Business, Web Further education
    Thu, 16 Aug '18
  2. Bournemouth University
    Clearing Open Day Undergraduate
    Fri, 17 Aug '18
  3. University of Bolton
    Undergraduate Open Day Undergraduate
    Fri, 17 Aug '18
Poll
Do you want your parents to be with you when you collect your A-level results?

The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

Write a reply...
Reply
Hide
Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.