I have two database tables with "one to many" relationship
let say, table A and Table B
One field from Table A is a foreign key in table B I wanna fetch just one record from table A as well as table B (given table A's primary key)
table A
id name
--------------------
1 ABC
2 XYZ
Table B
id A_id email
------------------------------------------------
1 1 temp@temp1.com
2 1 temp@temp2.com
3 1 temp@temp3.com
4 2 temp@temp4.com
4 2 temp@temp5.com
Answer should be like this (Single Record From Table B)
For a.id = 1
A.id, A.name,B.email
-------------------------
1, ABC, temp@temp1.com
For a.id = 2
A.id, A.name,B.email
-------------------------
1, XYZ, temp@temp4.com
I used this query, but it returns all the records from table B(as table B has multiple records for each record in Table A)
SELECT a.id,a.name, b.email FROM A a, B b WHERE a.id = 1 AND b.A_id = a.id
I have created a SQL Fiddle for the demo here: http://www.sqlfiddle.com/#!2/15ae7/5
The query that you can use for getting the desired output is:
SELECT
tableA.id,
tableA.name,
tableB.email
FROM tableA
LEFT OUTER JOIN tableB ON tableB.A_id = tableA.id
GROUP BY tableB.A_id;
For more information on JOINS and GROUP BY you can refer to the following pages:
http://dev.mysql.com/doc/refman/5.0/en/join.html
https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
Try a JOIN
SELECT
a.id,
a.name,
b.email
FROM A a
LEFT JOIN B b ON b.A_id = a.id
WHERE a.id = 1;
GROUP BY b.A_id
You can use LIMIT if you want only one record.
SELECT a.id,a.name, b.email FROM A a, B b WHERE a.id = 1 AND b.A_id = a.id LIMIT 0,1
Hope this helps you.