CODEWITHSUNDEEP
phpmysql
ZeroVash
ZeroVash

Reputation: 558

combine 2 Selects in one

I have a query in mysql which work perfect.

        $result =mysql_query("
            SELECT a.IDN, c.Name_F, c.Name_L, d.Url_photo, 
                    ( SELECT COUNT(*)
                        FROM Messages AS e
                        WHERE   e.IDN = a.IDN               
                    ) AS mes_new ,
                    ( SELECT LEFT(s.Messages,25)
                        FROM Messages AS s
                        WHERE s.Time= (SELECT MAX(k.Time)
                        FROM Messages AS k
                        WHERE k.IDN = a.IDN)

                    ) AS latest 
            FROM offers AS a 
                    LEFT JOIN info AS c
                ON a.IDN = c.IDN
                    LEFT OUTER JOIN photos AS d
                ON c.IDN = d.IDN AND d.Flag_av='1'

            Where a.IDN_with='$IDN' AND a.Status='2'    
        ");

But I have also one IDN and need to make also a similar another Select. Something like that:

            $result =mysql_query("
            SELECT a.IDN, c.Name_F, c.Name_L, d.Url_photo, 
                    ( SELECT COUNT(*)
                        FROM Messages AS e
                        WHERE   e.IDN = a.IDN               
                    ) AS mes_new ,
                    ( SELECT LEFT(s.Messages,25)
                        FROM Messages AS s
                        WHERE s.Time= (SELECT MAX(k.Time)
                        FROM Messages AS k
                        WHERE k.IDN = a.IDN)

                    ) AS latest 
            FROM users AS a 
                    LEFT JOIN info AS c
                ON a.IDN = c.IDN
                    LEFT OUTER JOIN photos AS d
                ON c.IDN = d.IDN AND d.Flag_av='1'

            Where  a.IDN='$active'  
        ");

So as you can see change only table (from offer to user) and condition (Where), but is it possible to make it in one Select? As double select I thought about UNION since if the IDN will be in two table the row can repeat and I don't need it. But again I would like to have it in one Select. Is it possible?

Upvotes: 0

Views: 49

Answers (1)

ZeroVash
ZeroVash

Reputation: 558

Well I figured it out by my self, since user table have all users I will take all row from them with more complex condition. The first tests got good. If you see mistake or have a better variant please share ))) THANKS.

        $result =mysql_query("
            SELECT a.IDN, c.Name_F, c.Name_L, d.Url_photo, 
                    ( SELECT COUNT(*)
                        FROM Messages AS e
                        WHERE   e.IDN = a.IDN               
                    ) AS 'mes_new' ,
                    ( SELECT LEFT(s.Messages,25)
                        FROM Messages AS s
                        WHERE s.Time= (SELECT MAX(k.Time)
                        FROM Messages AS k
                        WHERE k.IDN = a.IDN)

                    ) AS latest 
            FROM users AS a 
                    LEFT JOIN offers AS r
                ON a.IDN = r.IDN 
                    LEFT JOIN info AS c
                ON a.IDN = c.IDN
                    LEFT OUTER JOIN photos AS d
                ON c.IDN = d.IDN AND d.Flag_av='1'

            Where (r.IDN_with='$IDN' AND r.Status='2') OR a.IDN='$active'       
        ");

Upvotes: 1

Related Questions