Need Help Writing Simple Query

--0015175cb2622788e1048c404fc6
Content-Type: text/plain; charset=ISO-8859-1

I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.

Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get all the columns from table 1, but
substitute the names from table 2 for the hID and vID values. For example,

Table 1:
col 1, col 2, hID, vID, col 3
A B 1 2 C

Table 2:
ID, name
1 fred
2 sam

Query result:
col1, col 2, hName, vName, col 3
A B fred sam C

Thanks!

Mark

--0015175cb2622788e1048c404fc6--

Re: Need Help Writing Simple Query

Hi Mark,
Please test this query:
select test1.*, (select name from test2 where test2.id=test1.`v_id` limit 1) as name_1,
(select name from test2 where test2.id=test1.`h_id` limit 1) as name_2
from test1;

- test1 table:
col1 v_id h_id
America 1 2

- test2 table:
id name
2 SAM
1 UNCLE

----- Original Message -----
From: "Mark Phillips"
To: "Mysql List"
Sent: Monday, July 26, 2010 8:29:00 AM
Subject: Need Help Writing Simple Query

I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.

Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get all the columns from table 1, but
substitute the names from table 2 for the hID and vID values. For example,

Table 1:
col 1, col 2, hID, vID, col 3
A B 1 2 C

Table 2:
ID, name
1 fred
2 sam

Query result:
col1, col 2, hName, vName, col 3
A B fred sam C

Thanks!

Mark

--
Best Regards,
Cuongmc.

--
Nguyen Manh Cuong
Phong Ky Thuat - Cong ty Vien Thong So - VTC
Dien thoai: 0912051542
Gmail : philipscuong@gmail.com
YahooMail : philipscuong@yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Need Help Writing Simple Query

On 07/25/2010 09:29 PM, Mark Phillips wrote:
> I have been away from sql for awhile, and can't seem to figure out how to
> write a simple query for two tables.
>
> Table 1 has many columns, two of which are hID and vID. Table 2 has two
> columns, ID and name. The hID and vID in table 1 correspond to the IDs in
> table 2. I want to make a query so I get all the columns from table 1, but
> substitute the names from table 2 for the hID and vID values. For example,
>
> Table 1:
> col 1, col 2, hID, vID, col 3
> A B 1 2 C
>
> Table 2:
> ID, name
> 1 fred
> 2 sam
>
> Query result:
> col1, col 2, hName, vName, col 3
> A B fred sam C
>

select a, b, c,
hTable.name as hName,
vTable.name as vName
from Table1,
Table2 as hTable,
Table2 as vTable
where hId = hTable.ID and vID = vTable.ID

or

select a, b, c,
hTable.name as hName,
vTable.name as vName
from Table1
left join Table2 as hTable on hID = hTable.ID
left join Table2 as vTable on vID = vTable.ID




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Need Help Writing Simple Query

--00163645840e62ddd6048c4b71d5
Content-Type: text/plain; charset=ISO-8859-1

On Sun, Jul 25, 2010 at 8:49 PM, Nguyen Manh Cuong <
cuong.manh@vienthongso.com> wrote:

> Hi Mark,
> Please test this query:
> select test1.*, (select name from test2 where test2.id=test1.`v_id` limit
> 1) as name_1,
> (select name from test2 where test2.id=test1.`h_id` limit 1) as name_2
> from test1;
>
> - test1 table:
> col1 v_id h_id
> America 1 2
>
> - test2 table:
> id name
> 2 SAM
> 1 UNCLE
>
> ----- Original Message -----
> From: "Mark Phillips"
> To: "Mysql List"
> Sent: Monday, July 26, 2010 8:29:00 AM
> Subject: Need Help Writing Simple Query
>
> I have been away from sql for awhile, and can't seem to figure out how to
> write a simple query for two tables.
>
> Table 1 has many columns, two of which are hID and vID. Table 2 has two
> columns, ID and name. The hID and vID in table 1 correspond to the IDs in
> table 2. I want to make a query so I get all the columns from table 1, but
> substitute the names from table 2 for the hID and vID values. For example,
>
> Table 1:
> col 1, col 2, hID, vID, col 3
> A B 1 2 C
>
> Table 2:
> ID, name
> 1 fred
> 2 sam
>
> Query result:
> col1, col 2, hName, vName, col 3
> A B fred sam C
>
> Thanks!
>
> Mark
>
> --
> Best Regards,
> Cuongmc.
>
> --
> Nguyen Manh Cuong
> Phong Ky Thuat - Cong ty Vien Thong So - VTC
> Dien thoai: 0912051542
> Gmail : philipscuong@gmail.com
> YahooMail : philipscuong@yahoo.com
>

Thanks! That did the trick.

Mark

--00163645840e62ddd6048c4b71d5--