SQL selecting by many related foreign keys
In the beginning I'd like to mention, that I was trying to search for simillar problems but I didn't found the solution. I am doing my best to select disease from my database by typing in few id keys from sympthoms table. The relation between Disease and Sympthoms tables are many-to-many with connecting table disease_symptoms which holds FK fields(disease_id and symptoms_id)
As I'm asking for just one ID it works fine:
SELECT * FROM disease JOIN disease_symptoms ON disease.id=disease_symptoms.disease_id where disease_symptoms.symptoms_id=14 ORDER BY `disease`.`probability` DESC
But as I'm trying to ask for more ID's it doesn't:
SELECT * FROM disease JOIN disease_symptoms ON disease.id=disease_symptoms.disease_id where disease_symptoms.symptoms_id=14 AND disease_symptoms.symptoms_id=15 ORDER BY `disease`.`probability` DESC
(I am trying to select flu (id=1) from disease, it is related with sympthoms rised temperature (id 14) and cough (id 15)) Later I'd like to make checkbox list with sympthoms, which will return matching disease. (php/symfony).
Where am I making mistake?
Thank You in advance :)
You want the disease(s) that have both symptom 14 and 15, right? So your disease has to be in the list of all diseases that have 14 as a symptom, and in the list of diseases that have 15 as a symptom.
That could be:
SELECT * FROM disease WHERE ID in (select disease_id from disease_symptoms where symptoms_id=14) and ID in (select disease_id from disease_symptoms where symptoms_id=15) order by probability DESC
SQL selecting by many related foreign keys, You want the disease(s) that have both symptom 14 and 15, right? So your disease has to be in the list of all diseases that have 14 as a There are several methods to find the foreign keys referencing a table. Here I will go over the different methods to find the foreign keys references to a specific table. Using sp_fkey. One among the easiest way to list all foreign key referencing a table is to use the system stored procedure sp_fkey. Here is an example of using sp_fkey.
Your WHERE condition will never return any data.
disease_symptoms.symptoms_id=14 AND disease_symptoms.symptoms_id=15
symptoms_id can't be 14 AND 15 at the same time.
Try the following instead.
disease_symptoms.symptoms_id IN (14, 15)
Ok, from your comment I've a better idea of what you're after. Try this,
SELECT * FROM disease d JOIN disease_symptoms ds1 ON d.id = ds1.disease_id JOIN disease_symptoms ds2 ON d.id = ds2.disease_id WHERE ds1.symptoms_id = 14 AND ds2.symptoms_id = 15
By using aliases you can join the disease table to the diseasse_symptoms table twice, once for each sympton you're interested in.
15. Working with foreign keys, The context menu for the selected rows will give you the choice in which SQL tab you want the generated SELECT to be pasted. This is similar to the Put SELECT To enforce the link between data in the vendor_groups and vendors tables, you need to establish a foreign key in the vendors table. A foreign key is a column or a group of columns in one table that uniquely identifies a row of another table (or the same table in case of self-reference). To create a foreign key, you use the FOREIGN KEY constraint.
SELECT * FROM disease_symptoms JOIN disease ON disease_symptoms.disease_id = disease.id WHERE disease_symptoms.symptoms_id IN (14, 15) ORDER BY `disease`.`probability` DESC
You just need to select the relation from an opposite direction. This will return all diseases which have the given symptoms.
SQL FOREIGN KEY Constraint, To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL That way you could just run something like ("select ? from coach c, player p where team_id = ?). Also I think it's good practice to use the same name for the foreign key as the corresponding field (i.e. the id field in the team table should be called "team_id" just as the FK). – ryoung Jul 5 '11 at 19:11
List foreign keys in SQL Server database, Useful T-SQL queries for SQL Server to explore database schema. Query below returns foreign key constrants defined in a database. on pk_tab. object_id = fk.referenced_object_id cross apply (select col. If foreign key consists of multiple columns (composite key) it is still represented as one row. Both OLTP and OLAP databases take advantage of the same approach for enumerating all foreign key constraints. To select a subset of all foreign key constraints for a particular referenced table, all you need to do is add a WHERE clause designating the referenced table name.
List table columns with their foreign keys in SQL Server database , List table columns with their foreign keys in SQL Server database. Piotr Kononow select schema_name(tab.schema_id) + '.' + tab.name as Sample results. Article for: SQL Server . Azure SQL Database MySQL PostgreSQL MariaDB IBM Db2 Teradata Vertica. Query below returns all columns from all tables in a database with a foreign key refererence if column has one.
SQL FOREIGN KEY, In this case, an SQL FOREIGN KEY CONSTRAINT should be created with the ' customer1' table which is related to the SQL PRIMARY KEY I suggest to use a GROUP BY instead of a sub-query in your SELECT clause. It performs better for many data: SELECT. A.id, A.column1, A.column3, COUNT(B.Aid) As 'Number Of Foreign Keys' --foreign key