in my Laravel app I have 3 tables : users, documents and type_documents, the user have multiple documents and document have one type_document I want select the types that are not used in documents table for the current user with eloquent I try with this, but it give me the used type_documents : I use Laravel …
Tag: sql
How to convert SQL to LINQ where it contains IN keyword in statement
I have got this SQL statement which I am trying to convert to LINQ. I have not been able to do the IN part. Tried following but I am unable to complete it: How do I write IN part of SQL in LINQ? Answer You can use Contains:
SQL SELECT with an extra column with more than 1 value
I have a table with column_1, column_2, column_3, and select it by: What I want is to add an extra column ‘hour’, it would have 24 possible values from 0 to 23. The outcome is to have every row [column_1, column_2, column_3] repeated 24 times with all possible 24 values of the extra column hour: H…
Iterate through SQL result set adding subquery results to result set
I have a table [Robots] containing Robot IDs, and a [TestResults] table which has a FK to [Robots] of [RobotId] What I am aiming to get is a result set of each robotID in the [Robots] table and for each ID a total of all entries in the [TestResults] of Status ‘Active’, ‘Failed’ and tot…
Deleting records from particular TABLE – Oracle SQL
I have 1 table i.e. TABLE_1 which has 2 columns i.e. ROLL_NO, ID I have another 2 tables i.e. TABLE_2 with 3 columns ROLL_NO, LINE_NO, CODE and TABLE_3 with 2 columns i.e. NAME, ORIG_CODE My question is I need to delete the records from TABLE_1 using other 2 tables TABLE_2 and TABLE_3 in such a way that if an…
Select items only if they all meet the specific condition
I have two tables on a Oracle SQL DB. Article (id [PK]) File (id [PK], article_id [FK], insertion_date [DATETIME]). I want to select files from articles that are more than 15 days old without adding new files. For example, if post ID 1 has a file added 10 days ago and another 20 days ago, they should not be r…
Check in T-SQL whether certain value sets exist in a related table
I have following tables: User – userId, userName, … Settings – settingId, userId, settingKey, settingValue for example for userId = 123, I might have: Then for example how can I query for all users that have settingValue of “0” corresponding to settingKey of “allowClient…
funny characters in Sql String
I have a query string that works fine if tableStr is for example MSFT. However, if tableStr is BRK-B, the query fails. How do I get around this? Answer Per MySQL Documentation, “The identifier quote character is the backtick (`)”. This means that if your table name has special characters in it, yo…
Adding a SQL column while also filling it in with a value from each row
I have a table with a column LastUpdated of type DateTime, and would like to add a column LastUpdated2 to this table. I would like to populate it with whatever LastUpdated is for each existing row in the table. Eg: LastUpdated 12:01 PM 5:00 PM Becomes: LastUpdated LastUpdated2 12:01 PM 12:01 PM 5:00 PM 5:00 P…
MySQL to fill in the blanks with NULLs when their isn’t a row based on set number of IDs from the join
Looking to fill in the blanks with NULLs when there isn’t a record that is for a row that I am expecting. Consider the following wp_posts (p) ID post_title 1 Week 1 2 Week 2 3 Week 3 4 Week 4 5 Week 5 wp_users (u) ID user_email 1 email1@example.com 2 email2@example.com 3 email3@example.com 4 email4@exam…