spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Codeigniter - MySql : Join three column values of one table with the ID of another table


You do not need three joins with the category table. Instead join with all three fields as an accepted match:

select a.id, a.artist_fname,
a.artist_lname, b.artist_id, b.sm_file_name,
b.category_id, b.category_id2, b.category_id3,
c.id, c.category
from ap_mini_artist a
left join ap_mini_artwork b on b.artist_id = a.id
left join ap_art_categories c on c.id in
(b.category_id, b.category_id2, b.category_id3)
where c.category LIKE ?

Regarding your active record query:

  • When you have joins you do not need to repeat table names in the from clause
  • where function has no after arguments, change it to db->like(c.category', $category, 'after'

Categories : PHP

Related to : Codeigniter - MySql : Join three column values of one table with the ID of another table
MySQL - Need to get only the latest data based on the date/time column in the child table for each record in the parent table
I think the problem come from your distinct which apply on all properties. Have you try something like: SELECT s.ESN, s.AssignedToName, s.Department, sl.est_dt, sl.latitude, sl.longitude FROM SpotTrace s JOIN SpotTrace_Log sl on s.ESN = sl.ESN_ID AND MAX(est_dt) ORDER BY s.ESN

Categories : Mysql
how to add column to a mysql table by selecting columns from another table
You can get a table's structure with the following query: select * from information_schema.table_name Which you can then use the result to either create or alter another table.

Categories : Mysql
SQL Script to define a table schema using values from another existing table in MYSQL
Given this sample data: CREATE TABLE Table1 (`ID` int, `Team` varchar(6), `Task` varchar(7)) ; INSERT INTO Table1 (`ID`, `Team`, `Task`) VALUES (1, 'Team01', 'Task_01'), (2, 'Team02', 'Task_02'), (3, 'Team02', 'Task_01') ; You would usually just do this SELECT Team, Task, COUNT(*) FROM Table1 GROUP BY Team, Task; to get this result | TEAM | TASK | COUNT(*) | |----

Categories : Mysql
Inputing column values in one table from another related table
You can try this: Update ROOT.ISB_COURSE_REGISTRATIONS set ROOT.ISB_COURSE_REGISTRATIONS.degree= ( Select MAX(ROOT.ISB_STUDENTS.degree) from ROOT.ISB_STUDENTS where ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID ) where exists ( select 1 from ROOT.ISB_STUDENTS where ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID ); This c

Categories : SQL
Update mysql table column
If you are sure, this is for the first part i.e. the month part problem only, then try this: UPDATE contact_details SET created_date2 = CONCAT('0', created_date2) WHERE LENGTH(created_date2) < 10

Categories : Mysql
Recently Add
Cartesian product of associative array showing key and value - PHP
Datamapper orm save and update not working
find methods in COM object
PHP Keyword search not working with multiple words
Set a total based on items ordered, two types, and sometimes one type will not be ordered.
FB request to join a group, going around their official SDK
Using Google charts with php data?
OAuth2 integration with ExactOnline
libvirt-php receives the error: unable to connect to server Permission denied
How to echo results by sets of 2?
Why does PHP's sprintf not round 5s reliably?
php replace affects on replaced string
PHP Find and replace multiple similar entries
Replace PHP date output with pre-made images
How to install Laravel 4 packages on Windows
Using updateOrCreate for multiple data insert in Laravel
Storing products in a MYSQL database and then search and filter them?
Create order with PrestaShop's API
how to get latest messages from all users
Yii CMultiFileUpload Restrict Image width
Combine array by key factor
Semicolon after if condition in PHP - code still works
Get date using day of the week
soap web service with symfony
Seasonal Reservation system Day/month
is it possible to join a single table it self?
Attempting to compare two arrays PHP
Sticky Select Option
Eventbrite duplicate event entry
Pagination Not Working on custom post type in Wordpress
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.