MySQL Query?????

[tuxbuddy]

There is a  table called “ incidents “.Recently I (venkat) raised a call to ISD People that I need Ettercap tool to be installed.So When I raised a call the incident got ID = 41,description as specified.

 

Since noone has accepted call from ISD side it is showing NULL under owner_id.

 

Heres the complete table structure:

 

Incidents

 

ID          Description          contact_id  Owner_id

41          install ettercap    9                  NULL

 

Now here one important thing to consider is NULL under Owner_id refers to that No Engineer has yet taken responsibilty to carry on the issue.Once it is accepted by engineer on its ownself,it becomes that engineer ID.

 

Also,There is another table called Users.

Table:Users

 

ID  firstname  surname      loginname

 

9  venkat    saraswathi  saraswathiv

 

The Contact ID in incident table is same as ID on users table.

 

Now I am running this query:

 

SELECT  incidents.id,incidents.description,users.firstname,users.surname  FROM incidents,users where (incidents.contact_id = 9) AND  (incidents.contact_id = users.id)



All it displays:
---------------------------------------------------------------------------------
ID Description                           Engineers Firstname        Engineers Surname 
38 install redhat5 on26 server      venkat  
39 install perl                            venkat  
35 sdvfzsdgvsdfb                       venkat  
41 Pls install Ettercap 
     on 10.14.2.42                        venkat 
------------------------------------------------------------------------

 

 

What I want is display the following way:

 

 

ID    Description EngineersNAME

 

41    Install Ettercap        Prabhu

 

 

Engineers NAME should nt be ID but name.Simply passing the query:

 

select id,description,owner_id from incident where incidents.id= 9

 

will retrieve owner_id but I want ownerNAME ...

[SharkBait]

SELECT T1.id, T1.description, T2.firstname, T2.surname FROM incidents AS T1, INNER JOIN users AS T2 ON (T1.contact_id = T2.id) T1.contact_id = 9

 

Try that?

[tuxbuddy]

Its showing error:

1064 - 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 'INNER JOIN users AS T2

ON (T1.contact_id = T2.id)

(T1.contact_id = 9) LIMIT ' at line 1

 

Pls post the right query syntax

[tuxbuddy]

Now this query is working:

 

SELECT t1.id, t1.description, t2.firstname

FROM incidents AS t1

INNER JOIN users AS t2 ON ( t1.contact_id = t2.id )

AND (

t1.contact_id =9

) LIMIT 0 , 30

 

The Output Being:

38 install redhat5 on26 server venkat

39 install perl venkat

 

 

Heyyy... I dont want Venkat to be displayed ...It should display value realted to owner_id not for contact_id.

 

In the above table , the output shud be like:

 

 

38 install redhat5 on26 server  NULL

39 install perl venkat prabhu

 

Just note that NULL shoud also be shown and if any other value other than NULL is there it should also get displayed

 

In incident table we have owner_id and the table should display corresponding to id , name of the engineer who will work for the project.

[tuxbuddy]

More Clearly, if we run the query:

 

 

SELECT incidents.id, incidents.description, incidents.owner_id

FROM incidents, users

WHERE incidents.contact_id = users.id LIMIT 0 , 30

 

  id description owner_id 
      36 Install Mandriva NULL 
      26 test1 7 
      37 asd;lfkasd;fkjas NULL 
      38 install redhat5 on26 server  NULL 
      39 install perl NULL 
      40 install informix NULL 
      13 at 1 
      24 test 1 
      34 asdfgasdgsdf NULL 

 

Instead of 7,NULL from incidents table it shoud display firstname , surname corresponding to owner_id

[tuxbuddy]

Great !!! I did it happen...I passed the query like this:

 

SELECT  incidents.id,incidents.description,users.firstname  FROM incidents,users where (incidents.contact_id = '".$uid."') AND (incidents.owner_id = users.id) OR (incidents.owner_id = NULL)


But it is showing now:

ID   DEscription  EngineerNAme

9    install IOS       prabhu

But the entry corresponding to NULL is not shown.

[Barand]

Is this what you want?

 

<?php
$sql = "SELECT  incidents.id,incidents.description,users.firstname  
        FROM incidents 
            LEFT JOIN users ON incidents.owner_id = users.id
        WHERE (incidents.contact_id = '$uid') ";