[데이터베이스] 내가 가진 식재료로 만들 수 있는 레시피 조회하기

레시피DB 구조도

가정 : 냉장고에 양파, 김치, 계란, 밥이 존재

 

1) 계란, 밥, 양파, 김치가 포함된 레시피 ID 조회 + 레시피 ID별 포함된 개수(3개 이상, 내림차순) 출력

 

SELECT RECIPE_ID, count(IRDNT_NM) FROM recipe_ingredient WHERE IRDNT_NM="계란" OR IRDNT_NM="밥" OR IRDNT_NM="양파" OR IRDNT_NM="김치" GROUP BY RECIPE_ID HAVING count(IRDNT_NM) >= 3 ORDER BY count(IRDNT_NM) DESC;

 

 

2) 1)에서 검색된 레시피 ID를 가지고 레시피 이름+레시피 ID별 포함된 개수(3개 이상, 내림차순) 조회 

 

SELECT B.RECIPE_NM_KO, C.RECIPE_COUNT
FROM recipe_basic B,(SELECT A.RECIPE_ID, count(A.IRDNT_NM) AS RECIPE_COUNT
FROM recipe_ingredient A
WHERE A.IRDNT_NM="계란" OR A.IRDNT_NM="밥" OR A.IRDNT_NM="양파" OR A.IRDNT_NM="김치" 
GROUP BY A.RECIPE_ID HAVING count(A.IRDNT_NM) >= 3 
ORDER BY count(A.IRDNT_NM) DESC) C
WHERE B.RECIPE_ID=C.RECIPE_ID
ORDER BY C.RECIPE_COUNT DESC;

+ select B.*일 경우

 

 

3) 1)에서 검색된 레시피 ID를 가지고 레시피명, 레시피 과정 전체 출력

 

SELECT B.RECIPE_NM_KO, D.*, C.RECIPE_COUNT
FROM recipe_basic B,recipe_process D,(SELECT A.RECIPE_ID, count(A.IRDNT_NM) AS RECIPE_COUNT
FROM recipe_ingredient A
WHERE A.IRDNT_NM="계란" OR A.IRDNT_NM="밥" OR A.IRDNT_NM="양파" OR A.IRDNT_NM="김치" 
GROUP BY A.RECIPE_ID HAVING count(A.IRDNT_NM) >= 3 
ORDER BY count(A.IRDNT_NM) DESC) C
WHERE B.RECIPE_ID=C.RECIPE_ID AND C.RECIPE_ID=D.RECIPE_ID
ORDER BY C.RECIPE_COUNT DESC;

 

 

+추가 android에 적용하는 소스코드

 public void getVal() {
 
        DataBaseHelper dbHelper = new DataBaseHelper(this);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
 
        //Cursor cursor = db.rawQuery("SELECT * FROM recipe_ingredient where RECIPE_ID = 195453",null);
        //" and name = ?",new String[]{"홍길동"});
 
        Cursor cursor = db.rawQuery("SELECT B.RECIPE_NM_KO, C.RECIPE_COUNT" +
                " FROM recipe_basic B,(SELECT A.RECIPE_ID, count(A.IRDNT_NM) AS RECIPE_COUNT" +
                " FROM recipe_ingredient A" +
                " WHERE A.IRDNT_NM=? OR A.IRDNT_NM=? OR A.IRDNT_NM=? OR A.IRDNT_NM=?" +
                " GROUP BY A.RECIPE_ID HAVING count(A.IRDNT_NM) >= 3" +
                " ORDER BY count(A.IRDNT_NM) DESC) C" +
                " WHERE B.RECIPE_ID=C.RECIPE_ID" +
                " ORDER BY C.RECIPE_COUNT DESC", new String[]{"계란", "밥", "양파", "김치"});
 
        while (cursor.moveToNext())
        {
            val += cursor.getString(0)+", ";
 
        }
        sqlresult.setText("요리 목록: "+val);
        cursor.close();
        dbHelper.close();
    }

->결과: 김치볶음밥, 돈까스 덮밥, 돈부리, 해물밥전, 김치 수제비, 중국식 볶음밥, 콩나물 국밥, 오므라이스 출력됨