0
投票
2答案
58 次观看

通过权限限制访问

感谢您查看此内容。我有一个数据库,其中包含用户,角色和user_roles。 我要实现的目标是登录,该登录将选择具有Admin或Associate权限的用户。然后,登录名使用名称和密码来允许访问。 到目前为止,我的SQL语法是- SELECT * FROM users LEFT JOIN ON user_roles ON user.id=user_roles.userid AND roleid IN (Administrator, Associate) WHERE username = '$username' AND password = '$password'"; 我...

0
投票
1答案
1034 次观看

MySQL Select语句-两张表,按其他表的数量对一个表进行排序

所以我为我编写的自定义帖子系统构建了一个投票系统。 我希望能够按“投票最多”,“最喜欢”等进行排序。 我有两个桌子。 条目:ID,标题,帖子 投票:ID,EntryID,结果 我希望能够查询每个条目的投票表并查看有多少票,然后按每个表有多少票对条目进行排序。我搞砸了联接等,似乎无法弄清楚。有什么建议吗?

4
投票
2答案
10307 次观看

为什么我的MySQL MATCH()AGAINST()查询因LEFT JOIN数据库中的列而失败?

我有一个类似于以下内容的MySQL查询: SELECT * FROM products LEFT JOIN descriptions ON products.DescriptionID = descriptions.ID WHERE MATCH (name, overview, specs) AGAINST ('ram'); 我尝试使用MATCH() AGAINST()搜索的所有列都是FULLTEXT,但是在phpMyAdmin中进行测试时出现以下错误: #1210 - Incorrect arguments to MATCH 如果我只有MATCH列,那么它不会...

4
投票
2答案
259 次观看

MySQL:有条件地进行LEFT JOIN的方法吗?(或类似的?)

