Mgiyuk

MySQL JOIN whether null or not I'm pretty out of practice with MySQL and PHP, but I have a project I'm working on for a friend that involves selecting data from two tables, and combining them into one result - seems simple. Table 1 has 13 fields, but the important ones are id (auto-increment, primary key) and serial (unique). The rest are just ones like customer , description , etc. etc. Pictures has 3 fields, picID (auto-increment, primary key), imagePath and serial I need to retrieve all data from Table 1, and if there is a matching photo (identified by the same serial - only ever 1 photo possible per serial) in Pictures, then retrieve that data too. I then output the data from Table1, and use imagePath from Pictures to build an image in HTML if one has been uploaded. The query I've been using is: $sql = "SELECT * FROM Table1 LEFT JOIN Pictures ON Table1.serial = Pictures.serial ORDER BY Table1.serial"; Which seems perfect, EXCEPT if any row from Table1...

How to join list of dataframes to one dataframe, using the DF / list indices? library(dplyr); library(tibble) Here is my sample data. A list of small dataframes ( listOfDFs ) I want to join to a single dataframe, ( points ). listOfDFs points listOfDfs has 5 small dataframes with 7 rows total, and points is one dataframe with 7 rows: listOfDfs points points <- structure(list(EVENT_ID_CNTY = c("LBY1243", "LBY3389", "LBY3393", "LBY3506", "LBY3822"), year = c(2013, 2015, 2015, 2015, 2015), COUNTRY = c("Libya", "Libya", "Libya", "Libya", "Libya")), .Names = c("EVENT_ID_CNTY", "year", "COUNTRY"), row.names = c(NA, -5L), class = c("tbl_df", "tbl", "data.frame")) listOfDFs <- structure(list(`1` = structure(list(CELL_ID = c(165267, 164547 ), gwno = c(...

Finding Duplicate and Missing (null) Values in Oracle Table I am looking for errors in a table and want to report both duplicates and missing values. I am unsure of the best way to do this and am looking for advice on a better way to accomplish this. This is in Oracle 12c. This appears to achieve the desired result: SELECT a.id, a.mainfield, a.location, b.counter FROM maintable a INNER JOIN ( SELECT mainfield, Count(*) counter FROM maintable GROUP BY mainfield HAVING Count(mainfield) > 1 OR mainfield IS NULL ) b ON a.mainfield = b.mainfield OR ( a.mainfield IS NULL AND b.mainfield IS NULL ) ORDER BY a.mainfield; This works and gives me the ID, the potentially null MAINFIELD, the location and a count of either the duplicate MAINFIELD values or the null MAINFIELD values. Is there something simpler or potenti...

finding highest total price and converting So I have to use a query where I list the trade id stock id and the total price converted to us dollars where it is the highest price total. SELECT tr.trade_id, tr.stock_id, round(tr.price_total * con.exchange_rate,2) as "US Dollars" from trade tr JOIN stock_exchange se on se.STOCK_EX_ID = tr.STOCK_EX_ID JOIN currency curr on curr.CURRENCY_ID = se.currency_id JOIN conversion con on con.from_CURRENCY_ID = curr.CURRENCY_ID WHERE (tr.PRICE_TOTAL) = (Select Max(price_total) from trade) and curr.name = 'Dollar' and tr.stock_ex_id is not NULL group by tr.trade_id, tr.stock_id, round(tr.price_total), tr.price_total, round(tr.price_total * con.exchange_rate,2); Trade (trade_id PK, stock_id FK2, transaction_time, shares, stock_ex_id FK1,price_total) Stock-exchange( stock_ex_id PK, name, symbol, currency_id FK1) conversion( from_currency_id PK, to_currency_id) currency ( currency_id PK, name, symbol expected output should be - trade_...

Multiple Left Joins in BigQuery I'm trying to make a currently working SQL query that I have in BigQuery more streamlines and am running into the following issue: Error: ON clause must be AND of = comparisons of one field name from each table, with all field names prefixed with table name. Consider using Standard SQL .google.com/bigquery/docs/reference/standard-sql/), which allows non-equality JOINs and comparisons involving expressions and residual predicates. Below is the query that is giving the error above. The first LEFT JOIN works. When I added the second one, right below, I started getting the error. What I'm trying to do is get the human readable own.o.firstname and own.o.lastname values rather than the owner_id value of the deal record (o.properties.hubspot_owner_id.value), but in order to do so I need to join some tables. I had to use CAST on the ON clause of the second JOIN because the fields are of different types in each table's respective schema. If I don'...