12294

select all rows from table 1 and mark rows that exist in table 2

Question:

I have a table of suppliers (id, name, address)

I have another table linking suppliers with customers (accredited_suppliers-> supplier_id, customer_id)

I want to select all suppliers with an identifier showing if there is a relationship with the customer (customer_id)

i.e. results would look like:

suppliers:

id, name, address, customer_link 1, name1, address1, null 2, name2, address2, Yes 3, name3, address3, null 4, name4, address4, Yes

Some more information:

Supplier Table: id name ... ...

Customer Table: id name ... ...

AccreditedSuppliers Tables: id customer_id supplier id

e.g. I want all suppliers, with a column flagging all suppliers linked to customer id=100

Answer1:

SELECT t1.*, IF(count(`t2`.`customer_id`) > 0, 'yes', NULL) as customer_link FROM `suppliers` AS `t1` LEFT JOIN `accredited_suppliers` AS `t2` ON `t2`.`supplier_id` = `t1`.`id`

Answer2:

You're either looking for a list of suppliers who have a customer

SELECT * FROM suppliers WHERE supplier_id IN ( SELECT supplier_id FROM customers );

Or you are looking to have a join, depending on the way the relation ship works you'll need to figure out what type of join you want, e.g is the relationship 1-1, 1-m, n-m?

Answer3:

SELECT id, name, address, customer_id as customer_link FROM suppliers LEFT JOIN accredited_suppliers ON suppliers.id=accredited_suppliers.supplier_id

Answer4:

Hope this should work -

select id, name, address, (case when exists(select 'x' from linkedtable where id in (supplier_id)) then 'Yes' else null end) as customer_link from suppliers

Recommend

  • JSON Path Extractor : JSON Path Expression
  • Calculate relative frequency for a certain group
  • Dynamic formation of where clause in LINQ
  • Using distinct in XMLAGG on duplicating records
  • Updating inserted record within MERGE statement in SQL Server 2008 R2
  • Categorical variables into multiple columns
  • Help needed in getting total resource count (SQL Server 2005)
  • database design for a payment system
  • plsql procedure to compare two tables where structure of table is not known
  • What am i doing wrong in retrieving value of button group?
  • google maps api get lat and lng and replace marker?
  • Controller scaffolding doesn't work in VS 2017 preview for ASP.NET Core 2 Preview
  • concat columns by joining multiple DataFrames
  • Elasticsearch - query primary and secondary attribute with different terms
  • .net file random access recoard locking
  • How to inject module and make it accesible to entrie angular app
  • Alert Dialog was disappearing when user clicks out side
  • How to access R.string.xxx resources from a method by passing string 'xxx' as parameter to
  • get the column name during runtime with entityframework
  • Conditional Validation in Ecto for OR - 1 of 2 fields is required
  • Map weak entity by using Code first
  • Using Variable for Property Name of Object - Javascript [duplicate]
  • Association Injection or Dependency Injection?
  • Avoid losing data type for the partitioned data when writing from Spark
  • python requests equivalent to curl -H
  • Angular 2 + Jasmine Unit Test - Getting Error TS2345
  • Merging two XPathDocuments using XmlCompiledTransform
  • SQL query to exclude records that appear in other rows?
  • Storing data from SQL in array
  • Slowly changing dimension - What is Pure type 6 implementation
  • How to save dynamically created textboxes and their values
  • How do I remove all but some records based on a threshold?
  • iOS: Detect app start via notification press
  • Lost migrations and Azure database is now out of sync
  • How would I use PHP exceptions to define a redirect?
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Web-crawler for facebook in python
  • bootstrap to use multiple ng-app
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent