FAQ Database Discussion Community


MySQL: Get all Items and associated tags with just one request

mysql,database,join
I have two MySQL tables. One contains Items (format: id|name|...) the other contains tags which belong to the items (format: id|item_id|name|...). There is one row for each 'use' of a tag. One item can have many tags. Is it possible (e.g. By smart use of JOIN) to get all Items...

SQL - How to only show the row with the greatest date value based on ID?

sql-server,join
I have SQL statements in SQL Server 2008 R2 based on a few joined tables that utilizes all the information I need in my program: SELECT Laptops.Laptop_ID, Laptops.Model_Name, ... Users.Firstname + Users.Lastname AS Name, Loans.Date_Loaned FROM Users INNER JOIN Loans ON Users.User_ID = Loans.User_ID RIGHT OUTER JOIN Laptops ON Loans.Laptop_ID...

Mysql query using subselects needs to use joins or exists

mysql,sql,performance,join,subquery
NOTE If your going to downvote my question that I clearly put a lot of time into, can you please at least tell me why so I can avoid any mistakes in the future? Thank you. Update At the suggestion of @DRapp I am adding an explanation as to what...

Trying to join multiple tables in MySQL database and display in Web Table

php,mysql,database,join
Ok, so I'm very new to this database stuff, and I'm trying to figure out how to query multiple tables at the same time. Apparently you can query as many different tables as you'd like, although you might experience poor performance if you do too many joins. However I'm having...

Join master table's data to a key/value table data in one SELECT

sql,sql-server,join,pivot,key-value
I have a table called Contacts that contains the columns Id FirstName LastName I have another table called ContactsExtra. It has the following columns: Id ContactId (FK refers to Contacts Id) PropertyId (FK refers to Properties Id) PropertyValue This is a Key/Value table that stores some extra contact properties. E.g....

I need to join 3 tables avoiding duplicates and aggregating data from 2 of the tables

sql-server,join,aggregate
I am working with some Email data and have 3 files: sent, opens (a subset of sent), and clicks (a subset of opens). Basically i want to join opens and clicks to the Send file by SubID (a unique identifier). Also, there have been 3 email deployments (JobID); I want...

Optimization - is union the best way to go in this case?

sql-server,join,optimization,union
I have a dataset which is updated on a daily basis from an external data source (Example). This data is then combined with some other in-house data (Scale) and is formed to a new table. Example contains some figures that can be errornous at times. I register for which funds...

How to create a SELECT query FROM “TABLE1 AND TABLE2”

sql,postgresql,select,join
I have a PostgreSQL database, with only SELECT permissions. In this DB there are two tables with the same structure (the same columns). I need to write several query in each table and join the results. There is a way for writing a query like this one? SELECT field1, field2,...

Best way to serialize a JOIN SQL query to JSON

php,mysql,json,codeigniter,join
I can't find a good title for my question. I'm doing a Restful API /JSON My problem is: I have got two tables: Product: id, name Prices: id_product, price Product 1--* Prices (One product have got multiples prices) So, If I want to export the next information in JSON products:...

delete the output of a join (record in multipe tables)

sql,postgresql,join,cloudfoundry,cascading-deletes
I use select * from service_instances i inner join service_instance_operations op on i.id = op.service_instance_id inner join service_bindings bind on i.id = bind.service_instance_id where i.guid = 'daf67426-129b-4010-832c-692bcfe98f62'; Howto delete this record in all 3 tables? There are primary and foreign key constraints. It doesn't work to replace "select *" with...

Nested SQL with Coalesced data part of an update statement

sql-server,join,sql-update,concat,coalesce
I have the following set up create table #work ( customer_no int, attended_conf varchar(100) -- ADDED LR 6/16/15 ) The #work table is filled - customer_no is filled, attend_conf is null until a later update statement. What I would like to do is update the attended_conf field with the concatenated...

Error loading association from controller in cakephp

