Perform INNER JOIN correctly

Question

Perform INNER JOIN correctly

Navigation

#1 by (1 votes)

0

I have 2 tables t1=quotations and t2=quotation_has_packinglist in which I have repeated the fields email and date , in order to get the information from the table quotation_has_packinglist and show it in the view where I show the options in the quotations table as long as the email and the date date are equal. What I have now is

SELECT quotation_has_packinglist.* FROM quotation_has_packinglist, quotations WHERE quotations.email = quotation_has_packinglist.email

But it brings to my view all the information in the table 'quotation_has_packinglist' , does not filter by 'email' and 'date' . I appreciate the help. Thanks!

I edit the entry to add more information, as requested by @Aaron: What I want to achieve is shown in the following image, in the upper part showing the contents of the 2 tables relating by email and date:

My tables have the following structure:

And in both tables I have left the email fields and date with the same name and index to make it easier to find your data. My driver if I enable the line with email and date provided (fixed) correctly shows what I need, but not dynamically; but if I comment it and decompress the line where the JOIN is made provided by the first response of this thread it does not show anything in the lower part, that is, it does not bring the options to the variable $ packinglist.

And finally the code of the view where the top part is shown in the first image and the bottom part in the second image, where I want to show the Packing List options:

I am working with Codeigniter (MVC). The model does not include it since it is encoded with ActiveRecords, but it is shown in the images that are delivering the information, and what is missing is that the JOIN in the controller must be done correctly so that it shows what I need, from there the title of my question "how to perform the JOIN correctly". Thank you in advance for your solution proposals. Greetings !!

mysql php

asked by Eduardo Muñoz 01.12.2017 в 23:34

source

1 answer

Authorize Customized how to pass a .scss sheet to .css?

score 1 · Answer 1

If you want to do an inner join of both tables according to what you plan, it should be like this:

SELECT quotation_has_packinglist.* 
FROM quotation_has_packinglist 
INNER JOIN
    quotations ON quotations.email = quotation_has_packinglist.email 
AND 
    quotations.date = quotation_has_packinglist.date

Apparently in your query you are not using the date filter or inner join, you are crossing the two tables by a field that does not determine any difference because both can be the same in the whole table, so it seems that it did not filter.