SQL view to linkin custom fields tables

[mattaze]

Hey,
I will just say sorry if this SQL query not suitable for this forum and should be asking a SQL forum.

After making a view in the database to link all the tables to a campaign together so can query from an external excel file we use with existing system we running.
Looking at fully using Vicidial for our call centre, different to existing system, in terms how data saved in the database.

vicidial 2.4-364a
BUILD: 120409-1136
Asterisk v.1.4.39.2-vici

here is what iv got so far

select * from vicidial_lists
join vicidial_list ON vicidial_lists.list_id = vicidial_list.list_id
LEFT OUTTER JOIN custom_1234 ON vicidial_list.lead_id = custom_1234.lead_id
where vicidial_lists.campaign_id = 'campaign name'


Im comming from SQL Server background just to let you known, and no experience with WHILE proceduers. And used the Join, as thats what after adding the current sql to a View, it converted the Inner Joins to just Join.
Also I know it will with the * on that it will complain of duplicate field names, Ive shortend to save the 40+ fields that I would have to type other wise.

After it select all data for all lists associated with that campaign. and needs to link in the Custom Table

And stumped on how to use the ID names its found for the lists of that campaign, then loop through doing inner joins to the "list" table.


if was writing in a procedural language here kinda what id write

Select * from lists Inner Join lists on list_id
While (still lists to check)
If exists(Inner Join Custom_”list_id” On Custom_”list_id”.lead_id = list.lead_id
Loop
Where campaing = fleetcleanse


Thanks to anyone for any help or direction.

[DomeDan]

Hi.

I have been trying to use variables on table-names in a mysql query too, but failed.
I would write that in a high-level script language like perl or php instead something like this:

Code: Select all

<?php
mysql_connect($host, $user, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());

$query="SELECT * FROM vicidial_lists WHERE campaign_id = 'fleetcleanse'";

$result = mysql_query($query) or die(mysql_error());

$n=0;
$custom_query="SELECT * FROM vicidial_list l ";

while( $row = mysql_fetch_array( $result )) {
  $custom_query=$custom_query."INNER JOIN Custom_$row['list_id'] c$n ON c$n.lead_id=l.lead_id ";
  $n++;
}
# echo $custom_query;
#   SELECT * FROM vicidial_list l
#   INNER JOIN Custom_1000 c0 ON c0.lead_id=l.lead_id
#   INNER JOIN Custom_1008 c1 ON c1.lead_id=l.lead_id
#   INNER JOIN Custom_1016 c2 ON c2.lead_id=l.lead_id
?>