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.
Solved! Go to 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.
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
Dead_Patients is a table; Patient ID is the only column in the table.
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
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.
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!
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.
Try this instead
SELECT * FROM Cardiac_Meds WHERE Cardiac_Meds.Patient_Link IN (SELECT โPatient IDโ FROM Dead_Patients);
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.
Make sure the letters case is correct.
@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.
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
@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.
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
User | Count |
---|---|
44 | |
35 | |
24 | |
24 | |
15 |