| Author |
 Topic  |
|
|
rnelsch
Starting Member
USA
31 Posts |
 Posted - 05/23/2012 : 14:06:14
|
I'm not sure how to ask my question of if I'm stating my facts correctly so let me know if I'm not giving enough information. Here is my problem:
When I go to build a query we have a table named "Job", and somewhere in our DB each "job" is assigned what is called a "leadsource". However, the field "LeadSource" is not on the table "Job" so I have selected the field from the actualy "LeadSource" table. But when I run the query it shows a "Job" for every single lead source on the "leadsource" table. Normally I would get by this by creating some innerjoins, but there are no fields that the two tables share. Basically I need to know how to tell the query to only show the "LeadSource" for the specific Job. Does this make sense? I know I'm a noob but generally I'm pretty good with SQL queries and I cannot seem to get this one to work.
Ryan A Nelsch |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 05/23/2012 : 14:14:33
|
You'll have to find the tables & columns that link to both Job and LeadSource tables and include them in your JOIN conditions. Right now you're getting a CROSS JOIN, or cartesian product, of every row in both tables. In essence, there is no JOIN.
It would help if you posted the DDL of your tables, the query you tried, some sample data and the expected output that you want. |
 |
|
|
rnelsch
Starting Member
USA
31 Posts |
Posted - 05/23/2012 : 14:18:08
|
I though of Cross Join too, so I wrote the query as:
Select Job.JobID,
Job.JobClass,
Job.JobType,
Job.Department,
Job.LeadGenBy,
Job.LeadDescription,
Job.SchedDate,
Job.TotalTicket,
Job.OriginalLeadID,
Job.CountLead,
LeadSource.LeadSource
From Job Cross Join LeadSource
Where job.scheddate = '5/21/12'
But I still get 64,000 records and I know we only had about 60 "Jobs" that day.
Ryan A Nelsch |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 05/23/2012 : 14:42:36
|
Right, you probably have about 1067 rows in the LeadSource table.
What about the LeadGenBy column? Does that exist in the LeadSource table? Is it equivalent to the LeadSource column? If not, is there another table that has both LeadGenBy and LeadSource? |
|
|
|
rnelsch
Starting Member
USA
31 Posts |
Posted - 05/23/2012 : 14:58:49
|
No, the LeadGenBy has no data in it even if it was shared, I think its an element for the database but with our package its not a field that we use. The software has different levels of a package you can purchase and some of the fields are not on our GUI but are still in the database. I looked at that too but they are 100% blank. I tried using the LeadDescription, but they are different on both tables. I think I'm just going to have to find a table that has that distinct value but there are a bazillion tables to go through, is there a way to search the database and find all the tables that have "xys" field in them?
Ryan A Nelsch |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 05/23/2012 : 15:00:56
|
| SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%xys%' |
|
|
|
rnelsch
Starting Member
USA
31 Posts |
Posted - 05/23/2012 : 15:53:28
|
Awesome robvolk, you just solved my problem. Found the perfect table. THANK YOU!!!
Ryan A Nelsch |
|
|
| |
Topic |
|
Query Help
