[필기노트] SQL, 5개 Table 조회

-- 유저 정보 조회 (동일한 일정이 있을 경우)
2명의 userId가 존재한다.

1. 모든 유저를 출력한다.

SELECT * FROM Users
;


2. 모든 구인 공고를 출력한다.

SELECT * FROM Posts
;


3. 모든 채널을 출력한다.

SELECT * FROM Channels
;


4. 해당하는 Channels에 모든 user를 출력한다.

SELECT userId FROM Channels
WHERE postId = 1;


5. 입력받은 userId가 있는 경우의 Channel만 출력해야한다.

SELECT postId FROM Channels WHERE userId = 2;


6. 입력받은 userId가 있는 일정에 참여중인 userId를 출력한다.

SELECT DISTINCT userId
FROM Channels
WHERE postId IN (SELECT postId FROM Channels WHERE userId = 2)
    AND userId != 2
;


7. 입력받은 userId가 상대방 usreId와 같이 수행하는 일정의 갯수를 출력한다. scheduleCount 


SELECT DISTINCT COUNT(*) FROM Channels WHERE userId = 2 and postId IN (
    SELECT DISTINCT postId FROM Channels WHERE userId = 3 ) 
 - Sub Query WHERE IN으로 해결한 방법


SELECT COUNT(*)  
FROM (SELECT DISTINCT postId 
    FROM Channels 
    WHERE userId = 2) AS a
JOIN (SELECT DISTINCT postId 
    FROM Channels 
    WHERE userId = 1 ) AS b
ON a.postId = b.postId
;
 - Sub Query JOIN으로 해결한 방법
 - 이것을 기준으로 작성하도록 하자.


8. 코드 병합
SELECT nickname, rating, profileImg, statusMessage, 
(SELECT GROUP_CONCAT(likeItem ORDER BY likeItem ASC SEPARATOR ', ')
    FROM Likes 
    WHERE userId = u.userId
    GROUP BY userId) AS likeItem,
(SELECT COUNT(*)  
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 3 ) AS b
    ON a.postId = b.postId) AS scheduleCount
FROM Users AS u
WHERE userId = 2
;

9. 입력받은 userId와 대상 userId를 비교할 때 가장 최근 생성된 일정의 타이틀을 출력한다.


(SELECT title FROM Posts WHERE postId = (SELECT a.postId
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 3 ) AS b
    ON a.postId = b.postId ORDER BY a.postId DESC LIMIT 1)) AS scheduleTitle


10. 코드 병합
SELECT nickname, rating, profileImg, statusMessage, 
(SELECT GROUP_CONCAT(likeItem ORDER BY likeItem ASC SEPARATOR ', ')
    FROM Likes 
    WHERE userId = u.userId
    GROUP BY userId) AS likeItem,
(SELECT COUNT(*)  
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 3 ) AS b
    ON a.postId = b.postId) AS scheduleCount,
(SELECT title FROM Posts WHERE postId = (SELECT a.postId
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 3) AS b
    ON a.postId = b.postId ORDER BY a.postId DESC LIMIT 1)) AS scheduleTitle
FROM Users AS u
WHERE userId = 2


11. 친구 여부를 확인하는 코드 작성

SELECT COALESCE(MIN('Y'), 'N') AS isFriend
FROM Friends
WHERE EXISTS (select a.1
    FROM (SELECT 1
        FROM  Friends
        WHERE giveUserId = 1 AND receiveUserId = 3) AS a
    JOIN (SELECT 1
        FROM  Friends
        WHERE giveUserId = 3 AND receiveUserId = 1) AS b)
;

12. 코드병합

SELECT nickname, rating, profileImg, statusMessage, 
(SELECT GROUP_CONCAT(likeItem ORDER BY likeItem ASC SEPARATOR ', ')
    FROM Likes 
    WHERE userId = u.userId
    GROUP BY userId) AS likeItem,
(SELECT COUNT(*)  
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 3 ) AS b
    ON a.postId = b.postId) AS scheduleCount,
(SELECT title FROM Posts WHERE postId = (SELECT a.postId
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 3) AS b
    ON a.postId = b.postId ORDER BY a.postId DESC LIMIT 1)) AS scheduleTitle,
(SELECT COALESCE(MIN('Y'), 'N')
    FROM Friends
    WHERE EXISTS (select a.1
        FROM (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 2 AND receiveUserId = 3) AS a
        JOIN (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 3 AND receiveUserId = 2) AS b)) AS isFriend
FROM Users AS u
WHERE userId = 3 AND userId IN (SELECT DISTINCT userId
FROM Channels
WHERE postId IN (SELECT postId FROM Channels WHERE userId = 2)
    AND userId != 2)