join,cakephp-3.0
I can't get this simple query right. I need to join my adresses table to my annonces table. I supose this should be farly strait forward but I simply can't get it to work. I firstly made my adresse table object like this class AdressesTable extends Table { public function...

SQL Server - Substitute for OR when JOINing on non-indexed field

sql-server,join,query-performance
Background: One of our clients has a system where they have recordings of phone conversations between their agents and their customers, who they create various contracts with. The recordings are stored on a server with their locations saved in the Recordings table in the database. The agents can then "attach"...

Table update based on match/nomatch

sql,oracle,join,sql-update
I have two tables t1 and t2. t1 has this structure: yearmonth account company RCV_amount_t1 t2 has this structure: yearmonth account company billing amount billing amount CM_1 billing amount CM_2 RCV_amount_t2 I want to join t2 to t1 using yearmonth, account, and company. If they match, I want to update...

Full Outer Join (left join union right join) MySQL multiple tables

php,mysql,join,union
I have looked at other related SO questions, but they couldn't click for me. I have these tables: http://i.stack.imgur.com/cEYVn.png In the end, I’d like to combine these 5 tables so everything from Location (maybe loc_type (int) replaced with loc_type_name), everything but loc_id from Hours (since it will be joined on...

Search table based on linked text field from second table

mysql,sql,select,join
Not being an SQL wiz at all, browsing through the results here, I was unable to find a proper solution, although the problem itself is fairly simple. Two tables: products id title strings id language_en An entry in products may look like this: id = 1 title = 10 Corresponding...

MySQL 4 Table Join

mysql,table,join
I've been trying to get the proper data for this 4-table join for awhile and I can't seem to pin it down. I'm trying to grab a list of ids from songs based on who I am following in follows, and show the song id if my followers are the...

LEFT JOIN with condition is not returning data desired

php,mysql,join,left-join
I would like to do a left join using MYSQL. Currently I have a table something like this: CREATE TABLE `books` ( `bookId` int(11) NOT NULL, `bookTitle` varchar(100) NOT NULL, `bookAuthor` varchar(100) NOT NULL, `bookStatus` tinyint(1) NOT NULL, `bookDeleteFlag` tinyint(1) NOT NULL ); CREATE TABLE `lends` ( `lendId` int(11) NOT...

Join multiple values to virtual columns

mysql,sql,join
I have SQL table1 with value 'value1' in column Type. Then I have second table table2 in column Name 'value1'. In table2 in row with name 'value1' (unique data in this table), there are more column: color -> 'red', icon -> 'checkmark', type -> 'something'. How to join queries to...

Rotate columns to rows for joined tables

sql,sql-server,join,pivot
I have two tables similar to shown below (just leaving out fields for simplicity). Table lead : id | fname | lname | email --------------------------------------------- 1 | John | Doe | [email protected] 2 | Mike | Johnson | [email protected] Table leadcustom : id | leadid | name | value -------------------------------------------------...

Flag fields from table 1 that also bellowings to table 2

php,mysql,sql,select,join
I have two table (table1 and table2) where table2 is a subset of table1, what I am trying to do is a select statement that return a boolean if a field of tabl1 is also in the table2 For example: Table 1 has item1, item2, item3 and item4 Table 2...

SQL Update with a Group by Statement not working

sql-server,join,group-by,sum,sql-update
I've got two temp tables. In table @due_cte I have a series of Customer_Ids, followed by payment amounts collected throughout various points in the code. The @work table has a lot of various columns including hist_amt_due. What I want to do is update @work with the amt_due from @due_cte but...

Powershell Join Path returns array

arrays,powershell,join,path
I am trying to create folders at the start of my script and afterwords create files in those. To create paths i use md -Force $path, i also used just to test similar methods if(![System.IO.Directory]::Exists($path)){ [System.IO.Directory]::CreateDirectory($path) } After creating the path i like to build the filename + path of...

MySQL: Join distinct rows of two tables in a certain order?

mysql,join,distinct
I have a list of inventory units and sale transactions that I want to, (1) join by unit SKU, and (2) associate ONE transaction to ONE inventory unit in first-in-first-out order by date. I'm having trouble with the second part. The best I can come up with is: SELECT `units`.`unit_date`,...

SQL how to use multiple joins on the same table

mysql,sql,join
Hey guys I'm stuck with a tricky question that I'm unable to solve myself. This is what I'm trying to achieve. Is this even possible? I tried following query already, but I'm not getting what I want. SELECT t1.ID, info.start_bt1, info.start_bt2, t1.ANT as BT1_VL1, t2.ANT as BT1_VL2, t3.ANT as BT2_VL1,...

SQL Join Views - Duplicate Field

sql-server,tsql,join,view,alias
I am trying to join two views, I have tried to alias the cAuditNumber field under StkSalesUpdated view to AuditNumber1, but I still get the following message: Msg 4506, Level 16, State 1, Procedure KFF_Sales_Data_Updated, Line 3 Column names in each view or function must be unique. Column name 'cAuditNumber'...

Which performs first WHERE clause or JOIN clause

sql,sql-server,select,join,where
Which clause performs first in a SELECT statement? I have a doubt in select query on this basis. consider the below example SELECT * FROM #temp A INNER JOIN #temp B ON A.id = B.id INNER JOIN #temp C ON B.id = C.id WHERE A.Name = 'Acb' AND B.Name =...

Selecting from Different Tables, Sub queries or Joins

php,mysql,join,subquery
I have two tables; What I need to do is select comments of a given user. I need cid and heading as results posts pid | heading | body | username 1 smth.... smth.. u1 2 smth.... smth.. u2 posts cid | body | username 1 smth.. u1 2 smth.....

mysql - Select unique column based on max value of another column in a different table

mysql,sql,database,join
I'm trying to select only the highest kw (model table) for each category (type table). Model table +-----+----+---------+ | id | kw | type_id | +-----+----+---------+ | 1 | 2 | 1 | | 2 | 5 | 1 | | 3 | 10 | 2 | | 4 |...

Database Multiple select Query , Unable to Retrieve Desired Data

sql,sql-server,database,select,join
I have three Tables as given below , I want to search record on base of three Different cafeterias in Single query,I want to search record whether user give Brand_Name , Drug_Id, Generic Name It give same output I.E. all record of That Drug_ID ,I am new to Database, however...

Joining two Pandas DataFrames does not work anymore?

join,pandas,merge,dataframes
I have 2 Pandas Dataframes. The first one looks like this: date rank id points 2010-01-04 1 100001 10550 2010-01-04 2 100002 9205 The second one like this: id name 100001 A 100002 B I want to join both dataframes via the id column. So the result should look like:...

update rows after left join

mysql,join
I'm looking for a way to update some values in a table if they were used in a left join. I have two tables: table1: id | name | age | job 1 | john | 31 | 2 | eric | 25 | table2: id | job | inserted...

Is there a difference between using for-each and Contains in joining on a qeury

c#,linq,entity-framework,join
Let's say I am doing a JOIN like this below: var query1 = (from ga in this.Context.GroupAddress join gpa in this.Context.GroupsProvidersAddresses on ga.GroupAddress_K equals gpa.GroupAddress_K where this.TerminatedGroupAddressesKeys.Contains(ga.GroupAddress_K) select gpa.ProviderAddress_K).ToList(); where TerminatedGroupAddressesKeys is a collection of strings that I have and I am using .Contains Is that different from doing a...

MYSQL INNER JOIN unexpected result

mysql,join
I'm having a problem on my MySQL Inner join. My INNER JOIN doesn't give me my desired result. I have Table1 that contains the TrackNo only then Table2 contains the details of Table1 per trackNo. >> Table Structure: >> SQL Code: SELECT tr.trackNo AS 'TrackNo', trD.Status, MAX(trD.DatePosted) AS `Date/Time` FROM...

How to join with multiple tables of this complicated case?

sql,join
I have 4 tables with the following relationship. Table_3 is the central table of the star schema. How to write a sql to join these 4 tables ? The difficult thing is how to join table_1 with table_2 ? Do I have to use sub-query (nested join ) ? table_1...

2 join conditions codeigniter giving error

codeigniter,join,condition
I have 2 tables - for orders and for documents for these orders. I want to show all orders from first table and if there are documents for these orders, to show date of documents(I take date from table documents). I'm trying to make 2 join conditions because in table...

Shaping dataset in MySQL with a multiple inner join

mysql,sql-server,join,distinct
I am learning MySQL and working with some dummy tables that I set up from an example online. The code to create the tables wasn't given, just the end result of what they should look like, so I did my best to re-create them. I know that their design isn't...

Postgres SQL: how to delete rows of Table1 where category = x (but Category is defined in Table 2)?

database,postgresql,join,delete
I have a Postgres Database. I am trying to delete rows in Table 1, based on a condition expressed in Table 2. Table 1: id, object_id, time, action_type Table 2: object_id, object_name, object_category I would like to delete all rows in Table 1, where object_category = x. Thanks!...

How can I select same column value as different column from mysql table with join?

mysql,join
I have a table in mysql datbase name as communication and its columns and values are: and contact table and its columns and values are: From above tables I have a requirement to select values like following: contact_id f_name l_name phone_number email_address creation_date Actually I am not a database programmer....

Unique movies by UNION the results of two join, MYSQL

mysql,join,union
I ran the following query to UNION the result of two joins over the below 4 tables: SELECT member_CROWD.worker_Id, member_CROWD.Unreliable, Answer_CROWD.qId, Answer_CROWD.answer FROM member_CROWD JOIN `Answer_CROWD` ON member_CROWD.worker_Id = Answer_CROWD.worker_Id WHERE member_CROWD.Unreliable = '0' AND Answer_CROWD.qId = 'q8' GROUP BY Answer_CROWD.answer UNION SELECT member.mem_id, member.Unreliable, Answer.qId, Answer.answer FROM member JOIN...

ms access query very slow

sql,ms-access,join
I have this ms access query: SELECT t1.sb, suchbegriff2, menge FROM (SELECT artnr & '/' & [lfdnr-kal] AS sb, left(suchbegriff,7) & val(right(suchbegriff,4)) AS suchbegriff2 FROM kvks WHERE suchbegriff like '*/*') AS t1 INNER JOIN (SELECT artnr & '/' & [lfdnr-kal] AS sb, [artnr-hz] & '/' & val(lfdnr) AS hz, menge...

Mule Mongo Connector to produce INNER JOIN results

mongodb,join,mule
I have two collections, one of organizations and one of brands within the organization, and I am trying to produce a result set that will have the brands related to the organization show up as as a nested JSON inside the organization JSON result. The code for getting the results...

Sql: Selecting from other tables

mysql,sql,database,join,foreign-keys
I have 2 tables. Player and Stats. Player has the fields: Name, Age, DOB and SSN. Stats has the fields: Tackles, Goals, Assists, and SSN. SSN is a foreign key. How can I write a query to find the stats of players with DOB >= '1994'. DOB is not a...

Unable to get correct data over several joins

mysql,database,join,relational-database,jointable
I have the following tables and sample data: Table: activities +------------------+---------------+ | activity_id (PK) | activity_name | +------------------+---------------+ | 18 | Bicycling | | 19 | Running | +------------------+---------------+ Table: activity_attributes +------------------+-------------------+ | activity_id (FK) | attribute_id (FK) | +------------------+-------------------+ | 18 | 55 | | 18 | 56 |...

JOIN on keys that don't have the same value

mysql,sql,join
I am trying to do an INNER JOIN on two tables that have similar values, but not quite the same. One table has a fully qualified host name for its primary key, and the other the hosts short name, as well as the subdomain. It it safe to assume that...

Activerecord Rails 4 perform something like a join that still returns rows that don't have the association

actionscript-3,ruby-on-rails-4,join,left-join
If after reading my question you have a suggestion for a better title, please add a comment. I was having trouble succinctly saying what I wanted. I have a situation like this. class Artist < ActiveRecord::Base has_many :album_artists has_many :albums, :through => :album_artists end class Album < ActiveRecord::Base has_many :album_artists...

MySQL query JOIN with 2 tables and multiple entries

php,mysql,sql,join
I'm trying to do a MySQL JOIN query to grab the content from two tables. This is what Table A looks like: Table A ID | ISBN | Type ---------------------- 12 | 0338566 | book 15 | 6656565 | post 16 | 9435644 | book 20 | 8525446 | book...

Making an efficient query with 7 joins, JPA or SQL? What type of collection?

java,mysql,jpa,join
folks! Had to performance tune an expensive JPA query with 7 joins, taking 42 seconds to complete locally and ~3 minutes in the remote server (AWS). SELECT res FROM Resultado res JOIN res.panoramica pan JOIN pan.gondolaId gon JOIN gon.visitaId vis JOIN vis.estabelecimentoId estab JOIN estab.cliente cli JOIN res.configuracaoAnalise ca JOIN...

MySQL - Querying for unread messages along with mail messages

php,mysql,join
I am in need to retrieve the messages in one query with name of users and in addition count of unread messages. The query being used is the one below and the name of user is retrieved and what is remaining to get count of unread messages. How can I...

convert query to use of joins

mysql,join,database-normalization
How can I transform the following query to a query with joins? I tried the second query, but that gave me ?doubled? and not plausible results. Thank you in advance! works (but awkward and not using joins): SELECT score_cards.id, score_cards.start_time, score_cards.end_time, users.name AS user_name, holes.sort, hole_scores.score FROM score_card_user AS score_card_user,...

join two data tables and use only one column from second dt in R

r,join,merge,data.table
Edited to make it cleaner. Let's say I have two data tables (dt1 and dt2), and I want to get dt3 using data tables. A,B,C,E,F,G,H are column names. dt1 key is column A, and dt2 key is column E. Data tables have different number of rows. I want to keep...

MS Access multi (INNER, LEFT & RIGHT) JOIN query

sql,ms-access,join,ms-access-2010,outer-join
Ok, here's the thing. I have the Following tables involved : > YEARS ------------------------------ ID YEAR ACTUAL ------------------------------ 1 2014-15 TRUE 2 2015-16 FALSE > SHOPS ------------------------------ ID NAME ... ------------------------------ 1 ThisShop ... > ITA ------------------------------ ID YEAR_ID SHOP_ID ------------------------------ 1 1 1 2 1 2 ... > INSPECTORS...

Python : The most efficient way to join two very big (20+ GB) datasets?

python,join,dictionary,lookup
I have a 21 GB dataset df_ns: domain|ns 123.com|ns1.domanihost.com hymi.net|ns2.hostinger.com and another 12 GB dataset df_ip: ip|domain 28.76.2.2|myname.com 86.90.234.5| 123.com and I would like to join them on domain name and for the domains that are in both files extract ip and ns. The way I thought of using it...

Update a table using info from a second table and a condition from a third table in Postgresql

database,postgresql,join,sql-update
My current code is as follows UPDATE product_template SET listprice = product_uom.factor * product_template.list_price FROM product_uom INNER JOIN product_template ON product_uom.id = product_template.uom_id INNER JOIN product_product ON product_product.id = product_template.id WHERE product_product.manufacturer = 2646 As I understand line 1 specifies which table I want to update. then I specify that...

Python: too many joins()?

python,join,filepath
I'm not sure why I'm getting this error, I'm using str.join() and os.path.join() at different points in the script, is that the cause? Using os.path.join: from os.path import getsize, dirname, join class Wav: src_path = "No path" dest_path = destination old_name = "name.wav" new_name = "" def __init__(self, path): self.src_path...

Which JOIN type in multiple joins

mysql,sql,join,left-join,right-join
I have 4 tables that I want to be joined. Customers Traffic Average Live I want to insert joined data of these tables to "Details" table. The relationship between the tables is here: each of Traffic, Average and Live tables have a "cid" that is the primary key of "Customers"...

joining two tables by value of one column and calculating

sql,sql-server,join,sql-server-2014
Thank you for interested in question. I have in one table some thing like this sifrez | sifKorisnikPK | Status 1 | 1 | 'P' 2 | 1 | 'P' 3 | 1 | 'U' 4 | 2 | 'P' 5 | 2 | 'P' 6 | 2 | 'U'...

Mysql how to reduce rows by max datetime and group by non-uniqe id

mysql,datetime,join,group-by
I am having a severe case of brain flatulence. Keeping it simple, I have 3 tables: Orders, Statuses, XrefOrdersStatuses I've setup an SQLFiddle (SQLFiddle Here) with simplified schema and some redacted row data. What I need to get are Orders with their most recent XrefOrdersStatuses (joined with Statuses). I can...

Propel ORM left join select

mysql,join,orm,propel
I have 3 table major table: +----+------------+ | id | major | +----+------------+ | 1 | Computer | | 2 | Architect | | 3 | Designer | +----+------------+ classroom table: +----+----------+-------+ | id | major_id | name | +----+----------+-------+ | 1 | 1 | A | | 2 |...

Adding entries from multiple MySQL tables using one single SQL join statement, but only if there are entries available in the second table

mysql,join
Using one single SQL query with a join: How can I add entries from a second table only if there is a corresponding entry available? project source description | source source_id | value ---------------------------- -------------------------------- Project 1 | 1 1 | Additional Info 1 Project 2 | null When I...

Complex SQL with Multiple Joins

mysql,database,join
I have a database of politicians, political parties and candidacies for a post in the election. The database consists of the following tables: People, Groups, Candidacies and Posts. The Candidacies table is a join relationship between People and Groups. Each record in the Candidacies table represents a candidacy in the...

join two different list by id into one list

c#,list,join,merge,automapper
I've got two different list of two different objects. Then i got one list of a viewmodel that contains properties from both the objects and i want them to be joined into that list. //Product public string id { get; set; } public string unitMeasurement { get; set; } public...

Php Mysql: Retrieving 2 usernames via 2 user id

php,mysql,table,join
Hi all i have one problem that i cant solve. I am doing pair generator for tournament. I have two tables. The first table contains the usernames and userids. The second table contains match_id, round, user1_id, user2_id Table 1 user_id username 21 John 22 Peter 23 Ana 24 Dan Table...

MySQLi LEFT JOIN query is throwing a syntax error

php,mysql,join,mysqli,left-join
I have two tables 'lr_users' and 'lr_ranks' with the following table structures: lr_users: user_id(pk), username, rank(fk), job_id(fk), date_joined lr_ranks: rank_id(pk), name $qry= mysqli_query($con, "SELECT lr_users.username, lr_users.rank, lr_users.job_id, lr_users.date_joined lr_ranks.name AS rankname FROM lr_users LEFT JOIN lr_ranks ON lr_users.rank = lr_ranks.rank_id")or die(mysqli_error($con)); $rows = mysqli_num_rows($qry); Above is my PHP code for...

INSERT statement with INNER JOIN SELECT SUM

mysql,sql,sql-server,join
I try to make a query which can INSERT data quickly, before I execute query with INSERT statement I build a query which contains SELECT SUM CASE and INNER JOIN. And my code contains error. Here's my code : SELECT pkg_guru.id_pkg , pkg_guru.id_guru , guru.nama_guru , guru.mapel_diampu , pkg_guru.id_penilai ,...

CodeIgniter query to exclude a subset from results

php,mysql,codeigniter,join,codeigniter-3
I'm having some trouble figuring out how to write the proper query after doing a JOIN. I need to get all users in Group 1 while excluding a subset of these results. Table users: id name 1 John Smith 2 Joe Blow 3 Mary Jane Table users_groups: user_id group_id 1...

PostgreSQL get latest rows/events for all users

sql,join,greatest-n-per-group,amazon-redshift
Working on PostgreSQL 8.x (AWS Redshift) I have a db structure like this: userId: varchar, tstamp: datetime, event: string So let's say I have the following rows u2, t1, e1 u1, t2, e2 u2, t3, e1 u1, t4, e2 Where u1 and u2 are userids, t[1..4] are timestamps where t1>t2>t3>t4...

Join Statement omitting entries

unix,join,hidden-characters
Using: Unix 2.6.18-194.el5 I am having an issue where this join statement is omitting values/indexes from the match. I found out the values are between 11-90 (out of about 3.5 Million entries) and I have tried to look for foreign characters but I may be overlooking something (Tried cat -v...

How to use field value of joined table for join rather than foreign key

sql-server,tsql,join
I have a new database schema that I need to query and I am having trouble getting the data I need without using a primary key in the JOIN. I didn't design the schema but I have to work with it. I tried creating a SQLFiddle for this but it...

Join table comment in rails 4 migration

ruby-on-rails,ruby-on-rails-4,join,migration,has-and-belongs-to-many
I'm pretty new on rails 4 and I'm not really sure about how should be my join_table. I've done the migration rails g migration CreateJoinTableQuestionSubTopic question sub_topic And I get this file class CreateJoinTableQuestionSubTopic < ActiveRecord::Migration def change create_join_table :questions, :sub_topics do |t| # t.index [:question_id, :sub_topic_id] # t.index [:sub_topic_id,...

Good practice for handling naturally JOINed results across an application

php,mysql,database,join,database-design
I'm working on an existing application that uses some JOIN statements to create "immutable" objects (i.e. the results are always JOINed to create a processable object - results from only one table will be meaningless). For example: SELECT r.*,u.user_username,u.user_pic FROM articles r INNER JOIN users u ON u.user_id=r.article_author WHERE ......

Update a field from one table to another, involving a 3 table join

sql,database,join,firebird
I have a table I need to update the price field in. I need to update this field from a different price field from a different table. The only way I can get to the required table for the update is by joining another table into this query. So in...

Query from 3 tables

sql,join
I have 3 tables: TblUsers, tblBridge, and tblAssignments. The bridge contains the ID of the user and the corresponding AssignmentID for that user. I'm trying to find the user with a certain assignment @Assignment, but also where tblUser.isAdmin = Yes. I feel like its possible with Joins, I just can't...

Finding unmatched rows of 2 tables in SQL

sql,sql-server,join
I have 2 tables with 3 columns that are suppose to have the same information. I would like have a query that selects only the rows that don't have a complete row match. Below is an example of the 2 tables I would like to match: Table 1 ID FPRICE...

How to count users in JOINed models?

python,django,join,django-models,django-queryset
Let's say, we have two models: class User(models.Model): nickname = models.CharField(max_length=50) email = models.CharField(max_length=50) class Profile(models.Model): user = models.OneToOneField(User, primary_key=True) carma = models.BooleanField(default=False) birthdate = models.DateField(input_formats=['%Y-%m-%d']) How to get amount of users where: 1) all users with ages below 18? 2) all users between 18 - 25 3) all users...

mysql query retrieve too slow from 10,000 of data (Query Optimizing)

php,join,mysqli,subquery,left-join
10,000 of data in memberships, members and payments table. Retreiving the query is too slow, while searching a particular payment status in each members latest payment. SELECT m.id AS member_id, m.full_name, m.unit, m.street, m.block, m.country, m.postal_code, cat . * , cat.id AS cat_id, mem.membership_num, mem.id AS membership_id FROM memberships mem...

Inefficient execution plan on MySQL query with multiple joins

mysql,sql,join
I have a performance issue with MySQL ; it seems execution plan for my request is far from optimal, but I don't know why MySQL chooses it nor how I could change it. I reproduced the issue within a minimal environment, and here is the query: SELECT member.id, member_cache.id, section.id,...

MySQL: Remove JOIN for Matched Row if 2nd Round of Criteria Not Met

mysql,join,left-join,match
CONDENSED VERSION I'm trying to join a new list with my existing database with no unique identifier -- but I'm trying to figure out a way to do it in one query that's more specific than matching by first name/last name but less specific than by all the fields available...

Joining tables on two fields

mysql,join
I always have confusion when it comes into JOINING tables. So, I have a table that stores the user details called tblUsers having the following fields(for the sake of simplicity, I am including only the required fields here while posting): user_id first_name And I have another table which stores the...

MYSQL: doing a select to filter using the ID of 2 tables

mysql,wordpress,join
I really dont know how to ask this so i will resume and go to the point. I have 2 tables. (Im using Wordpress so you may know the tables) Table 1 = wp_users (contains id column and display_name nickname) Table 2 = wp_simple_login_log (contains uid column wich is the...

3 Table JOIN with group by doesn't work

mysql,sql,join,group-by
I have 3 tables: CREATE TABLE data ( id VARCHAR(30) NOT NULL PRIMARY KEY, cat VARCHAR(30), name VARCHAR(30) ) ENGINE=InnoDB; CREATE TABLE sales_data ( id VARCHAR(30) NOT NULL, sold date, sale_s SMALLINT(5) ) ENGINE=InnoDB; CREATE TABLE expenses ( id VARCHAR(30) NOT NULL, date_exp date, exp DECIMAL(5,3) ) ENGINE=InnoDB; and need...

Does Maria DB support ANSI-89 join syntax

sql,database,join,syntax,mariadb
Does Maria DB support ansi-89 join syntax? SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title FROM sites, pages WHERE sites.site_id = pages.site_id; or only ansi-92 syntax? SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title FROM sites INNER JOIN pages ON sites.site_id = pages.site_id; ...

t-SQL what is the maximum number of tables I am able to join? [duplicate]

sql-server,join
This question already has an answer here: What is the maximum number of joins allowed in SQL Server 2008? 3 answers What is the maximum number of tables I am able to join? Or is it unlimited? Are there any shortcuts to add multiple tables, without having to alias...

Rails HABTM: Select everything a that a record 'has'

ruby-on-rails,postgresql,join,has-and-belongs-to-many
I have three tables: projects, technologies, and projects_technologies (a map from project IDs to/from technology IDs). I have two models: projects and technologies. Projects have and belong to many (HABTM) technologies and vice versa. Now I'm trying to get all the names (or active records, if there's a way to...

irc server response to JOIN comment

python,join,network-programming,irc,hexchat
I'm new with writing an IRC sever. I get Log from joining the channel in freenode connection . The server response to : /JOIN #h was :[email protected] JOIN #h * :realname and test was my nickname. Does anyone know that , what is exactly 151.232.114.48 ?...

left join table, find both null and match value

sql,sql-server,join
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...

Understanding cartesian product in SQL

mysql,sql,select,join,cartesian-product
I am not able to understand how Cartesian product works. Consider the simple schema: mysql> select * from account; +----------------+-------------+---------+ | account_number | branch_name | balance | +----------------+-------------+---------+ | A101 | Downtown | 500 | | A102 | Perryridge | 400 | | A201 | Brighton | 900 | |...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables

sql-server,join
I have two tables, one with various details such as Username, email id, user code and another table with columns which are: UserCode, supervisor email id, active_flag I required an output of a table which only displays the active users i.e. the users with active_flag (Y or N) column as...

Can we perform joins on tables in two different projects in BigQuery?

database,join,google-bigquery
I have two projects having datasets. I want to perform join of one table from first project to table in second project. How can I do that? Query ?

PL/SQL: Join between two tables error [on hold]

sql,table,join,plsql
I have got a task in which I have to display information from two different tables using a cursor. I tried doing it with an example we did before, but it doesn't seem to work! Below mentioned are the two tables that I have to take records from and display...

Learning to use Advanced features of SQL

mysql,join
I'm at the point where i need to learn to use more advanced features of SQL if i would like to advance my career. I confess i don't know what a join is or how to use them. i start reading queries with joins and my brain just turns to...

PHP Select Count and Left Join where

php,mysql,join,left-join
Im not sure if it´s possible to combine SELECT COUNT + Left Join on this way but it would be very helpful for me. This is my code whitout Left Join attempt: $pp = mysql_result(mysql_query("SELECT COUNT(*) FROM app_tgp WHERE idt='$zaznam[id]' AND ( partic='' OR partic='y' OR (partic=='n' AND $zaznam[logoff]=='n'))"), 0);...

INNER JOIN clause ignoring NULL values

sql-server,sql-server-2008,join
I am looking to query some data that pertains to medications a patient has been prescribed that are in a certain category. But I also want to show patients that do not have any medications. My query so far: SELECT pd.fname, pd.lname, pp.drug_name, pp.drug_strength FROM patient_data pd FULL OUTER JOIN...

Query to join two tables by mapping third table without returning all records from third table in Oracle

sql,oracle,join,left-join
I have 3 tables: Table Maths: ID Marks1 (1 , 80) Table English: ID Marks2 (2 , 85) Table Student: ID Name (1 , Raj) (2 , Sam) (3 , John) I need a SQL query (Oracle) to get the following output: ID Name Marks1 Marks2 (1 , Raj ,...

chunk of data into fixed lengths chunks and then add a space and again add them all as a string

regex,list,join,ironpython,findall
I have got hex values as a85b080040010000. I want it to be as a8 5b 08 00 40 01 00 00. I have done it by using below code. But I have to work with very large data. So I want computed time to be very low. import binascii import...

Is there a better way to write this query involving a self select?

sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

Mysql count and return just one row of data

mysql,join,count,subquery,having
I need to count the amount of users that have have answered all of those 3 profile_options (so they have at least 3 records in the profile_answers table). SELECT COUNT(DISTINCT(users.id)) users_count FROM users INNER JOIN profile_answers ON profile_answers.user_id = users.id WHERE profile_answers.profile_option_id IN (37,86,102) GROUP BY users.id HAVING COUNT(DISTINCT(profile_answers.id))>=3 The...

MYSQL Query: JOIN Using Value from tbl 1 and concate to tbl 2

mysql,join
i'm trying to do something like this: tbl1: MatNr| text ------------ 100 | lala tbl2: MART | lolo -------------- 100.1 | miau I need something like this: Result tbl1: MatNr | MART | text | lolo ---------------------------------- 100 | 100.1 | lala | miau I Try to use a simple...

SQLITE inner join windows phone 8

c#,.net,sqlite,join,windows-phone-8
I have two tables... ITEM and STOREITEM ITEM columns --ITMID_PK,ITMNAME, description ,iCON STOREITEM columns --ITMID_FK,PRICE to retrieve data of ITEM table to list and store in listbox. I follow this List<ITEM> retrievedlist = sqlconn.query<ITEM>("select * from ITEM").ToList<ITEM>(); foreach (var t in retrievedlist ) { listbox.Items.Add(t); } this works perfectly for...

Mysql joining two tables where part of string in table1 are in table2

mysql,sql,join
I am currently developing an employee database website. Previously my client use ms. excel as database storage which has no structure. But they already have thousands of data. I have already imported data into temporary tables. The problem is, I had to adjust the excel data with database structure. This...