Introduction to SQL (part 1)

example

Store, (Id, Name)

Member table Member (Id, Name, StoreId)

Requirements:

List all stores and mark the stores to which the specified user Id belongs as Isfavorite = 1.

Train of thought

Take primary data first, filter again, or link table to find relationship.

A contingency table is key: it can be an existing table or a temporary table. (select * from XXX) tempTable you can use it as a table.

SQL procedures

1. List all stores

1. select store.Id,store.Name from store

1. Find the store to which the user Id belongs

1. select StoreId from member

where member.Id = “faf24f20-59fe-11e9-bd16”

1. Left jion (left jion, left jion, left Jion, left Jion, left Jion, left Jion, left Jion, left Jion)

1. The following is the favorateStoreId for all stores and the specified user in the list data.
2. 

1. IF, IS NULL. Tag favorateStoreId as favorateStoreId = 1

1. List all stores, associate userStore, and mark Isfavorite as 1 with IF

Select store.Id, IF(userstore. StoreId IS NULL,0,1) as Isfavorite, store.name

from store

left join

Select user’s store from user table

(select StoreId from member

where member.Id = “faf24f20-59fe-11e9-bd16-a7e8be91”) userStore

on

userStore.StoreId = store.Id

B. The results are as follows:

Please pay attention to our public account: