mySQL join with where condition

Question

I have two tables in my db and it is one to many connection with other as shown below

enter image description here

In kvves_units table I will get 'name' from the GET Method

now I want to have all value from the kvves_units and kvves_members according to the name of the kvves_units

I'm using the code something like this

$kvvesDetails = $conn->prepare( "SELECT u.id, u.name, u.phone, u.email, u.address, m.name,    m.designantion, m.phone, m.email, m.imageFROM kvves_units AS u JOIN kvves_members AS m ON m.unit_id = u.id WHERE `name` = $committee");

That is a standard join. Didn't you take at least a SQL tutorial or what is the special problem with this case? — juergen d
– juergen d, Commented Dec 19, 2014 at 7:47
look for LEFT JOIN, INNER JOIN ... mysql.com will be your friend — donald123
– donald123, Commented Dec 19, 2014 at 7:48
$kvvesDetails = $conn->prepare( "SELECT u.id, u.name, u.phone, u.email, u.address, m.name, m.designantion, m.phone, m.email, m.image FROM kvves_units AS u JOIN kvves_members AS m ON m.unit_id = u.id WHERE name = $committee" ); — Sujith Wayanad
– Sujith Wayanad, Commented Dec 19, 2014 at 7:49

Jens · Accepted Answer · 2014-12-19 09:09:22Z

1

This is a standard join:

$kvvesDetails = $conn->prepare( "SELECT u.id, u.name, u.phone, u.email, u.address, m.name, m.designantion, m.phone, m.email, m.image FROM kvves_units AS u JOIN kvves_members AS m ON m.unit_id = u.id WHERE name = '$committee'"

edited Dec 19, 2014 at 9:09

answered Dec 19, 2014 at 7:49

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Sujith Wayanad Over a year ago

$kvvesDetails = $conn->prepare( "SELECT u.id, u.name, u.phone, u.email, u.address, m.name, m.designantion, m.phone, m.email, m.image FROM kvves_units AS u JOIN kvves_members AS m ON m.unit_id = u.id WHERE name = $committee" ); I'm using something like this can please edit this?

Jens Over a year ago

Looks OK. what is the error message you get? WHERE name = $committee must be WHERE u.name = $committee

Sujith Wayanad Over a year ago

am getting error like this Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'state' in 'where clause'' in D:\xampp\htdocs\projects\online-vyapari\business\kvvs.php:19 Stack trace: #0 D:\xampp\htdocs\projects\online-vyapari\business\kvvs.php(19): PDOStatement->execute() #1 {main} thrown in D:\xampp\htdocs\projects\online-vyapari\business\kvvs.php on line 19

Jens Over a year ago

That can not be. You do not have any column state in your where statement. The error message must come from an other statement.

Sujith Wayanad Over a year ago

yes it is the same. 'state' is from GET method that means the variable $commitee returns 'state'

|

jay.jivani · Accepted Answer · 2014-12-19 07:49:41Z

1

Use this SQL

select kvves_units.*,kvves_members.* from kvves_units a join kvves_members b where a.name = b.name and a.name = '".$_GET['name']."'

answered Dec 19, 2014 at 7:49

jay.jivani

1,5741 gold badge19 silver badges33 bronze badges

Comments

Yash · Accepted Answer · 2014-12-19 07:51:43Z

0

Try :

$name = $_GET['name'];
$sql = "Select *from kvves_units as u INNER JOIN kvves_members as m where u.id = m.unit_id and u.name = '".$name."'";

You will get your solution.

answered Dec 19, 2014 at 7:51

Yash

1,4361 gold badge13 silver badges26 bronze badges

Collectives™ on Stack Overflow

mySQL join with where condition

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related