how to put 3 SQLs in 1

Discussion in 'Web Design and Development' started by timmillwood, Apr 12, 2007.

  1. timmillwood macrumors 6502a

    Joined:
    Apr 7, 2006
    #1
    is it possible to squish this into 1 select?
    or to stop it outputting so many of the same result?

    PHP:
    $social_one mysql_query("SELECT * FROM member_review WHERE review_id = $review_idno");

    if (!
    social_one) {
    exit(
    'error fetching info');
    }

    while (
    $row_one mysql_fetch_array($social_one)) {

    $social_member $row_one['member_id'];

    $social_two mysql_query("SELECT * FROM member_review WHERE member_id = $social_member");

    if (!
    social_two) {
    exit(
    'error fetching info');
    }

    while (
    $row_two mysql_fetch_array($social_two)) {


    $social_review $row_two['review_id'];

    $social_three mysql_query("SELECT * FROM album, artist, recordlabel, review, reviewer WHERE artist.artist_id = album.artist_id AND recordlabel.recordlabel_id = album.recordlabel_id AND album.album_id = review.album_id AND reviewer.reviewer_id = review.reviewer_id AND review.review_id = $social_review AND review.review_id != $review_idno LIMIT 0, 10");

    if (!
    social_three) {
    exit(
    'error fetching info');
    }

    while (
    $row_three mysql_fetch_array($social_three)) {
    echo 
    '<p><b>' $row_three['artist_name'] . '</b> - ' $row_three['album_name'] . '</p>';
    }
    }
    }
    EDIT:
    I knew i could do it, just gotta have patience

    Code:
    SELECT artist_name, album_name
    FROM album, artist, recordlabel, review, reviewer
    WHERE artist.artist_id = album.artist_id
    AND recordlabel.recordlabel_id = album.recordlabel_id
    AND album.album_id = review.album_id
    AND reviewer.reviewer_id = review.reviewer_id
    AND review.review_id
    IN (
    
    SELECT review_id
    FROM member_review
    WHERE member_id
    IN (
    
    SELECT member_id
    FROM member_review
    WHERE review_id =1
    )
    )
    LIMIT 0 , 10
    
     
  2. tutubibi macrumors 6502a

    tutubibi

    Joined:
    Sep 18, 2003
    Location:
    localhost
    #2
    It's kind a hard reading SQL wrapped in PHP code but just use nested SELECTs. Remove those first 2 selected and insert them in the last one.

    Something like:

    SELECT * FROM x, y, z WHERE x.a IN (SELECT a FROM w) AND y.b IN (SELECT b FROM v).


    EDIT: Looks like you figured it out while I was typing my response :)
     
  3. Winterfell macrumors regular

    Joined:
    Apr 3, 2007
    Location:
    Tulsa, Oklahoma
    #3
    Oh boy, how I do love sub queries!

    Would you believe that MySQL lacked sub queries prior to version 5? Had to use complicated joins for everything! Talk about pain. ;)
     

Share This Page