;

13. 동일한 Schedule에 소속되어 있을 경우 출력한다.

SELECT DISTINCT userId
FROM Channels
WHERE postId IN (SELECT postId FROM Channels WHERE userId = 2)
    AND userId != 2


14. 코드 병합

SELECT nickname, rating, profileImg, statusMessage, 
(SELECT GROUP_CONCAT(likeItem ORDER BY likeItem ASC SEPARATOR ', ')
    FROM Likes 
    WHERE userId = u.userId
    GROUP BY userId) AS likeItem,
(SELECT COUNT(*)  
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 3 ) AS b
    ON a.postId = b.postId) AS scheduleCount,
(SELECT title FROM Posts WHERE postId = (SELECT a.postId
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 3) AS b
    ON a.postId = b.postId ORDER BY a.postId DESC LIMIT 1)) AS scheduleTitle,
(SELECT COALESCE(MIN('Y'), 'N')
    FROM Friends
    WHERE EXISTS (select a.1
        FROM (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 2 AND receiveUserId = 3) AS a
        JOIN (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 3 AND receiveUserId = 2) AS b)) AS isFriend
FROM Users AS u
WHERE userId = 3 AND userId IN (SELECT DISTINCT userId
FROM Channels
WHERE postId IN (SELECT postId FROM Channels WHERE userId = 2)
    AND userId != 2)
;
 - 2번의 기준으로 3번을 검색한다.




SELECT nickname, rating, profileImg, statusMessage, 
(SELECT GROUP_CONCAT(likeItem ORDER BY likeItem ASC SEPARATOR ', ')
    FROM Likes 
    WHERE userId = u.userId
    GROUP BY userId) AS likeItem,
(SELECT COUNT(*)  
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 4) AS b
    ON a.postId = b.postId) AS scheduleCount,
(SELECT title FROM Posts WHERE postId = (SELECT a.postId
    FROM (SELECT DISTINCT postId FROM Channels WHERE userId = 2) AS a
    JOIN (SELECT DISTINCT postId FROM Channels WHERE userId = 4) AS b
    ON a.postId = b.postId ORDER BY a.postId DESC LIMIT 1)) AS scheduleTitle,
(SELECT COALESCE(MIN('Y'), 'N')
    FROM Friends
    WHERE EXISTS (select a.1
        FROM (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 2 AND receiveUserId = 4) AS a
        JOIN (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 4 AND receiveUserId = 2) AS b)) AS isFriend
FROM Users AS u
WHERE userId = 4 
    AND userId IN (SELECT DISTINCT userId
        FROM Channels
        WHERE postId IN (SELECT postId FROM Channels WHERE userId = 2)
            AND userId != 2)
;
 - 2번을 기준으로 4번을 검색한다.



-- 유저 정보 확인 (친구)

1. 모든 친구 정보 출력

SELECT * FROM Friends
;

2. 나와 친구인 경우 'Y' 아닐경우 'N'을 출력한다.

(SELECT COALESCE(MIN('Y'), 'N')
    FROM Friends
    WHERE EXISTS (select a.1
        FROM (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 1 AND receiveUserId = 3) AS a
        JOIN (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 3 AND receiveUserId = 1) AS b))
;

3. 모든 코드를 병합한다.

SELECT u.userId, u.email, u.name, u.nickname, u.profileImg, u.statusMessage,
(SELECT GROUP_CONCAT(likeItem ORDER BY likeItem ASC SEPARATOR ', ')
    FROM Likes 
    WHERE userId = u.userId
    GROUP BY userId) AS likeItem
FROM Users AS u
WHERE userId = 2 AND 'Y' = (SELECT COALESCE(MIN('Y'), 'N')
    FROM Friends
    WHERE EXISTS (select a.1
        FROM (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 2 AND receiveUserId = 3) AS a
        JOIN (SELECT DISTINCT 1
            FROM  Friends
            WHERE giveUserId = 3 AND receiveUserId = 2) AS b));
 - 지정한 userId(1)가 나(3)와 친구일 경우 출력한다.




-- postId 지정해서 Post 데이터 가져오기

1. tag 제외 출력하기.

SELECT title, postImg, content, maxMember, startDate, endDate, place, bring
FROM Posts
WHERE postId = 5
;


2. postId 기준으로 묶기 

SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
    FROM Tags
    WHERE postId = 5
    GROUP BY postId
;


3. 코드 병합

SELECT title, postImg, content, maxMember, startDate, endDate, place, bring,
(SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
    FROM Tags
    WHERE postId = p.postId
    GROUP BY postId) AS tag
FROM Posts AS p 
WHERE postId = 5
;