0

Is it possible to strip away columns from the response I get in a query where I join 3 tables and need more or less all columns for the query itself so that some columns aren't visible in the response?

This is the query I have:

    $sth = mysql_query("
        SELECT
            tbl_subApp2Tag.*,
            tbl_subApp.*,
            tbl_tag.*
            ISNULL(tbl_userDeviceNOTTag.userDevice_id) AS selected

        FROM tbl_subApp2Tag

        LEFT JOIN tbl_subApp
            ON tbl_subApp.id = tbl_subApp2Tag.subApp_id
            AND tbl_subApp.subApp_id = '".$sub."'

        LEFT JOIN tbl_tag
            ON tbl_tag.id = tbl_subApp2Tag.tag_id

        LEFT JOIN tbl_userDeviceNOTTag
            ON tbl_userDeviceNOTTag.tag_id = tbl_tag.id
            AND tbl_userDeviceNOTTag.userDevice_id = '".$user."'

        WHERE tbl_subApp2Tag.subApp_id = tbl_subApp.id

        ORDER BY tbl_tag.name ASC ");
    if(!$sth) echo "Error in query: ".mysql_error();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }
just_user
  • 11,769
  • 19
  • 90
  • 135
  • This can help if I got you right http://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql – Narek Feb 29 '12 at 08:43
  • Which columns do you need? Basically, you only need to include the columns you want in to SELECT. You don't need to include them into SELECT just to use them in JOINs or as WHERE clause. – Martin Rothenberger Feb 29 '12 at 08:45
  • Best way to do this is by explicitly specifying the columns you need. – Salman A Feb 29 '12 at 08:50

1 Answers1

2

You do not need to include columns in the result table, just because they are referenced elsewhere in the query. Just select the columns that you need.

Philip Sheard
  • 5,789
  • 5
  • 27
  • 42
  • Do you mean in SELECT? I've tried this, for example I changed the tbl_subapp.* to tbl_subapp.id which the results in an error in the query! Error in query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ISNULL(tbl_userDeviceNOTTag.userDevice_id) AS selected FROM tbl_subApp2Tag' at line 5
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in     – just_user Feb 29 '12 at 09:27
  • It is probably an error in the syntax. I have just noticed that you are missing a comma from your original query. – Philip Sheard Feb 29 '12 at 09:29
  • Thanks man, that did it. Missing the comma caused the error, so with your suggestion it worked perfectly! Thanks! – just_user Feb 29 '12 at 10:22