CODEWITHSUNDEEP
mysqljoincomposite-primary-key
shyarry g
shyarry g

Reputation: 375

how to do inner join with composite key

I have card_type table in which there is card_id is auto_increment key and cardtpe is primary key. The another table which I have is activities table. The columns in activities table are activity_id with auto_increment key and activity column with primary key. The third table is actual_alldetails table. The columns in actual_alldetails are id with auto_increment key and (emp_code,activity_id,card_id) column with composite key. The fourth table is employee table and columns in these table are emp_code with primary key and emp_name.

When I am trying to execute one of my query:-

SELECT i.*,
       d.Date,
       a.in_time,
       a.out_time,
       SEC_TO_TIME(SUM(TIME_TO_SEC(a.out_time))-(TIME_TO_SEC(a.in_time))) AS duration,
       c.cardtype,
       a.wo,
       v.activity,
       a.quty,
       a.wastage,
       a.mcusage,
       a.actual_wastage
FROM employee_details i
INNER JOIN actual_alldetails a ON i.emp_code=a.emp_code
INNER JOIN attendance_date d ON d.date_id=a.date_id
INNER JOIN card_type c ON c.card_id=a.card_id
INNER JOIN activities v ON v.activity_id=a.activity_id
WHERE d.Date='2016-01-30'
ORDER BY v.activity;

It show me error like:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause.

I don't get understand why it show me error like these. Is it because I am doing inner join with one primary key and one composite key?? If my prediction is right then what is the correct query to do these?? Please help me to solve this problem.

Upvotes: 0

Views: 1731

Answers (2)

Jester
Jester

Reputation: 1408

The error message says it:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause.

you have a select statement which contains an aggregated value SUM(TIME_TO_SEC(a.out_time) and a non aggregated value TIME_TO_SEC(a.in_time)

I don't know what exactly the result is you are hoping for but you either have to apply an aggregating function to the second value or you have do add a group by as the error message says so it knows what to group on.

For example change the query to this:

SELECT i.*,
   d.Date,
   a.in_time,
   a.out_time,
   SEC_TO_TIME(SUM(TIME_TO_SEC(a.out_time))-(TIME_TO_SEC(a.in_time))) AS duration,
   c.cardtype,
   a.wo,
   v.activity,
   a.quty,
   a.wastage,
   a.mcusage,
   a.actual_wastage
FROM employee_details i
INNER JOIN actual_alldetails a ON i.emp_code=a.emp_code
INNER JOIN attendance_date d ON d.date_id=a.date_id
INNER JOIN card_type c ON c.card_id=a.card_id
INNER JOIN activities v ON v.activity_id=a.activity_id
WHERE d.Date='2016-01-30'
GROUP BY i.emp_code
ORDER BY v.activity;

I'm guessing at the id value of your employees: i.emp_code

Upvotes: 1

Turbo J
Turbo J

Reputation: 7701

Look closer at this line:

SEC_TO_TIME(SUM(TIME_TO_SEC(a.out_time))-(TIME_TO_SEC(a.in_time))) AS duration,

There is a SUM() function here that looks very odd and most likely triggers your error message. Try to remove the SUM.

Upvotes: 0

Related Questions