CODEWITHSUNDEEP
sqljoinwhere-clause
Daanvn
Daanvn

Reputation: 1266

Joining SQL queries with where clause

I have 2 tables in a MYSQL database wich look like this:

Klant:

ID  Naam           Email    Soort  Status
6   test           test     test2   
7   status test    test     test    20
8   soort test     test     test    
9   soort test 2   test2    Museum

Mail:

ID  Content                 Datum       Titel 
1   (lots of encoded HTML)  18-03-13    test
2   (lots of encoded HTML)  18-03-13    test2
4   (lots of encoded HTML)  18-03-13    alles weer testen

(yes, I'm still testing alot^^)

Now I have a SQL query that selects all from 'Klant' with a where clause which gets the ID from a previous page:

$strSQL = "SELECT * FROM Klant WHERE ID =  '".$_GET["ID"]."' ";

What I want is to JOIN this query with the following query:

SELECT ID, Titel FROM Mail;

EDIT:

From all your answers and comments I think I begin to think my question maybe is totally wrong.. I'll explain where I need it for and I might not even need JOIN? I currently have a table wich includes the data from 'Klant' which looks like this:

enter image description here

The meaning is that I add another table which includes all the ID's and Title's from 'Mail'. I am sorry for the confusion I may have caused with you since I wasn't that clear with my question. I hope that this may clear up what I want and you guys can maybe tell me if I even need to JOIN this or can I do something else?

I am still a student and this is the first time I've had to use JOIN and I can't figure this out. If anyone can show me how to do this or push me in the right direction it would be great!

Upvotes: 3

Views: 8755

Answers (4)

Bender
Bender

Reputation: 83

SELECT * FROM Klant t1
JOIN
SELECT ID, Titel FROM Mail t2
ON t1.ID = t2.ID
WHERE t1.Name  = 'test'

To have the desired result do the following:

SELECT * FROM Klant t1
JOIN
SELECT ID, Titel FROM Mail t2
ON t1.ID = t2.ID

And if you want to have a specific row than just add the where clause:

WHERE t1.ID = 6

or

WHERE t1.Naam = 'test'

and so on

Upvotes: 3

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

It is difficult to see how a JOIN is applicable in the example in your question.

A JOIN let's you pull information from more than one table based on a relationship. As far as I can see, your table's don't have any way to link a row in one with a row in the other, unless SteveP is correct and your id's provide that relationship.

For example, if your klant table had a mail_id column then you could do

SELECT *
FROM klant
JOIN mail ON klant.mail_id = mail.id

and this would return a row for every matching pair of rows in the two tables. Alternatively you could use a LEFT OUTER JOIN to pull back all rows from the table on the left of the JOIN and optionally data from a matching row on the right.

If there is nothing joining the table, you can use a CROSS JOIN which will return you a full cartesian of each row in table1 with every row in table2.

Something people often confuse with a JOIN is a UNION which allows you to write 2 SELECT statements and return the result set of both combined/joined together, but these should return the same columns in each query (e.g. selecting NULL in place of the column in a query if the query doesn't pull data for that column)

Upvotes: 0

Ajo Koshy
Ajo Koshy

Reputation: 1225

You can do this directly by using the following query : 

select k.ID,k.Naam, k.Email,k.Soort,k.Status, m.ID,m.Titel from Klant k, Mail m where k.ID = m.ID and k.ID = '".$_GET["ID"]."'

Upvotes: 0

SteveP
SteveP

Reputation: 19103

I'm guess that you want to join on the ID field which is common between the tables.

select * from Klant, Mail where Klant.ID = '".$_GET["ID"]."' and Klant.ID = Mail.ID

You can also do

select * from Klant
  join Mail on  Mail.ID = Klant.ID
  where Klant.ID = '".$_GET["ID"]."'

Upvotes: 0

Related Questions