how can i fetch 2 table with perticluar row? mysql php

Question

I have table1 and table2.

Table 1
ID , Status
1 | open
2 | close
3 | open

Table 2
ID, keyString, Name
1 | FirstName | john
1 | LastName | wick
2 | FirstName | linken
2 | LastName | jenn
3 | FirstName | wong
3 | LastName | allen

I want to result the list of full name (firstname + Last name) with the status open.

I am trying mysql join query but having no luck.

Qam Afxal
MiniTAQ

Answer 1

Hi,

As Olaf said, if your question is about MySQL, please ask in the MySQL forum.

Based on your data, I created simple tables to test with T-SQL.

CREATE TABLE [dbo].[table1](
    [id] [int] NULL,
    [status] [varchar](10) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[table2](
    [id] [int] NULL,
    [keystring] [varchar](20) NULL,
    [name] [varchar](20) NULL
) ON [PRIMARY]
GO


SELECT id,  
fullname=STUFF  
(  
     (  
       SELECT DISTINCT ' ' + CAST(name AS VARCHAR(MAX))  
       FROM table2 t2   
       WHERE t2.id = t1.id    
       FOR XML PATH('')  
     ),1,1,''  
)  
FROM table1 t1 where t1.status='open'