2
select * from deliveries, remittance

table 'deliveries' has 10 records while table 'remittance' has none. This query returns no results. what I want is mysql to return the 10 records from table 'deliveries'. please help me.

this is a sample table

deliveries -> trans_number from to

remittance -> trans_number from to

zzlalani
  • 22,960
  • 16
  • 44
  • 73
Alex Coroza
  • 1,747
  • 3
  • 23
  • 39

6 Answers6

3

You might need to use LEFT JOIN:

select deliveries.* 
from deliveries
left join remittance on ... -- put a join condition here.

This will return all the records from the deliveries table even if no matching rows found in the other table.

If you used INNER JOIN it won't return any rows, since the other table is empty. But it will give you only the rows from the deliveries which match the condition.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • sir what if table 'remittance' has records and 'deliveries' has none. using the above query will return no results. – Alex Coroza Feb 06 '14 at 11:26
  • @AlexCoroza - Yes it won't return any rows, if you want to return the rows found in `remittance` in this case use `RIGHT JOIN` instead `select remittance.* from deliveries right join remittance on ...` or switch the orders of the two tables: `select remittance.* from remittance left join deliveries on ...` – Mahmoud Gamal Feb 06 '14 at 11:33
  • @AlexCoroza - For more information about the differences between the join types see this article: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html – Mahmoud Gamal Feb 06 '14 at 11:34
2

try something like

select * from deliveries
left join remittance
on remittance.id = deliveries.remittance_id

It will bring all data from deliveries and matching data from remittance

UPDATE:

If you want to show records in any case if any table has records then you are looking for FULL OUTER JOIN

But since FULL OUTER JOIN is not supported in try this query

SELECT * FROM deliveries
LEFT JOIN remittance ON remittance.id = deliveries.remittance_id
UNION
SELECT * FROM deliveries
RIGHT JOIN remittance ON remittance.id = deliveries.remittance_id
zzlalani
  • 22,960
  • 16
  • 44
  • 73
  • sir i tried your answer and it worked. But what if table 'deliveries' has no records and then table 'remittance' has records. – Alex Coroza Feb 06 '14 at 11:09
  • 1
    i saw it from the link you that youve provided earlier. this query emulates full outer join, right? thanks a lot for your answer man. – Alex Coroza Feb 06 '14 at 11:30
  • sir i have another problem. Really, i have 3 tables to select. is it possible select them in 1 query? using your solution. – Alex Coroza Feb 06 '14 at 11:34
  • @AlexCoroza have a look at this: http://stackoverflow.com/a/9226900/829533 and this: http://stackoverflow.com/a/11410564/829533 – zzlalani Feb 06 '14 at 11:40
  • also have a look at this and its example http://stackoverflow.com/a/17663157/829533 – zzlalani Feb 06 '14 at 11:43
1

try this

     select d.* , r.* from deliveries d , remittance r
echo_Me
  • 37,078
  • 5
  • 58
  • 78
0

You need to give some criteria so that mysql can display the result for the tables..Like Join

G one
  • 2,679
  • 2
  • 14
  • 18
0
SELECT * FROM deliveries 
LEFT JOIN remittance 
ON remittance.id = deliveries.remittance_id
Andrius Naruševičius
  • 8,348
  • 7
  • 49
  • 78
0

Try This

select d.* from deliveries d left join remittance r
Jignesh Patel
  • 1,028
  • 6
  • 10