我正在编写一个查询,以获取products表中产品的 ALL ,并且每个产品的销售价格 IF 在specials表。 我正在寻找的东西是这样的: SELECT * FROM products P IF (S.specials_date_available <= NOW() AND S.expires_date > NOW()) { // The sale has started, but has not yet expired LEFT JOIN specials S ON P.products_id = S.products_id } 我认识...

1
投票
3答案
208 次观看

当我运行左联接时内部发生了什么?

我对SQL上的左联接有一个疑问:我想知道SQL服务器如何执行左联接? 假设我有两个桌子。 人 id 名称 电话 id person_id 电话 执行时: select name, phone from people left join phone on people.id = phone.person_id ...我想知道SQL Server如何处理查询字符串。 我的猜测是: 选择所有行的人 以匹配条件开始匹配电话行。在这种情况下,people.id = phone_person_id。 将“电话”值显示为null(如...

0
投票
1答案
309 次观看

LINQ查询以返回在联接中没有关联记录的记录列表

在结果集是那些没有关联记录的记录时,返回查询的最有效方法是什么?我正在使用LINQ和Visual Basic,并一直试图构造一个左外部联接,然后对关联表中id为null或null的那些记录进行过滤,但是对此太新了,以至于我什么都无法工作。/ p> 想做类似的事情: Dim query = From s In db.spaces _ Group Join t In db.tickets On s.spaceid Equals t.spaceid Into rs = Group _ Select rs End Function 我想返回减去该结果集的整个左表...

1
投票
3答案
793 次观看

从PHP检索MYSQL Join值

我有两个或多个表,这些表在相同的列名下保存值。现在,当我连接这两个表时,列名保持不变。在PHP($ row ['name'])中检索这些值时,我遇到了问题,因为列'name'被使用了两次。 是否有可能将查询内的这两列分开? SELECT * FROM stories s JOIN authors a ON a.id = s.authorid 表 故事 ID,名称,内容,日期 表 作者 ID,名称,日期 当我同时加入这两个时,我得到一个带有类似“名称”列的表。 总有两个表分开吗,以便作者表前面有一个前缀?例如。authors_name / auth...

2
投票
2答案
1450 次观看

GROUP_CONCAT和LEFT_JOIN问题-并非返回所有行

假设我的数据库方案如下: T_PRODUCT id_product (int, primary) two entries: (id_product =1) , (id_product =2) T_USER id_user (int, primary) id_product (int, foreign key) name_user (varchar) two entries: (id_product=1,name_user='John') , (id_product=1,name_user='Mike') 如果我运行第一个查询来获取所有产品及其用户(如果有),我将得到: ...

1
投票
1答案
1488 次观看

使用@JoinTable和SQLQuery来获取联接

我有一个具有属性“ latestHistory”的映射实体,该实体通过连接表进行映射,例如: class Record { @OneToOne(cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.LAZY, optional = true) @JoinTable(name = "latest_history_join_view", joinColumns = { @JoinColumn(name = "record_id") }, invers...

0
投票
2答案
1379 次观看

跨多个模型构建条件语句(CakePHP 1.2.5)

假设我有3个模型:用户,地区和国家/地区。 User belongsTo Region Region belongsTo Country 每个模型都使用Containable行为。我正在尝试查找代码为“ US”的国家/地区的用户。这是我要尝试的: $users = $this->User->find('all', array( 'conditions' => array('Country.code' => 'US'), 'contain' => array('Region.Country'), )); CakePH...

3
投票
3答案
5916 次观看

MySQL:LEFT JOIN ..从表1中选择全部,即使表2中没有?

我要加入一些表格进行选择 如果在第二,第三,第四表中没有任何匹配项,只要第一个表具有匹配项,我仍然希望提取结果。我以为LEFT JOIN可以这样做,但事实并非如此。 这是完整的查询: SELECT cart_product.*, prod_drop_products.prod_drop_product_name, everlon_sheet.*, cart_product.product_id AS product_id FROM cart_product LEFT JOIN everlon_sheet ON cart_product.product_id = everlon...

3
投票
1答案
2421 次观看

Select * from table1 where JID is not in table2 with table2.uid also not being 11111

I want to select everything from table one, which contains a column JID. These are available things the player can learn. But there is a table2 which has a list of things the player has already learned. So if JID is in table2, it has been learned, and I do not want that selected from table 1. ...

1
投票
1答案
1603 次观看

Efficient MySQL query to find entries in A where not matched in B

I have a couple of tables (products and suppliers) and want to find out which items are no longer listed in the suppliers table. Table uc_products has the products. Table uc_supplier_csv has supplier stocks. uc_products.model joins against uc_suppliers.sku. I am seeing very long queries when tr...

1
投票
4答案
3541 次观看

Using multiple left joins to calculate averages and counts

I am trying to figure out how to use multiple left outer joins to calculate average scores and number of cards. I have the following schema and test data. Each deck has 0 or more scores and 0 or more cards. I need to calculate an average score and card count for each deck. I'm using mysql for co...

2
投票
4答案
309 次观看

Why can I select something out of left join on a NULL column?(with contrived example to reproduce it locally,probably a bug!)

VERSION I'm using Server version: 5.1.36-community-log MySQL Community Server (GPL) I've finally contrived a easy example to reproduce it easily! setup: create table t1(id integer unsigned,link integer unsigned); create table t2(id integer unsigned auto_increment,primary key(id)); create table...

0
投票
2答案
379 次观看

SQL DELETE falling over in PHPmyAdmin

SQL DELETE falling over in PHPmyAdmin. Can't work out why; is it because of LEFT OUTER JOIN? SELECT * FROM `User` LEFT OUTER JOIN `freshersdata` ON `User`.`username`=`freshersdata`.`username` WHERE (`freshersdata`.`username` IS null) AND (`User`.`Persistent`!=1) This SQL query is fal...

73
投票
3答案
69698 次观看

Rails ActiveRecord :joins with LEFT JOIN instead of INNER JOIN

I have this code User.find(:all, :limit => 10, :joins => :user_points, :select => "users.*, count(user_points.id)", :group => "user_points.user_id") which generates following sql SELECT users.*, count(user_points.id) FROM `users` INNER JOIN `user_...

5
投票
5答案
11196 次观看

Linq to entities Left Join

I want to achieve the following in Linq to Entities: Get all Enquires that have no Application or the Application has a status != 4 (Completed) select e.* from Enquiry enq left outer join Application app on enq.enquiryid = app.enquiryid where app.Status <> 4 or app.enquiryid is null Ha...

1
投票
7答案
2182 次观看

left join with special condition on right table

don't know if this is possible.. I'm using sqlite3 schema: CREATE TABLE docs (id integer primary key, name string); CREATE TABLE revs (id integer primary key, doc_id integer, number integer); I want to select every job joined with only one of its revisions, the one with the highest number. How ...

3
投票
3答案
2826 次观看

How to build HQL query, thats joins subtables marked LAZY, automatically?

I have some entity: public class Album extends GenericAuditedEntity { @OneToMany(fetch = FetchType.LAZY) private Set<Item> itemSet = new HashSet<Item>(); } And when i run HQL like this: em.createQuery("select a from Album a").getResults() it produses many SQL querie...

0
投票
4答案
300 次观看

Left join on the same table

i dont remember how to join a table to itself.. my table is: | id | proc | value | kind | | 1 | 1 | foo | a | | 2 | 1 | bar | b | | 3 | 2 | some | a | And i need to retrieve the value col where proc is $proc and kind is both 'a' and 'b'.. well, i need to do have that ...

61
投票
7答案
408155 次观看

SQL SELECT from multiple tables

How can I get all products from customers1 and customers2 include their customer names? customer1 table cid name1 1 john 2 joe customer2 table cid name2 p1 sandy p2 linda product table pid cid pname 1 1 phone 2 2 pencil 3 p1 pen 4 p2 paper Result should be like this pid ...

0
投票
1答案
89 次观看

How can I select records in MySQL when a foreign key doesn't return anything?

SELECT videos.id, videos.game_id, videos.xbox360, videos.ps3, videos.pc, videos.wii, videos.other, videos.thumbnail, videos.vid_info, videos.sdvid, videos.hdvid, UNIX_TIMESTAMP( videos.date_added ) , game_data.name, AVG( video_ratings.rating ) FROM videos, game_data, video_ratings WHERE vi...

6
投票
1答案
6339 次观看

NHibernate 2.1: LEFT JOIN on SubQuery with Alias (ICriteria)

I am basically trying to create this query with NHibernate ICriteria interface: SomeTable 1:n AnotherTable SomeTable has columns: PrimaryKey, NonAggregateColumn AnotherTable has columns: PrimaryKey, ForeignKey, AnotherNonAggregate, YetAnotherNonAggregate SELECT table1.NonAggregateColumn...

1
投票
3答案
133 次观看

MySQL Query with some type of join? Not sure

If anyone could recommend a good book for learning mySQL as well, that would be great :). I have two tables, tags, codes_tags CREATE TABLE `tags` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY ...

0
投票
3答案
780 次观看

Complex SQL query, involving three tables and variable table names

I have two different tables from which I need to pull data blogs which has the following column blog_id and another table which has a variable name, like $blog_id . "_options Which has the following columns: option_id, option_name, option_value For example: option_id = 1, option_name...

2
投票
5答案
7576 次观看

SQL LEFT outer join with only some rows from the right?

I have two tables TABLE_A and TABLE_B having the joined column as the employee number EMPNO. I want to do a normal left outer join. However, TABLE_B has certain records that are soft-deleted (status='D'), I want these to be included. Just to clarify, TABLE_B could have active records (status= n...

1
投票
1答案
182 次观看

MySQL create view problem,

I'm using a view as a table to join data on 3 tables: create view category_list as select forum_categories.*, max( forum_answer.a_id ) as latest_answer_id from forum_categories left join forum_question on forum_question.catid = forum_categories.id left join forum_answer on forum_answer.quest...

1
投票
2答案
513 次观看

SubSonic 2.2 Left join error

I know this may have been answered but SubSonic 2.2 causes an error in the SQL provider when trying to do a Left join (Left inner join to subsonic) instead of creating SQl like SELECT * FROM table1 LEFT JOIN table 2 ON table1.id = table2.id it creates: SELECT * FROM table1 LEFT INNER JOIN ta...

0
投票
2答案
266 次观看

MySQL complex query not yielding proper results

I have two table: Vehicles(Id, VIN) and Images(Id, VehicleId, Name, Default). I need to select the vehicles VIN and its default picture to display in a table. The problem I am having is that if a default picture is not set I still would like to select an image to display if it exists. If no im...