Good day Sir/Ma'am, I need help regarding on Join clause is it possible to use join clause in multiple column names from other table with foreign key? Problem: I have table named
Table 1: 'biditem'
COL.1: biditemid(pk)
COL.2: user_id(fk)
COL.3: product_id(fk)
COL.4: bidamountprice (decimal)
COL.5: bidtime (datetime)

Table 2: 'user'
COL.1: user_id(pk)
COL.2: user_firstname (varchar)
COL.3: user_middlename (varchar)
COL.4: user_lastname (varchar)

Table 3: 'product'
COL.1: product_id(pk)
COL.2: product_name varchar(25)

I want to display from the biditem look like this in php

CodePudding user response：

You can also write your query like

select 
   b.biditemid, 
   concat(u.user_firstname,' ',u.user_middle,' ',u.user_lastname) as name, 
   p.product_name,
   b.bidamountprice, 
   b.bidtime
from 
   bitem b,
   user u,
   product p
where
   b.user_id=u.user_id
   and b.product_id=p.product_id

• name your table with alias short form
• always remember for joining if you have 3 tables then 3-1 will be your minimum where clauses to make proper join with your all tables
• likewise you had 3 tables so the minimum number or where clause use was 2 in your above code.

You can use that standard and you can use left inner, outer join too whatever you are comfort in. Both standards can be use in oracle sqlplus, mySql, postgreSql

CodePudding user response：

The 2 tables mentioned do have relationships between them so you can try like - using aliases to refer to the tables to make things clearer.

select 
    b.`biditemid`,
    concat(u.`user_firstname`,' ',u.`user_middle`,' ',u.`user_lastname`) as `Name`, 
    p.`product_name` as `Product Name`,
    b.`bidamountprice` as `Bid Amount`,
    b.`bidtime` as `DateTime`
from `bititem` b
    left outer join `user` u on u.`user_id`=b.`user_id`
    left outer join `product` p on p.`product_id`=b.`product_id`

CodePudding user response：

select 
bi.biditemid,
CONCAT(u.user_firstname, ' ', u.user_middle, ' ',u.user_lastname) AS Name,
p.product_name as "Pruduct Name",
bi.bidamountprice as "Bid Amount", 
bi.bidtime as Datetime
from biditem as bi left join user as u on bi.user_id = u.user_id
left join product as p on bi.product_id = p.product_id

add where condition as you needed after that.