i have 3 tables.
leave_cri:typs of leave allowd 4 an emp
leave_eli:all typ of leaves
leave_detail:details of leave taken by an emp

i need 2 write a query 2 select data based on these 3 tables.
ie) i have 2 display these in a grid.
leave details from_leave_details ; leave typ from leave_eli oly if that typ of leave is allowd 4 that emp.
For eg:

select leave_Type,emp_Leave_From,emp_Leave_To,emp_Leave_Days from leave_details inner join leave_eli on leave_details.leave_Type_Id=leave_eli.leave_Type_Id

bt i need this data to b displayed oly if the leave_Type_Id is in leave_cri.
Plz hlp me out vth the query.

select leave_Type,emp_Leave_From,emp_Leave_To,emp_Leave_Days from leave_details inner join leave_eli on leave_details.leave_Type_Id=leave_eli.leave_Type_Id
inner join leave_cri on leave_cri.leave_Type_Id=leave_details.leave_Type_Id
select leave_Type,emp_Leave_From,emp_Leave_To,emp_Leave_Days from leave_details inner join leave_eli on leave_details.leave_Type_Id=leave_eli.leave_Type_Id
inner join leave_cri on leave_cri.leave_Type_Id=leave_details.leave_Type_Id

the query is fine. but the data is getting displayed twice.

leave_cri:typs of leave allowd 4 an emp
leave_eli:all typ of leaves
leave_detail:details of leave taken by an emp

Does leave_cri has any emp id or some column to identify specific leaves are allowed to a emp

if possible post table structures

leave_cri:typs of leave allowd 4 an emp
leave_eli:all typ of leaves
leave_detail:details of leave taken by an emp

Does leave_cri has any emp id or some column to identify specific leaves are allowed to a emp

if possible post table structures

leave_cri tabl has the foll fields..
leave_cri_id, empid, leavtyp_id, leav_availblty

leav_eli has foll fields:
leavtyp_id, leav_typ, leav_days, salry_typ

leav_details hav maby fields out of som r the foll:
empid, leavtyp_id

hope this helps..

select 
leave_Type,
emp_Leave_From,
emp_Leave_To,
emp_Leave_Days 
from leave_details 
inner join leave_eli 
    on leave_details.leave_Type_Id=leave_eli.leave_Type_Id
inner join leave_cri 
    on leave_cri.leave_Type_Id=leave_details.leave_Type_Id
where leave_cri.empid=leav_details.empid
select 
leave_Type,
emp_Leave_From,
emp_Leave_To,
emp_Leave_Days 
from leave_details 
inner join leave_eli 
    on leave_details.leave_Type_Id=leave_eli.leave_Type_Id
inner join leave_cri 
    on leave_cri.leave_Type_Id=leave_details.leave_Type_Id
where leave_cri.empid=leav_details.empid

Thanx 4 replying. But this query only displays the data in the first row of the table. No matter what empid i enter the first rowq will be displayed. Has it got some thing to do with the primary keys i have set?

My proble has been solved..

SELECT insurance_property_allied.property_name AS [property_name],
insurance_property_allied.insurance_id AS [insurance_id],
mst_client.client_name AS [client_name],
COUNT(insurance_property_allied_location.location_id) AS [no_of_location]

FROM insurance_property_allied
INNER JOIN
mst_client
ON insurance_property_allied.client_id = mst_client.client_id
LEFT OUTER JOIN
insurance_property_allied_location
ON insurance_property_allied.insurance_id = insurance_property_allied_location.insurance_id

WHERE (insurance_property_allied.delete_flag IS NULL) AND (insurance_property_allied_location.delete_flag IS NULL)

GROUP BY insurance_property_allied.property_name,
insurance_property_allied.insurance_id,
mst_client.client_name

commented: beter reply to a more recent question. -3

I have 3 tables in mysql database

  1. mdl_course(id, fullname)

  2. mdl_course_displays(course, userid)

  3. mdl_user(id, username)

here, mdl_course id and mdl_course_display course are equal,
then mdl_course_display userid and mdl_user id are equal,

i want to display on fullname and username in dropdown box, if i select fullname in first dropdown box, automatically change the second dropdown box username, it is possible. anyone can help me. .

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.