4 replies to this topic

[BlaineSch]

I only used 2 tables for this example so the problem is simplified so here we go:

table1
    id int(10)
    fid int(10)
table2
    id int(10)
    visible int(10)
    data varchar(100)

Basically I want to select all the rows of table1, simple so far.

SELECT * FROM `table1`

Now for each row in table1 there MIGHT be a row in table2, if I do a "JOIN" then if there was NOT row in table2 for table1 then that row in table1 would be excluded, so we use "LEFT JOIN"

SELECT
     *
FROM
     `table1` AS `t1`
     LEFT JOIN `table2` AS `t2` ON t1.fid=t2.id

Result:


Perfect so far, but now I want to make sure it's visible, this is where the issue comes in. Since we see that the first row `visible`=1 and the second one it's null, and we want the same results we'd simple do something like:

    SELECT
        *
    FROM
        `table1` AS `t1`
        LEFT JOIN `table2` AS `t2` ON t1.fid=t2.id
    WHERE
        visible = 1 OR visible = NULL

But this only shows the first row... I could also do something like:

    SELECT
        *
    FROM
        `table1` AS `t1`
        LEFT JOIN `table2` AS `t2` ON t1.fid=t2.id
    WHERE
        visible != 2

Since none are equal to 2 this 'should' work but it still only shows the first row.

If I try to do something inside of the SELECT clause it say I can't even find the row.

Example:

    SELECT
        IF(t2.visible >= 1,t2.visible,-1) AS visi, t1.*, t2.*
    FROM
        `table1` AS `t1`
        LEFT JOIN `table2` AS `t2` ON t1.fid=t2.id

and

    SELECT
        COALESCE(t2.visible, -1) AS visi, t1.*, t2.*
    FROM
        `table1` AS `t1`
        LEFT JOIN `table2` AS `t2` ON t1.fid=t2.id

Both give me:


So now I should be able to filter by it but when I try this:

    SELECT
        IF(t2.visible >= 1,t2.visible,-1) AS visi, t1.*, t2.*
    FROM
        `table1` AS `t1`
        LEFT JOIN `table2` AS `t2` ON t1.fid=t2.id
    WHERE
        visi = -1

I get this:

#1054 - Unknown column 'visi' in 'where clause'

Remember, all I want to do is select all the rows in table1, then if they exist append them to the end, but also make sure they are visible.

I believe in the real database tables visible is actually the user id and I need to make sure the correct user is accessing the data... after I access this data I will know if I should "update" or "insert" into "table2"

[Alex-V]

I'm having problems understanding what you want to do. I think this is you answer

    SELECT

        *

    FROM

        `table1` AS `t1`

        LEFT JOIN `table2` AS `t2` ON `t1`.`fid` = `t2`.`id`

    WHERE

        `visible` IN(1, NULL);

[wim DC]

I don't get this part of what you want:

Quote

then if they exist append them to the end

--------------------------------------------------------------------------------------
Don't you literally have to write "IS NULL" for a null-check?

It's been quite a while for me writing queries but here goes nothing :D
The syntax i use seems to be different...:P

SELECT *
FROM table1 t1
  LEFT JOIN table2 t2 
  ON t1.fid = t2.id[LEFT][COLOR=#000000]WHERE t2.visible IS NULL OR t2.visible = 1
[/COLOR][/LEFT]
 

[BlaineSch]

Wow... "IS NULL"... why is this an exception for comparison? I should have found this while Googling for almost an hour.

Yes, it works, thanks all for helping! :]

[wim DC]

BlaineSch said:

why is this an exception for comparison?

Because null in DBs are annoying ^^. I remember cursing so hard inside me when i was working with nulls in DB in ASP C#.

You can get a row back and then do if(row.field != null) --> epic crash.
Because field is DBNull, which isn't the same as null :glare: