MySQL Joins with partial data from other table

[afridy]

Hi folks,

Table : dn_buys

vehicle_id | buying_date

1| 12-10-2019
2| 11-05-2019
3| 12-10-2019

Table : dn_sales

vehicle_id | sold_date

1| 12-10-2019

Here i need to select all data in table dn_buys while need to get the sold_date also (if a sale record is avaiaible) from dn_sales table for that relevant vehicle_id. if not avaiaible, the print will have sold_date column blank.

i tried this.

$query = $db->prepare("SELECT * 
					   FROM dn_buys 
					   LEFT JOIN dn_sales 
					   ON dn_buys.vehicle_id = dn_sales.vehicle_id
					   ORDER BY dn_buys.vehicle_id DESC");

The result is, this correctly featch all data in table_buys and the relevent sales record from dn_sales table for which ever the avaiaible records.

while($row = $query->fetch()){
      $vehicle_id=$row['vehicle_id'];

but problem is this does not print the vehicle id of records which are present in both tables. the vehicle id column is blank in results. please advise.

[m_hutley]

vehicle_id is ambiguous. Declare your select better:

SELECT dn_buys.vehicle_id ....

[afridy]

sorry i cant undestand correctly. if you can be more descriptive better.
btw, pls assume in reality more data colums are there in both tables.

[Mittineague]

What is the value of the “vehicle_id” field that’s in the first row of the table?

[afridy]

it is 274,

btw, i found that which ever records exist in dn_buys which are not found in dn_sales are printing blank for vehicle_id in results.

[Mittineague]

I’m curious how you were able to determine it’s 274

@afridy

which are not found in dn_sales are printing blank for vehicle_id in results.

Maybe a LEFT JOIN isn’t what you want to use here? (INNER JOIN, UNION?)

[afridy]

@Mittineague

I’m curious how you were able to determine it’s 274

first value you mean in the result set or in the dn_buys table?

[Mittineague]

@afridy

you mean in the result set or in the dn_buys table?

Exactly. Without more explicit identification the question is ambiguous. As humans we can be great at “knowing” what was meant without the explicitness. Code, not so much. It’s great at doing what it’s told to do, but it has to be told. For example, instead of

SELECT * 
FROM some_table 
JOIN other_table 

I find it is often better more like

SELECT some_table.some_field 
   , other_table.other_field 
FROM some_table 
JOIN other_table 

True, it can get very verbose compared to using the * “everything” but it makes the code easier to read and is less work for the database because it doesn’t need to figure out what the everything is.

[afridy]

tried INNTER JOIN. It creates a result set from records found in both table only. which ever record not present in either one of the tables are not showing. And UNION also seems not the solution here.

[Mittineague]

I’m missing something. You don’t want results with missing values in them and you don’t want only results that don’t have missing values in them. How would you describe what your ideal “expected results” would be?

[afridy]

-yah i want all records in dn_buys
-but some of the above above records also have more detail in dn_sales table.
unique key is vehicle_id. so these more details should also be added to the result set.

[afridy]

in short, i need this result

1| 12-10-2019|12-10-2019
2| 11-05-2019
3| 12-10-2019

so you can see, the first vehicle is brought and sold in same date. other two are not sold yet.

[benanamen]

I don’t see any reason the sale date should be in a separate table. Perhaps OP can explain why it is.

OP please explain the actual problem you are trying to solve with your code attempt.

[Mittineague]

Is this true? Of all these fields:

dn_buys.vehicle_id 
dn_buys.buying_date 
dn_sales.vehicle_id 
dn_sales.sold_date 

• Every vehicle_id value always appears in both tables.
• The only field that might ever be empty is dn_sales.sold_date.
• For some reason, when using LEFT JOIN and dn_sales.sold_date is empty, the ambiguous vehicle_id result is also empty.

[afridy]

absolutly. so i have decided to join both table data togeather and make a single table called ‘vehciles’ where brought and sold records all are kept in one table. easy to manage.
this i am reparing another once developed system

Thank you mitt and everyone.

[r937]

@afridy

but problem is this does not print the vehicle id of records which are present in both tables. the vehicle id column is blank in results. please advise.

the sql query does return data – it’s php that’s barfing on the duplicate column name

pro tip: never use the dreaded, evil “select star” – code only the columns you want (and in this case you want only vehicle_id from dn_buys)

[afridy]

Thanks for the tip.