-1

I have this code:

Select  p_firstname, p_email
From    teacher as t  inner join borrowed as b
where t.p_id = b.p_id;

It works perfectly without where clause.
Once i add it, it gives record count : 0 .
Any solutions? I uploaded a picture

enter image description here

Anas
  • 43
  • 1
  • 1
  • 10
  • 1
    Better share a link to the sqlfiddle, in addition to the screenshot. – Al.G. Dec 27 '16 at 19:37
  • *instead of* the screenshot. Also, try specifying **which** error you get. There is more than one (:O) – Blorgbeard Dec 27 '16 at 19:37
  • thanks i uploaded a screenshot, it is not an error, it is just give me record count 0 which is not true – Anas Dec 27 '16 at 20:17
  • Possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – suraj_fale Dec 27 '16 at 21:29
  • 1
    Some advice: 1. Show sample data and expected result when asking such questions. Thus we would have seen that you want data from one table that has no match in the other. 2. Don't show pictures, but text that we can copy and paste. 3. Use qualifiers for all columns when working with more than one table. 4. An inner join needs an `ON` clause. MySql violates the SQL standard by not demanding it. 5. You are only selecting fields from `teacher` it seems, so what for are you using `borrowed` in your query? Can't you simply remove it and be fine? You should have explained, why you join the tables. – Thorsten Kettner Dec 27 '16 at 23:07
  • "It works perfectly without where clause". Perfectly??? Without any join criteria this is a cross join (in spite of being allowed to call this "inner join" in MySQL), so you'd join every record from one table with every record from the other, thus getting all possible combinations. Is this desired? Is there only one record in one of the two tables maybe? – Thorsten Kettner Dec 27 '16 at 23:11

2 Answers2

1

Are you simply trying to INNER JOIN the two tables on p_id? If that is the case:

SELECT p_firstname, p_email FROM Teacher t
INNER JOIN Borrowed b ON t.p_id = b.p_id

If you would like to return all records from the Teacher table, even when there are null p_id in the Borrowed table, use a LEFT JOIN instead.

Wes Doyle
  • 2,199
  • 3
  • 17
  • 32
  • it still gives me the same thing – Anas Dec 27 '16 at 19:58
  • Record Count: 0 – Anas Dec 27 '16 at 19:59
  • Are there corresponding p_id records in both tables? An INNER JOIN will return all records for which a match is found in both tables. LEFT JOIN will return all records from the left table, even when p_id are null in the right table. – Wes Doyle Dec 27 '16 at 20:02
  • i want to thank you 1000 times, it worked the secret was in left join not inner thank you again – Anas Dec 27 '16 at 20:26
1

If I am not mistaken since you are using an inner join, instead of using Where t.p_id = b.p_id; you need to use ON. For example:

SELECT p_firstname, p_email  
FROM teacher as t
INNER JOIN borrowed as b
    ON (t.p_id = b.p_id);

If you need more info such as name = "bob", then you use a WHERE statement afterwards. Try the fixed statement I wrote and let me know if it works!

EDIT:

You can also try this if teacher and borrowed are two separate databases:

SELECT p_firstname, p_email
FROM teacher, borrowed
WHERE (teacher.p_id = borrowed.p_id);
Daniel
  • 1,229
  • 14
  • 24
Jurick Pastechi Genaro
  • 94
  • 12
  • it gaves me the same thing record count : 0 – Anas Dec 27 '16 at 20:10
  • i editted my comment to add something else. i am not sure if you saw it or tried it already. let me know if you tried option 2 lol. 1 question tho. Teacher and Borrowed are 2 seperate databases right? or is borrowed a copy of teacher? – Jurick Pastechi Genaro Dec 27 '16 at 20:26