当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - SQL OR Operator on different tables and column names

问题描述:

I know that I can use the OR SQL operator on the same column of a table like the following:

SELECT * FROM users WHERE last_name = 'Peter' OR last_name = 'Smith';

But the situation I have right now is that I'm trying to use the OR operator on two different tables (different column names). Is that possible? How can I achieve that in an SQL query?

And yes there is a foreign key column that links one table to the primary key column of the other table.

Thanks in advance for any help.

网友答案:

If the fields in the different tables have the same name, you can distinguish them with tablename.fieldname, if the tables have the same name (in different schemas), you can further qualify the names with schemaname.tablename.fieldname.

Of course, all tables referenced in the WHERE and SELECT clauses should be included in the FROM clause.

Note: If a table is aliased in the FROM, the alias should be used instead of the table name.


You cannot display (or use in WHERE conditions, etc...) fields from a table that not included in the FROM; however, you can use subqueries on those tables.

Examples:

...
FROM table1 AS t1
WHERE t1.field1 = somevalue 
   OR EXISTS (
         SELECT * 
         FROM table2 AS t2 
         WHERE t2.somefield = someothervalue
   )
...

or

SELECT t1.field1
, (SELECT t2.somefield FROM table2 AS t2 WHERE t2.anotherfield = somevalue LIMIT 1) AS t2Val
FROM table1 AS t1

...

网友答案:

Yes , it is possible

SELECT users.* FROM users,countries WHERE users.last_name = 'Peter' OR countries.name = 'mexico' AND users.idCountry=countries.id;
分享给朋友:
您可能感兴趣的文章:
随机阅读: