Need some SQL query assist

[MultiTech]

Lookin for some MySQL query help (rookie here).

I have a table with IDs, and other tables with various datas that I want to filter based on the ID being inside the first mentioned table.

I’m this close, but some syntax is killing me here.

something like this, but it’s not right.

Any help would be appreciated.

All I keep finding is stuff about Joins… I don’t want to JOIN anything; I have a list of IDs that their data needs to be cleared out of the active system.

[Austin]

So Dead_Patients.‘Patient ID’ is a list?
Cause the better solution to me is
SELECT c.* FROM CARDIAC_MEDS AS c LEFT JOIN DEAD_PATIENTS ON Patient_Link=‘Patient id’

This would return the full row from CARDIAC_MEDS where Patient_Link is in Dead_Patients

[MultiTech]

What is the c stuff???

---

Austin_Lambeth:

Dead_Patients.‘Patient ID’

Dead_Patients is a table; Patient ID is the only column in the table.

If I were to make this in AppSheet syntax:

select(CardiacMeds[MedsID], 
   in([_thisrow].[Cardiac_Meds_Patient_Link], Dead_Patients[Patient ID])
)

I don’t know how to do the IN() stuff for sql

[Austin · Accepted solution]

The c.* and AS c is just so that you can only return the columns from the CARDIAC_MEDS Table.
Try
SELECT * FROM CARDIAC_MEDS WHERE Cardiac_Meds.Patient_Link IN (SELECT * FROM Dead_Patients)
A Join would work here although I think it would be a RIGHT JOIN since you only want the results that are in Dead_Patients.

[MultiTech]

Austin_Lambeth:

SELECT * FROM CARDIAC_MEDS WHERE Cardiac_Meds.Patient_Link IN (SELECT * FROM Dead_Patients)

This didn’t throw an error, but did not produce any records.

SELECT * FROM Cardiac_Meds WHERE Cardiac_Meds.Patient_Link IN (SELECT * FROM Dead_Patients);

@Austin_Lambeth I appreciate the help!

[Austin]

I recommend DBforge for mysql if you are going to be doing lots of stuff in mysql because that’s the tool I use plus it is free if you don’t need to put lots of entries/tables together in a view. It’s basically sql studio manager for mysql.

[WillowMobileSys]

Try this instead

SELECT * FROM Cardiac_Meds WHERE Cardiac_Meds.Patient_Link IN (SELECT ‘Patient ID’ FROM Dead_Patients);

[MultiTech]

I just tried that, no go.

I"m sure there’s data here… but let me do some due-diligence and find an actual record to ensure there is something for the query.

[WillowMobileSys]

Make sure the letters case is correct.

[MultiTech]

@Austin_Lambeth @WillowMobileSystems You two rock. It was due to there being no actual output. I would have sworn that after 3 years at least 1 record would have made it into this category… but I guess not.

I took a patient ID from an actual CardiacMed record and added it to the dead patient list - then re-ran the query and it found that patients data.

So it totally worked. Thanks guys, I think I get the basics now.

[Austin]

SELECT f.* FROM ApplicantReqMatchTable AS m LEFT JOIN FullApplicantData AS f ON m.UniqueApplicantID=f.UniqueApplicantID

So here’s a comparable query from one of my databases. This returns all FullApplicantData for every row in ApplicantReqMatchTable. You would just do

SELECT c.* FROM Dead_Patients AS d LEFT JOIN Cardiac_Meds AS c ON c.Patient_Link=d.‘Patient ID’

This should return the same results just more efficiently.

My ultimate mysql resource: https://www.w3schools.com/sql/sql_ref_mysql.asp

[WillowMobileSys]

@MultiTech_Visions @Austin_Lambeth

Started to post this earlier but since my SQL is rusty, I wanted to double check myself.

You can simplify the above SQL as follows:

SELECT cm.* FROM Cardiac_Meds cm, Dead_Patients dp 
       WHERE cm.Patient_Link = dp.`Patient ID`

---

The “LEFT JOIN…ON” is the formal declaration of a left outer join. The above format is a simplified version and for MOST queries is perfectly suitable.

However, in more complex queries, the order of the tables and the comparison function ("=") CAN make a difference in the returned results. When in doubt, use “LEFT JOIN…ON”

---

Some other tidbits:

• The proper term for the “cm” and “dp” characters is “alias”. They are table name alias’. They are meant to simplify the SQL and make it easier to read.

• In SQL, join just means a way to “connect two or more tables” to compare or combine columns (yes…you can pull columns from multiple tables into a single result).

• For columns that have a space in the name (e.g. Patient ID) you need to use the back slanted single quote.

• Using the SELECT within the SELECT like we did above for the IN clause is normally avoided because it is not efficient in larger datasets. This is mainly due to bad SQL optimization.

[Douglas_Thom]

Hello, @MultiTech_Visions,

Your original request mentioned something about deletes. I have prepared a “SQL Fiddle” to illustrate all of the concepts. In SQL there are different ways to do things depending on what you’re doing.

http://sqlfiddle.com/#!9/8171b0/11

To use it, click “Build Schema” on the left, then “Run SQL” on the right. There are comments throughout to outline what’s going on.

In your case, I would go for the straight JOIN version of the delete. It allows the database to figure out the best way to execute the query.

If you had a more complex query, I’d use the IN to show your logic separately in the subquery so it’s clear what you’re doing.

If you had MANY rows to reference in the subquery then I’d use the EXISTS one because it stops looking as soon as it finds any example of what you’re looking for.

Hope that helps,

Doug